Role profiles¶

The role command line entry point exposes a set-profile sub-command. That sub-command allows to link a role, a database and a schema with what we call a privileges profile.

Warning

Role profiles feature in pglift should be considered as a last-resort option. It is recommended to only use it when other approaches (e.g. such as predefined roles pg_read_all_data, pg_write_all_data) cannot be used.

pglift comes with the following profiles configured:

read-only, which executes GRANT with the following options and objects:

SELECT on tables and sequences
EXECUTE on routines (functions, aggregate functions and procedures)
USAGE on types

read-write, which gives the read and write privileges on a specific database and schema to the role. When using that profile, pglift use the GRANT command with those specific privileges:
- SELECT, INSERT, DELETE, UPDATE, REFERENCES and TRUNCATE on tables
- SELECT and UPDATE on sequences
- EXECUTE on routines (functions, aggregate functions and procedures)
- USAGE on types

That sub-command gives privileges based on the owner of the schema. Basically when using our profiles, pglift executes GRANT and ALTER DEFAULT PRIVILEGES for objects created by the owner of the schema to the specified role. When the schema is owned by the pre-defined role pg_database_owner (available since PostgreSQL >=14 and owning the public schema by default), pglift retrieves the database owner and use it to alter the default privileges.

In psql, \du, \dp and \ddp commands can be used to display granted and revoked privileges managed by the set-profile sub-command [1].

Assigning profile for a role, database and schema¶

In this example, the db2 database has currently no default privileges defined.

$ pglift instance exec main -- psql db2
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.

[16/main] postgres@db2=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

pglift will be used to create some roles and give them privileges on the v schema with the set-profile sub-command.

Default privileges, handled by the set-profile command, are always related to the owner of the schema. pglift uses the schema owner as parameter for the FOR ROLE part of ALTER DEFAULT PRIVILEGES commands.

pglift can be used to assign a profile (read-write in this example) to a role (Eg: dba2) for the v schema on a specific database (db2).

$ pglift role set-profile dba1 --database db2 --schema v read-write
INFO setting profile 'read-wirte' for role 'dba1' on schema 'v' in database 'db2'

Privileges created by pglift when assigning a profile can be inspected with the \ddp command with the psql client or the pglift privileges command:

$ pglift instance exec main -- psql db2
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.

[16/main] postgres@db2=# \ddp
                Default access privileges
      Owner    | Schema |   Type   |  Access privileges
---------------+--------+----------+----------------------
 schema_owner  | v      | function | dba1=X/schema_owner
 schema_owner  | v      | sequence | dba1=rw/schema_owner
 schema_owner  | v      | table    | dba1=arwdDx/schema_owner
 schema_owner  | v      | type     | dba1=U/schema_owner
(4 rows)

$ pglift database privileges --default db2
┏━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ database ┃ schema ┃ object_type ┃ role ┃ privileges                                           ┃
┡━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ db2      │ v      │ FUNCTION    │ dba1 │ EXECUTE                                              │
│ db2      │ v      │ SEQUENCE    │ dba1 │ SELECT, UPDATE                                       │
│ db2      │ v      │ TABLE       │ dba1 │ DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE │
│ db2      │ v      │ TYPE        │ dba1 │ USAGE                                                │
└──────────┴────────┴─────────────┴──────┴──────────────────────────────────────────────────────┘

$ pglift database privileges db2
┏━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┓
┃ database ┃ schema ┃ object_type ┃ role         ┃ privileges                                                    ┃ object_name ┃ column_privileges ┃
┡━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩
│ db2      │ v      │ TABLE       │ dba1         │ DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE          │ state2      │                   │
│ db2      │ v      │ TABLE       │ schema_owner │ DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE │ state2      │                   │
└──────────┴────────┴─────────────┴──────────────┴───────────────────────────────────────────────────────────────┴─────────────┴───────────────────┘

pglift profiles are exclusive. pglift ensures role, database and schema are always linked to only one profile. When assigning a new profile to a role, a database and schema, we first revoke the existing privileges and then add the privileges corresponding to the requested profile.

In this example we change the profile of dba1, for the database db2 and schema v from the previously selected read-write profile to read-only:

$ pglift role set-profile dba1 --database db2 --schema v read-only
INFO     setting profile 'read-only' for role 'dba1' on schema 'v' in database 'db2'

$ pglift instance exec main -- psql db2
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.

[16/main] postgres@db2=# \ddp
                 Default access privileges
     Owner    | Schema |   Type   |  Access privileges
--------------+--------+----------+----------------------
 schema_owner | v      | function | dba1=X/schema_owner +
 schema_owner | v      | sequence | dba1=r/schema_owner +
 schema_owner | v      | table    | dba1=r/schema_owner +
 schema_owner | v      | type     | dba1=U/schema_owner +
(4 rows)

$ pglift database privileges --default db2
┏━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━┓
┃ database ┃ schema ┃ object_type ┃ role ┃ privileges ┃
┡━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━┩
│ db2      │ v      │ FUNCTION    │ dba1 │ EXECUTE    │
│ db2      │ v      │ SEQUENCE    │ dba1 │ SELECT     │
│ db2      │ v      │ TABLE       │ dba1 │ SELECT     │
│ db2      │ v      │ TYPE        │ dba1 │ USAGE      │
└──────────┴────────┴─────────────┴──────┴────────────┘

$ pglift database privileges db2
┏━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┓
┃ database ┃ schema ┃ object_type ┃ role         ┃ privileges                                                    ┃ object_name ┃ column_privileges ┃
┡━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩
│ db2      │ v      │ TABLE       │ dba1         │ SELECT                                                        │ state2      │                   │
│ db2      │ v      │ TABLE       │ schema_owner │ DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE │ state2      │                   │
└──────────┴────────┴─────────────┴──────────────┴───────────────────────────────────────────────────────────────┴─────────────┴───────────────────┘