Creating Users and Privileges with PostgreSQL

Create User

The simplest way to create a new user is to use the createuser command. If you are going to use PostgreSQL a lot, its a good idea to add your PostgreSQL bin directory to your PATH. Add personal accounts for each user and also for the webserver. By default, unix usernames are used when connecting to the database. If you installed and ran PostgreSQL under the postgres user, then you will have to use the following command to add a new user:

bash-2.05a$ createuser --help
createuser creates a new PostgreSQL user.

Usage:
  createuser [OPTION]... [USERNAME]

Options:
  -a, --adduser             user can add new users
  -A, --no-adduser          user cannot add new users
  -d, --createdb            user can create new databases
  -D, --no-createdb         user cannot create databases
  -P, --pwprompt            assign a password to new user
  -E, --encrypted           encrypt stored password
  -N, --unencrypted         do no encrypt stored password
  -i, --sysid=SYSID         select sysid for new user
  -e, --echo                show the commands being sent to the server
  -q, --quiet               don't write any messages
  --help                    show this help, then exit
  --version                 output version information, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -W, --password            prompt for password to connect

If one of -a, -A, -d, -D, and USERNAME is not specified, you will
be prompted interactively.

Report bugs to <pgsql-bugs@postgresql.org>.
bash-2.05a$ createuser -U postgres -E -e -P
Enter name of user to add: jurn
Enter password for new user:
Enter it again:
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER jurn ENCRYPTED PASSWORD 'secret' CREATEDB NOCREATEUSER;
CREATE USER
bash-2.05a$

From the above log, I've added a newuser called 'jurn', with the password called 'secret'. However, anyone on the machine can still login to the database as user 'postgres'. To set a password on the 'postgres' user, login as that user.

bash-2.05a$ psql -U postgres -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(2 rows)

bash-2.05a$ psql -U postgres template1
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# ALTER USER postgres ENCRYPTED PASSWORD 'very_secret';
ALTER USER
template1=# \q
bash-2.05a$

Client Authentication

Although you've added another user and changed the postgres user password, clients may still be able to connect without authenticating. Read the section on Client Authentication in the documentation. You must edit the configuration file pg_hba.conf, that is found in the data directory before any password authentication will even be used. The configuration we use for local unix domain, and local TCP users is:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
  local all         all                                             md5
  host  all         all         127.0.0.1         255.255.255.255   md5

Now whenever we try to login with the postgres user, we must specify the password 'very_secret'.