Roles¶
There are several definitions of roles in Charmed PostgreSQL:
Predefined PostgreSQL roles
Instance-level database/relation-specific roles
LDAP-specific roles
Extra user roles relation flag
See also: Users
PostgreSQL 14 roles¶
Roles from the upstream PostgreSQL application:
test123=> 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 14 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