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
cnp_pooler_pgbouncer(when the PgBouncer
- The application user, set as the low-privilege owner of the application database
This process is described in the "Bootstrap" section.
managed stanza in the cluster spec, EDB Postgres for Kubernetes now provides full
lifecycle management for roles specified in
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
Here is an excerpt from that file:
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:
ensureattribute is not part of PostgreSQL. It enables declarative role management to create and remove roles. The two possible values are
present(the default) and
inheritattribute is true by default, following PostgreSQL conventions.
connectionLimitattribute defaults to -1, in line with PostgreSQL conventions.
- Role membership with
inRolesdefaults 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.
The declarative role management feature includes reconciling of role passwords. Passwords are managed in fundamentally different ways in the Kubernetes world and in PostgreSQL, and as a result there are a few things to note.
Managed role configurations may optionally specify the name of a Secret where the username and password are stored (encoded in Base64 as is usual in Kubernetes). For example:
This would assume the existence of a Secret called
containing a username and password. The username should match the role we
are setting the password for. For example, :
If there is no
passwordSecret specified for a role, the instance manager will
not try to CREATE / ALTER the role with a password. This keeps with PostgreSQL
conventions, where ALTER will not update passwords unless directed to with
If a role was initially created with a password, and we would like to set the
password to NULL in PostgreSQL, this necessitates being explicit on the part of
the user of EDB Postgres for Kubernetes.
To distinguish "no password provided in spec" from "set the password to NULL",
DisablePassword should be used.
Imagine we decided we would like to have no password on the
dante role in the
database. In such case we would specify the following:
NOTE: it is considered an error to set both
disablePassword on a given role.
This configuration will be rejected by the validation webhook.
VALID UNTIL role attribute in PostgreSQL controls password expiry. Roles
VALID UNTIL specified get NULL by default in PostgreSQL,
meaning that their password will never expire.
PostgreSQL uses a timestamp type for
VALID UNTIL, which includes support for
'infinity' indicating that the password never expires. Please see the
With declarative role management, the
validUntil attribute for managed roles
controls password expiry.
validUntil can only take:
- a Kubernetes timestamp, or
- be omitted (defaulting to
In the first case, the given
validUntil timestamp will be set in the database
VALID UNTIL attribute of the role.
In the second case (omitted
validUntil) the operator will ensure password
never expires, mirroring the behavior of PostgreSQL. Specifically:
- in case of new role, it will omit the
VALID UNTILclause in the role creation statement
- in case of existing role, it will set
VALID UNTILwas not set to
NULLin the database (this is due to PostgreSQL not allowing
VALID UNTIL NULLin the
ALTER ROLESQL statement)
The declarative role management feature has changed behavior since its
initial version (1.20.0). In 1.20.0, a role without a
lead to setting the password to NULL in PostgreSQL.
In practice there is little difference from 1.20.0.
New roles created without
passwordSecret will have a
The relevant change is when using the managed roles to manage roles that
had been previously created. In 1.20.0, doing this might inadvertently
result in setting existing passwords to
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
petrarcaas a member of the role (group)
poetsdoes not exist.
- We ask PostgreSQL to drop the role
dante, but the role
danteis the owner of the database
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…
The CRD status includes a section for the managed roles' status, as shown below:
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.
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.