Database Role Management v1
From its inception, EDB Postgres for Kubernetes has managed the creation of specific roles required in PostgreSQL instances:
- some reserved users, such as the
postgres
superuser,streaming_replica
andcnp_pooler_pgbouncer
(when the PgBouncerPooler
is used) - The application user, set as the low-privilege owner of the application database
This process is described in the "Bootstrap" section.
With the managed
stanza in the cluster spec, EDB Postgres for Kubernetes now provides full
lifecycle management for roles specified in .spec.managed.roles
.
This feature enables declarative management of existing roles, as well as the creation of new roles if they are not already present in the database. Role creation will occur after the database bootstrapping is complete.
An example manifest for a cluster with declarative role management can be found
in the file cluster-example-with-roles.yaml
.
Here is an excerpt from that file:
apiVersion: postgresql.k8s.enterprisedb.io/v1 kind: Cluster spec: managed: roles: - name: dante ensure: present comment: Dante Alighieri login: true superuser: false inRoles: - pg_monitor - pg_signal_backend
The role specification in spec.managed.roles
adheres to the
PostgreSQL structure and naming conventions.
Please refer to the API reference for
the full list of attributes you can define for each role.
A few points are worth noting:
- The
ensure
attribute is not part of PostgreSQL. It enables declarative role management to create and remove roles. The two possible values arepresent
(the default) andabsent
. - The
inherit
attribute is true by default, following PostgreSQL conventions. - The
connectionLimit
attribute defaults to -1, in line with PostgreSQL conventions. - Role membership with
inRoles
defaults to no memberships.
Declarative role management ensures that PostgreSQL instances align with the spec. If a user modifies role attributes directly in the database, the EDB Postgres for Kubernetes operator will revert those changes during the next reconciliation cycle.
Unrealizable role configurations
In PostgreSQL, in some cases, commands cannot be honored by the database and will be rejected. Please refer to the PostgreSQL documentation on error codes for details.
Role operations can produce such fundamental errors. Two examples:
- We ask PostgreSQL to create the role
petrarca
as a member of the role (group)poets
, butpoets
does not exist. - We ask PostgreSQL to drop the role
dante
, but the roledante
is the owner of the databaseinferno
.
These fundamental errors cannot be fixed by the database, nor the EDB Postgres for Kubernetes
operator, without clarification from the human administrator. The two examples
above could be fixed by creating the role poets
or dropping the database
inferno
respectively, but they might have originated due to human error, and
in such case, the "fix" proposed might be the wrong thing to do.
EDB Postgres for Kubernetes will record when such fundamental errors occur, and will display them in the cluster Status. Which segues into…
Status of managed roles
The CRD status includes a section for the managed roles' status, as shown below:
status: […snipped…] managedRolesStatus: byStatus: not-managed: - app pending-reconciliation: - dante - petrarca reconciled: - ariosto reserved: - postgres - streaming_replica cannotReconcile: dante: - 'could not perform DELETE on role dante: owner of database inferno' petrarca: - 'could not perform UPDATE_MEMBERSHIPS on role petrarca: role "poets" does not exist'
Note the special sub-section cannotReconcile
for operations the database (and
EDB Postgres for Kubernetes) cannot honor, and which require human intervention.
This section covers roles reserved for operator use and those that are not under declarative management, providing a comprehensive view of the roles in the database instances.
Important
In terms of backward compatibility, declarative role management is designed to ignore roles that exist in the database but are not included in the spec. The lifecycle of these roles will continue to be managed within PostgreSQL, allowing EDB Postgres for Kubernetes users to adopt this feature at their convenience.