feat(postgres): add per-user createRole and bypassRls attributes - #3505

What this PR does

• Adds users.<name>.createRole to the postgres chart, which sets the CREATEROLE role attribute on a managed login user.
• Adds users.<name>.bypassRls, which sets BYPASSRLS on the same user. The two attributes are needed together to provision a privileged service role, see below.
• Sets createrole_self_grant as a role-level default alongside createRole, without which the attribute is granted but not usable.
• Refuses createRole on PostgreSQL 15 and older, where it is an escalation path rather than a bounded privilege.
• Makes the init job record the roles it manages in a superuser-only marker instead of trusting role comments and refuses to adopt a role it did not create once createRole has been used, see below.
• Adds tests/user_createrole_test.yaml: defaults, both explicit values for each attribute, composition with replication and with each other, the createrole_self_grant set and reset paths, and the version guard on v15 and v13. Adds tests/managed_roles_test.yaml for the marker and the adoption checks.

Today a tenant that needs to create its own roles has no way to do it through the chart, because role attributes are not inherited through role membership: granting CREATEROLE to the generated <db>_admin group role has no effect on the users that are members of it, and <db>_admin is NOINHERIT anyway. The attribute has to be set on the login user itself, which only the init script can do. The remaining option is to hand the tenant the postgres superuser, which also gives it COPY ... FROM PROGRAM, arbitrary file access and command execution inside the instance pod. These flags are the narrow version of that: the tenant manages its own roles, and the superuser stays internal.

Both attributes are reconciled on every init-script run, like replication, so setting one back to false emits the NO... form and revokes it.

Why bypassRls belongs in the same PR

CREATEROLE on its own does not let a tenant reproduce a role layout that uses row-level security, which is the common case this feature exists for. PostgreSQL only lets a role create other roles carrying BYPASSRLS if it holds the attribute itself, so CREATE ROLE service_role ... BYPASSRLS fails with Only roles with the BYPASSRLS attribute may create roles with the BYPASSRLS attribute. Exposing createRole without bypassRls therefore ships a feature that stops one statement short of the goal.

bypassRls is not gated on a PostgreSQL version, because it carries no escalation of its own: BYPASSRLS waives row-level security inside the tenant's own databases, and the database owner already reads every row in them. It grants no reach outside the tenant's data.

Why createrole_self_grant is set with the attribute

This is the non-obvious half. PostgreSQL 16 grants a CREATEROLE holder only ADMIN OPTION on the roles it creates, governed by createrole_self_grant, whose default is empty. ADMIN OPTION allows granting the role onward but not SET ROLE to it, and creating an object owned by another role requires SET. The attribute therefore looks functional -- CREATE ROLE succeeds -- while the very next statement a tenant writes, CREATE SCHEMA ... AUTHORIZATION <new role>, fails with must be able to SET ROLE. Every tenant granted createRole would hit this and have to discover the parameter, so the chart sets it as a role-level default and resets it when the attribute is withdrawn.

Why the init job stops trusting role comments and role names

The init job runs as the superuser, and it trusted two things a CREATEROLE holder controls for the roles it creates. It picked the users and roles to drop by their comment, and on PostgreSQL 16 a CREATEROLE holder may COMMENT ON ROLE every role it created, so it could hand the chart for deletion a role the chart never created. It also adopted any role that already existed under a declared name, so a role created under a name an administrator declares later stayed under its creator's control, for example debezium before users.debezium.replication: true, or newdb_admin before databases.newdb.

Neither case can be caught from the catalog. Creating a role through an intermediate role and then dropping that role leaves the squatted role with no row in pg_auth_members at all, while the objects, role settings and default privileges planted in it stay. On 17.11 such a debezium had zero memberships, and after the chart adopted it migrator could still run code as debezium with REPLICATION through a function it had planted. The chart therefore keeps its own record:

• Chart-managed roles are members of cozystack:managed, a NOLOGIN role that only the superuser can grant. On 16+ the membership is WITH INHERIT FALSE, SET FALSE. The name is outside the username and database charsets. The delete passes act only on these members. On its first run the chart marks every role it had commented before, when no role could yet hold CREATEROLE through it.
• Once any user has held createRole, recorded as cozystack.createrole_granted on the postgres database (the same mechanism as cozystack.adopted_restore), the chart refuses to adopt an existing role it did not mark, both for users and for <db>_admin/<db>_readonly. The init job fails with the role's name, and a platform administrator must drop the role. Before that point adoption works as before, so a fresh install that declares users.app still adopts the role CNPG bootstraps.

The PostgreSQL 16 floor on createRole

