3 Commits

Include renames

Author	SHA1	Message	Date
Mattia Eleuteri	21fa1fcdb1	feat(postgres): add per-user createRole and bypassRls attributes ... Without these, a tenant that needs to manage its own roles has no option but the postgres superuser, which also hands over filesystem access and command execution on the instance. Role attributes are not inherited through role membership, so the generated <db>_admin group role cannot carry them: they have to be set on the login user itself. users.<name>.createRole and users.<name>.bypassRls set CREATEROLE and BYPASSRLS on a managed login user. Both are reconciled on every init-script run, so setting one back to false emits the NO... form and revokes the attribute. createRole is refused on PostgreSQL 15 and older. 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 prevents. PostgreSQL 16 restricted CREATEROLE to roles the grantee administers. bypassRls carries no such gate: BYPASSRLS only waives row-level security inside the tenant's own databases, whose owner already reads every row, so it adds no reach. The two attributes are needed together, not separately. PostgreSQL only lets a role create other roles carrying BYPASSRLS if it holds the attribute itself, so provisioning a privileged service role fails with "Only roles with the BYPASSRLS attribute may create roles with the BYPASSRLS attribute" when only createRole is granted. createRole also sets createrole_self_grant as a role-level default. PostgreSQL 16 grants a CREATEROLE holder only ADMIN OPTION on the roles it creates, governed by that parameter, 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 next statement a tenant writes, CREATE SCHEMA ... AUTHORIZATION <new role>, fails with "must be able to SET ROLE". The default is reset when the attribute is withdrawn. The init job runs as the superuser, and before this change it trusted two things a CREATEROLE holder controls for the roles it creates: role comments and role names. It picked the users and roles to drop by their comment, and a PostgreSQL 16 CREATEROLE holder may comment every role it created, then hand it to the chart for deletion. It 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. Neither can be caught from the catalog: creating a role through an intermediate role and then dropping that role leaves the squatted role with no member at all, while the objects, settings and default privileges planted in it stay. The chart therefore records the roles it manages as members of "cozystack:managed", a role only the superuser can grant, and the delete passes act only on those. Its first run marks every role the chart had commented before, when no role could yet hold CREATEROLE through it. Once any user has held createRole, recorded as a setting of the postgres database, the chart refuses to adopt a role it did not mark and fails with the role's name, rather than hand a squatted role REPLICATION or the ownership of a database. Verified against live PostgreSQL instances. On 17 a migration checklist replayed as the login user, with no superuser: role creation with attributes, CREATE SCHEMA ... AUTHORIZATION with the requested owner, extensions in a named schema, per-role search_path and timeouts, RLS including FORCE ROW LEVEL SECURITY, and SECURITY DEFINER functions all succeed. On 17 the forged-comment and squatting paths are refused or ignored, the upgrade from the previous chart marks the roles it already managed, and a fresh install still adopts the role CNPG bootstraps for users.app. On 13, 15 and 16 the init script runs and re-runs cleanly. Assisted-by: LLM Signed-off-by: Mattia Eleuteri <mattia@hidora.io>	2026-10-06 16:26:59 +03:00
Aleksei Sviridkin	810d293421	fix(postgres): reassign dropped roles' objects to an unprivileged role ... When a user leaves values, or a managed database role is orphaned, the init-job reassigned everything the role owned to the postgres superuser before dropping it. Objects a tenant role created should not become superuser-owned. They now go to "cozystack:orphaned", a NOLOGIN role whose attributes and memberships the init-job resets on every run; its name is outside the charset allowed for users and databases, so values cannot declare it. Because that one role receives the objects of every dropped role, functions it owns are switched to SECURITY INVOKER and, on PostgreSQL 15 and later, its views to security_invoker. The cost is that grants and memberships of the dropped role do not carry over, so a view or function it owned can lose access it had through them; keep the user in values, or move such objects to another owner first, to preserve it. The change is not retroactive: objects earlier releases reassigned to postgres stay superuser-owned, and the backup docs describe how to find and hand them over. The orphaned-roles pass now reassigns and drops in every connectable database, as the user pass already did, so a role owning an object outside the default database no longer fails DROP ROLE on every run. Role names read from the catalog now reach SQL as psql variables instead of being spliced in by the shell, and the queries are echoed after substitution so the log names the role being dropped. Assisted-by: LLM Signed-off-by: Aleksei Sviridkin <f@lex.la>	2026-09-29 19:10:44 +03:00
Alexey Artamonov	2b5877eff9	feat(postgres)!: generate user passwords and read them at runtime ... users[].password is gone from the chart, its schema and the API type. Every application-user password is generated into <release>-credentials and kept across reconciles by lookup, so an upgraded release keeps the password it already has and a password left in values is ignored. The init-job mounts that Secret and applies each password with ALTER ROLE and a psql variable, so no password is rendered into the init-script Secret that Helm keeps in release history. While bootstrap is enabled the Secret is marked credentials-pending, and NOTES say the generated passwords apply once the init-job runs. Names reach superuser SQL and the init-script shell, so render-time guards reject a username, database or extension name outside [-._a-zA-Z0-9] or past the length PostgreSQL silently truncates: 63 bytes for a role or extension, 54 for a database, whose <db>_readonly role would otherwise truncate and fail the next run. A username over 63 bytes that the credentials Secret already carries still upgrades. users.postgres, the CNPG superuser, is rejected in every bootstrap state. Changing a database's key in values drops the database, so the reject message and the README give the rename that keeps the data: a platform administrator renames it and its roles as the CNPG superuser before the key changes. The init-job cannot hang its Helm hook: the readiness wait ends on elapsed time below activeDeadlineSeconds and the Job never retries, so a purged restore target fails with a diagnostic. The delete-users pass reads managed users from pg_shdescription instead of parsing the version-dependent \du+ layout, which on PostgreSQL 16 and later matched nothing and left a removed user able to log in; it compares names as CREATE ROLE stores them and reassigns a removed user's objects in every database before dropping it. A database without roles renders, and extension names are quoted for hyphenated names and folded to lowercase so they resolve as they did unquoted. A value that carries its own surrounding quotes, the only way a hyphenated extension rendered before, has that pair removed and keeps its case, so such a release still upgrades. The README reads a password with jq, since jsonpath splits a dotted key. Signed-off-by: Alexey Artamonov <aleksei.artamonov@aenix.io>	2026-09-24 18:10:57 +03:00