fix(postgres): reassign dropped roles' objects to an unprivileged role - #4574

## 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](https://github.com/cozystack/website) -
follow-up:
- [ ]
[cozystack/terraform-provider-cozystack](https://github.com/cozystack/terraform-provider-cozystack)
- follow-up: not filed yet, see the note above
- [ ]
[cozystack/ansible-cozystack](https://github.com/cozystack/ansible-cozystack)
- follow-up:
- [ ] [cozystack/ccp](https://github.com/cozystack/ccp) - follow-up:
- [ ] [cozystack/talm](https://github.com/cozystack/talm) - follow-up:
- [ ] [cozystack/cozyhr](https://github.com/cozystack/cozyhr) -
follow-up:
- [ ] [cozystack/cozy-proxy](https://github.com/cozystack/cozy-proxy) -
follow-up:
- [ ]
[cozystack/cozystack-telemetry-server](https://github.com/cozystack/cozystack-telemetry-server)
- follow-up:
- [ ]
[cozystack/external-apps-example](https://github.com/cozystack/external-apps-example)
- follow-up:
- [ ] [cozystack/examples](https://github.com/cozystack/examples) -
follow-up:

### Release note

```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.
```


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## 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.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->