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, it’s 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 run 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 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’.
References
PostgreSQL Documentation is available online.