PostgreSQL: create a user with password and assign it to a database

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

To create a database user in PostgreSQL, do:

# su - postgres
$ createuser someuser -P
Enter password for new role:
Enter it again:


Then, to create a database and assign our user to it:

# su - postgres
$ psql
psql (9.5.4)
Type "help" for help.

postgres=# create database somedb;
CREATE DATABASE

postgres=# GRANT ALL PRIVILEGES ON DATABASE somedb TO someuser;
GRANT


To verify - try connect using a wrong password:

# psql -h localhost -U someuser somedb
Password for user someuser:
psql: FATAL:  password authentication failed for user "someuser"
FATAL:  password authentication failed for user "someuser"


This is connecting with a good password:

# psql -h localhost -U someuser somedb
Password for user remind:
psql (9.5.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

somedb=>