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
- To restrict access to a database, revoke the
CONNECTprivilege granted toPUBLIC(REVOKE CONNECT ON DATABASE database_name FROM PUBLIC;), then grant it to the new user. - A monitoring account does not need superuser status: prefer predefined roles (for example
pg_monitor) or targeted privileges. - The
REPLICATIONandBYPASSRLSattributes are extended privileges: grant them only when needed.