LLI Wiki — My Digital Knowledge Base

Create users and grant privileges in PostgreSQL

psql commands to create databases and users, change a password, and assign roles and privileges.

This page gathers the commands to open a PostgreSQL session, create databases and users, change a password and grant privileges.

Open a psql session

sudo -u postgres psql postgres

or, in two steps:

sudo su postgres
psql

Create a database and a user

CREATE DATABASE database_name;
CREATE USER my_username WITH PASSWORD 'my_password';
GRANT ALL PRIVILEGES ON DATABASE "database_name" to my_username;

Interactive variant from the shell:

createuser --interactive --pwprompt

Change a password

From psql:

\password postgres

or with an SQL statement:

alter user <username> with encrypted password '<password>';

Grant attributes to a user

ALTER USER user1 CREATEDB;
ALTER USER user1 CREATEROLE;

Create a dba role and an associated user

create role dba with nosuperuser createdb createrole nologin replication bypassrls;
grant usage on schema public to dba;
alter default privileges in schema public grant all on tables to dba;
alter default privileges in schema public grant all on sequences to dba;
grant connect on database myDatabase to dba;
grant select on all tables in schema public to dba;
grant select on all sequences in schema public to dba;
grant all privileges on all tables in schema public to dba;
create user dba_user login inherit encrypted password '<strong_password>' in role dba;

CREATE ROLE and ALTER ROLE options

Option Effect
SUPERUSER / NOSUPERUSER Superuser privileges
CREATEDB / NOCREATEDB Create databases
CREATEROLE / NOCREATEROLE Create roles
CREATEUSER / NOCREATEUSER Deprecated form of SUPERUSER / NOSUPERUSER
INHERIT / NOINHERIT Inherit the privileges of the roles the role is a member of
LOGIN / NOLOGIN Allow login
REPLICATION / NOREPLICATION Allow replication connections
BYPASSRLS / NOBYPASSRLS Bypass row-level security (RLS) policies
CONNECTION LIMIT connlimit Maximum number of concurrent connections
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' Password of the role
VALID UNTIL 'timestamp' Expiration date of the password

Connection failures

If the connection is refused (for example "password authentication failed for user postgres"), check the pg_hba.conf authentication file. On an old 8.4 installation the path was /etc/postgresql/8.4/main/pg_hba.conf; adapt the path to the installed version.

Notes

Mastodon LLIMastodon SocialFosstodon