PostgreSQL Users/Roles

PostgreSQL uses the roles concept to manage database access permissions. A role can be a user or a group, depending on how you setup the role. A role that has login rights is called user.

List users/roles

\du+

select * from pg_roles;

Create users/roles

CREATE ROLE myusername WITH LOGIN PASSWORD 'mypassword' SUPERUSER CREATEROLE CREATEDB REPLICATION;

Alternative options...

CREATE USER myusername WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'mypassword';

Login without password

The recommended method is to create a file called .pgpass in your home directory. There is no obfuscation so make sure it is secure (at least: chmod 600). The file should contain a line for each hostname/port/database/username/password combination you will use. These need to match the values you will pass on the command line.

hostname:port:database:username:password

SCRAM

SCRAM password encryption was introduced in PostgreSQL 10.

Enable

SET password_encryption = 'scram';

Example Usage

CREATE ROLE myrole PASSWORD 'mypassword';

Check

SELECT substring(rolpassword, 1, 14) FROM pg_authid WHERE rolname = 'myroole';

Bibliography