Roles¶
Charmed PostgreSQL 14 ships the minimal necessary roles logic: each application relation gets a user with dedicated role matching the resources owner. This can be fine-tuned using extra-users-roles relation flag.
See also: Users
Native PostgreSQL roles¶
postgres=# SELECT * FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_database_owner | f | t | f | f | f | f | -1 | ******** | | f | | 6171
pg_read_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6181
pg_write_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6182
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
...
Charmed PostgreSQL roles¶
postgres=# SELECT * FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
...
operator | t | t | t | t | t | t | -1 | ******** | | t | | 10
replication | f | t | f | f | t | t | -1 | ******** | | f | | 16384
rewind | f | t | f | f | t | f | -1 | ******** | | f | | 16385
postgres | t | t | f | f | t | f | -1 | ******** | | f | | 16386
backup | t | t | f | f | t | f | -1 | ******** | | f | | 16387
monitoring | f | t | f | f | t | f | -1 | ******** | | f | | 16388
admin | f | t | f | f | f | f | -1 | ******** | | f | | 16389
...
Relation-specific roles¶
For each application/relation, a dedicated user is created with a matching role and all resource ownership. The resources ownership is updated every time new users or roles are regenerated by re-relating.
Example of a simple application relation to PostgreSQL and creating table:
postgres=# SELECT * FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
...
relation_id_12 | f | t | t | t | t | f | -1 | ******** | | f | | 16416
...
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------------------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
...
relation_id_12 | 16416 | t | f | f | f | ******** | |
...
mydb=# \d+
List of relations
Schema | Name | Type | Owner | ...
--------+---------+-------+----------------+ ...
public | mytable | table | relation_id_12 | ...
When the same application is being related through PgBouncer, the extra users/roles created following the same logic as above:
postgres=# SELECT * FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
...
relation-14 | t | t | f | f | t | f | -1 | ******** | | f | | 16403
pgbouncer_auth_relation_14 | t | t | f | f | t | f | -1 | ******** | | f | | 16410
relation_id_13 | f | t | t | t | t | f | -1 | ******** | | f | | 16417
...
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------------------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
...
relation-14 | 16403 | f | t | f | f | ******** | |
pgbouncer_auth_relation_14 | 16410 | f | t | f | f | ******** | |
relation_id_13 | 16417 | t | f | f | f | ******** | |
...
mydb=# \d+
List of relations
Schema | Name | Type | Owner | ...
--------+---------+-------+----------------+ ...
public | mytable | table | relation_id_13 | ...
In this case there are several records created to:
relation_id_13- for relation between Application and PgBouncerrelation-14- for relation between PgBouncer and PostgreSQLpgbouncer_auth_relation_14- to authenticate end-users which connects PgBouncer
LDAP roles¶
To map LDAP users to PostgreSQL users, the dedicated LDAP groups have to be created before hand using Data Integrator charm. The result of such mapping will be a new PostgreSQL Roles:
postgres=# SELECT * FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
...
myrole | t | t | f | f | t | f | -1 | ******** | | f | | 16422