PostgreSQL User & Role Management

Users and Roles:

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. 

Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects.

Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

Role Attributes:

CREATEDB                    NOCREATEDB
CREATEROLE             NOCREATEROLE
LOGIN                        NOLOGIN
SUPERUSER                NOSUPERUSER
INHERIT                    NOINHERIT
BYPASSRLS                NOBYPASSRLS
REPLICATION                NOREPLICATION
PASSWORD
ENCRYPTED PASSWORD
CONNECTION LIMIT
VALID UNTIL

work scenarios examples:

postgres=# create role readrole;
CREATE ROLE
postgres=#
postgres=# \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 readrole  | Cannot login
postgres=#
postgres=# create role rwrole;
CREATE ROLE
postgres=# \du+
                                    List of roles
 Role name |                         Attributes                         | Description
-----------+------------------------------------------------------------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS |
 readrole  | Cannot login                                               |
 rwrole    | Cannot login                                               |

postgres=#
postgres=# grant usage ON schema public TO readrole ;
GRANT
postgres=#
postgres=# grant select on all tables in schema public TO readrole ;
GRANT
postgres=# grant usage ON schema public TO rwrole ;
GRANT
postgres=# grant all ON
aaa                       ALL SEQUENCES IN SCHEMA   emp                       information_schema.       PROCEDURE                 SEQUENCE                  TYPE
ALL FUNCTIONS IN SCHEMA   ALL TABLES IN SCHEMA      FOREIGN DATA WRAPPER      LANGUAGE                  public.                   TABLE
ALL PROCEDURES IN SCHEMA  DATABASE                  FOREIGN SERVER            LARGE OBJECT              ROUTINE                   TABLESPACE
ALL ROUTINES IN SCHEMA    DOMAIN                    FUNCTION                  PARAMETER                 SCHEMA                    test

postgres=# grant all privileges on all tables in schema public to rwrole ;
GRANT

postgres=# \c postgres dev
You are now connected to database "postgres" as user "dev".

postgres=> \dt+
                                     List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+------+-------+----------+-------------+---------------+------------+-------------
 public | aaa  | table | postgres | permanent   | heap          | 16 kB      |
 public | emp  | table | postgres | permanent   | heap          | 24 MB      |
 public | test | table | postgres | permanent   | heap          | 8192 bytes |
(3 rows)

postgres=> select * from aaa;
 id | name
----+-------
  2 | sdsad
  1 | muthu
(2 rows)

postgres=> insert into aaa values (1,'sadsa');
ERROR:  permission denied for table aaa

postgres=# \c postgres dba
You are now connected to database "postgres" as user "dba".
postgres=> select * from aaa ;
 id | name
----+-------
  2 | sdsad
  1 | muthu
(2 rows)

postgres=> insert into aaa values (3,'siva');
INSERT 0 1

How to grant access to users in PostgreSQL?

Here are some common statement to grant access to a PostgreSQL user:

Grant CONNECT to the database:

GRANT CONNECT ON DATABASE database_name TO username;

Grant USAGE on schema:

GRANT USAGE ON SCHEMA schema_name TO username;

Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

Grant all privileges on all tables in the schema:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

Grant all privileges on all sequences in the schema:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;

Grant all privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Grant permission to create database:

ALTER USER username CREATEDB;

Make a user superuser:

ALTER USER myuser WITH SUPERUSER;

Remove superuser status:

ALTER USER username WITH NOSUPERUSER;

Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default. For example:

ALTER DEFAULT PRIVILEGES FOR USER username IN SCHEMA schema_name GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

How to show particular user table privileges:

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

postgres=#
postgres=# select table_schema,table_name,privilege_type from information_schema.role_table_grants where grantee='dba';
 table_schema | table_name | privilege_type
--------------+------------+----------------
(0 rows)

postgres=# select table_schema,table_name,privilege_type from information_schema.role_table_grants where grantee='dev';
 table_schema | table_name | privilege_type
--------------+------------+----------------
(0 rows)

postgres=# select table_schema,table_name,privilege_type from information_schema.role_table_grants where grantee='rwrole';
 table_schema | table_name | privilege_type
--------------+------------+----------------
 public       | test       | INSERT
 public       | test       | SELECT
 public       | test       | UPDATE
 public       | test       | DELETE
 public       | test       | TRUNCATE
 public       | test       | REFERENCES
 public       | test       | TRIGGER
 public       | emp        | INSERT
 public       | emp        | SELECT
 public       | emp        | UPDATE
 public       | emp        | DELETE
 public       | emp        | TRUNCATE
 public       | emp        | REFERENCES
 public       | emp        | TRIGGER
 public       | aaa        | INSERT
 public       | aaa        | SELECT
 public       | aaa        | UPDATE
 public       | aaa        | DELETE
 public       | aaa        | TRUNCATE
 public       | aaa        | REFERENCES
 public       | aaa        | TRIGGER
(21 rows)

postgres=# select table_schema,table_name,privilege_type from information_schema.role_table_grants where grantee='readrole';
 table_schema | table_name | privilege_type
--------------+------------+----------------
 public       | test       | SELECT
 public       | emp        | SELECT
 public       | aaa        | SELECT
(3 rows)