.. SPDX-FileCopyrightText: 2024 Dalibo .. .. SPDX-License-Identifier: GPL-3.0-or-later 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 [#f1]_. Assigning profile for a role, database and schema ------------------------------------------------- In this example, the ``db2`` database has currently no `default privileges`_ defined. .. code-block:: console $ 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``). .. code-block:: console $ 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: .. code-block:: console $ 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``: .. code-block:: console $ 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' .. code-block:: console $ 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 │ │ └──────────┴────────┴─────────────┴──────────────┴───────────────────────────────────────────────────────────────┴─────────────┴───────────────────┘ .. [#f1] See the `privileges documentation`_ and the `privileges abbreviations`_ .. _`privileges documentation`: https://www.postgresql.org/docs/current/ddl-priv.html .. _`privileges abbreviations`: https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGES-SUMMARY-TABLE .. _`default privileges`: https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html