The floor is a security boundary, not a cosmetic check. Before PostgreSQL 16, CREATEROLE allowed granting membership in any non-superuser role, so a user holding it could grant itself pg_execute_server_program and reach command execution on the instance, which is exactly what not exposing the superuser is meant to prevent. I verified both sides on a real cluster: on 15.15 GRANT pg_execute_server_program TO <createrole_user> succeeds and COPY ... FROM PROGRAM 'id' then returns uid=26(postgres); on 16.11 and 18.1 the same grant is refused because it now requires ADMIN option on the role. PostgreSQL 16 restricted CREATEROLE to roles the grantee administers, which is what makes the attribute safe to expose. The guard uses the same fail-fast SECURITY: idiom as the blocked postgresql.parameters list in templates/db.yaml.

Note that a grant obtained on PostgreSQL 15 survives a major upgrade: after moving an instance to 16.11 the user kept its membership in pg_execute_server_program even though new attempts were refused. Operators who exposed CREATEROLE out of band on an older major should audit and revoke memberships in pg_execute_server_program, pg_read_server_files and pg_write_server_files before relying on the floor.

Verification

The marker, adoption and createRole paths were rendered and run against live PostgreSQL 13, 15, 16 and 17 containers. An upgrade from the previous chart marks the roles it already managed and removes a dropped user. What happens to a dropped role's objects comes from #4574 and is covered by its tests. A fresh install adopts the CNPG app role in the same run that grants createRole. A forged comment is ignored, and squatted debezium and newdb_admin roles stop the init job with nothing granted to them. The orphaned-role pass still drops the roles of a removed database, and every re-run is clean. Reverting each part of the fix in turn fails tests/managed_roles_test.yaml.

Beyond the chart unit tests, I replayed a real migration checklist against a live PostgreSQL 17.7 instance, connected as the managed login user and never as the superuser: creation of four roles with their attributes including CREATEROLE and BYPASSRLS, CREATE SCHEMA ... AUTHORIZATION landing on the requested owner, trusted extensions into a named schema, per-role and per-database search_path, per-role statement_timeout and idle_in_transaction_session_timeout, RLS including FORCE ROW LEVEL SECURITY verified end to end (a plain role sees zero rows, the BYPASSRLS role sees them all), and SECURITY DEFINER functions. All of it succeeds without the superuser. The one thing that remains out of reach is CREATE EVENT TRIGGER, which PostgreSQL restricts to superusers and no chart flag can change.

Other attributes and the predefined roles that a managed-service admin role usually wants (pg_monitor, pg_read_all_settings, pg_maintain) can follow the same per-user pattern in a later PR, minus the three that grant OS access (pg_execute_server_program, pg_read_server_files, pg_write_server_files).

Screenshots

No UI change.

Downstream repositories

packages/apps/postgres/values.schema.json gains two fields, which per the trigger map reaches cozystack/terraform-provider-cozystack: its postgres resource carries a hand-maintained schema plus an expand/flatten pair for users, with no codegen from values.schema.json, so createRole and bypassRls will be invisible to Terraform users until they are added there by hand. I searched that repository and found no open PR or issue covering it (the only open PR is a dependabot Go module bump). I have not filed the follow-up, so I am leaving the box unticked rather than claiming work that does not exist. Happy to open the issue or the provider PR if a maintainer confirms that is the preferred route.

I walked the rest of the map against the diff and found nothing else: no package is added, renamed or removed, no version enum changes, no default changes, release.prefix and the output Secret and Service names are untouched, and no CRD outside the regenerated packages/system/postgres-rd/cozyrds/postgres.yaml is affected. The README.md parameter table is regenerated here and postgres is already in the website generator's app list, so the website path stays automated.

• No downstream repository is affected by this change
• cozystack/website - follow-up:
• cozystack/terraform-provider-cozystack - follow-up: not filed yet, see the note above
• cozystack/ansible-cozystack - follow-up:
• cozystack/ccp - follow-up:
• cozystack/talm - follow-up:
• cozystack/cozyhr - follow-up:
• cozystack/cozy-proxy - follow-up:
• cozystack/cozystack-telemetry-server - follow-up:
• cozystack/external-apps-example - follow-up:
• cozystack/examples - follow-up:

Release note

feat(postgres): add `users.<name>.createRole` and `users.<name>.bypassRls`, which grant the CREATEROLE and BYPASSRLS attributes to a managed user so a tenant can create and manage its own roles, including roles that bypass row-level security, without the postgres superuser. `createRole` also sets `createrole_self_grant` on the user so that roles it creates can own objects. `createRole` requires PostgreSQL 16 or newer: on older majors CREATEROLE can be used to grant oneself pg_execute_server_program and reach command execution on the instance, so the chart refuses to render the attribute there. The init job now records the roles it manages as members of a superuser-only `cozystack:managed` role instead of trusting role comments. Once `createRole` has been used on an instance, it refuses to adopt an existing role it did not create.

Summary by CodeRabbit

• New Features

  • PostgreSQL user configuration now supports createRole and bypassRls privileges.
  • These privileges can be combined with replication access. Enabling createRole also configures the role’s self-grant behavior.
  • createRole requires PostgreSQL 16 or newer; configuring it on older versions is rejected.

• Documentation

  • Updated configuration schemas, examples, and guides to describe the new options and PostgreSQL version requirements.