Skip to content

Commit 71ab455

Browse files
mattia-eleutericlaude
authored and
lexfrei
committed
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. Verified against a live PostgreSQL 17.7 instance by replaying a real migration checklist as the login user: 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 without the superuser. Assisted-by: LLM Co-Authored-By: Claude Opus 5 (1M context) <[email protected]> Signed-off-by: Mattia Eleuteri <[email protected]>
1 parent d9ed566 commit 71ab455

7 files changed

Lines changed: 218 additions & 2 deletions

File tree

‎api/apps/v1alpha1/postgresql/types.go‎

Lines changed: 4 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎packages/apps/postgres/README.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -262,6 +262,8 @@ For `sslmode=verify-full` to work, the CA bundle retrieved above must be saved t
262262
| ------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------- | ------- |
263263
| `users` | Users configuration map. Passwords are always auto-generated and stored in the `<release>-credentials` Secret; they cannot be set from values — read a user's password from that Secret. A `password` left over in values from before the field was removed is ignored by the render and draws an admission warning, but it is not inert on an upgraded release: the chart preserves whatever password is already in the Secret, which on the first upgrade is the value that was set before removal, so that value stays the live credential until it is rotated (dedicated rotation is tracked in cozystack/community#72). The live password lives only in the Secret. | `map[string]object` | `{}` |
264264
| `users[name].replication` | Whether the user has replication privileges. | `bool` | `false` |
265+
| `users[name].createRole` | Whether the user can create and manage roles (CREATEROLE). Requires PostgreSQL 16 or newer. | `bool` | `false` |
266+
| `users[name].bypassRls` | Whether the user can bypass row-level security policies (BYPASSRLS). A role can only create other roles carrying BYPASSRLS if it holds the attribute itself, so this is required alongside `createRole` to provision a privileged service role. | `bool` | `false` |
265267

266268

267269
### Databases configuration

‎packages/apps/postgres/templates/init-script.yaml‎

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -185,11 +185,29 @@ stringData:
185185
once per user, into the rendered init.sh.
186186
*/}}
187187
{{- range $user, $u := .Values.users }}
188+
{{- if and (default false $u.createRole) (lt (atoi (trimPrefix "v" $.Values.version)) 16) }}
189+
{{- fail (printf "SECURITY: users.%s.createRole requires PostgreSQL 16 or newer. On PostgreSQL 15 and older, a role holding CREATEROLE can grant itself any non-superuser role, including pg_execute_server_program, which escalates to command execution on the instance." $user) }}
190+
{{- end }}
188191
pw=$(cat "/credentials/{{ $user }}")
189192
psql -v ON_ERROR_STOP=1 -v pw="$pw" <<\EOT
190193
SELECT 'CREATE ROLE "{{ $user }}" LOGIN INHERIT;'
191194
WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $user }}')\gexec
192-
ALTER ROLE "{{ $user }}" WITH PASSWORD :'pw' LOGIN INHERIT {{ ternary "REPLICATION" "NOREPLICATION" (default false $u.replication) }};
195+
ALTER ROLE "{{ $user }}" WITH PASSWORD :'pw' LOGIN INHERIT {{ ternary "REPLICATION" "NOREPLICATION" (default false $u.replication) }} {{ ternary "CREATEROLE" "NOCREATEROLE" (default false $u.createRole) }} {{ ternary "BYPASSRLS" "NOBYPASSRLS" (default false $u.bypassRls) }};
196+
{{- /*
197+
PostgreSQL 16 grants a CREATEROLE holder only ADMIN OPTION on the roles it
198+
creates, governed by createrole_self_grant, whose default is empty. ADMIN
199+
OPTION alone allows granting the role onward but not SET ROLE to it, and
200+
creating an object owned by another role requires SET. Without this the
201+
attribute looks functional — CREATE ROLE succeeds — yet the very next
202+
statement a tenant writes, CREATE SCHEMA ... AUTHORIZATION <new role>,
203+
fails with "must be able to SET ROLE". Set as a role-level default so it
204+
applies to every session, and reset when the attribute is withdrawn.
205+
*/}}
206+
{{- if default false $u.createRole }}
207+
ALTER ROLE "{{ $user }}" SET createrole_self_grant = 'set, inherit';
208+
{{- else }}
209+
ALTER ROLE "{{ $user }}" RESET createrole_self_grant;
210+
{{- end }}
193211
COMMENT ON ROLE "{{ $user }}" IS 'user managed by helm';
194212
EOT
195213
{{- end }}

‎packages/apps/postgres/tests/user_createrole_test.yaml‎

Lines changed: 179 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,179 @@
1+
suite: per-user CREATEROLE attribute
2+
3+
# users.<name>.createRole grants the CREATEROLE role attribute to a managed login
4+
# user, so a tenant can create and manage its own roles without the postgres
5+
# superuser. Role attributes are not inherited through role membership, so the
6+
# attribute cannot be carried by the generated <db>_admin group role and has to be
7+
# set on the login user itself.
8+
#
9+
# The attribute is reconciled on every run of the init script: flipping createRole
10+
# back to false emits NOCREATEROLE and revokes it.
11+
#
12+
# The guard on PostgreSQL 16 is a security boundary, not a cosmetic check. Before
13+
# PostgreSQL 16, CREATEROLE allowed granting membership in any non-superuser role,
14+
# so a user holding it could grant itself pg_execute_server_program and reach
15+
# command execution on the instance. PostgreSQL 16 restricted CREATEROLE to roles
16+
# the grantee administers, which is what makes the attribute safe to expose.
17+
18+
templates:
19+
- templates/init-script.yaml
20+
21+
release:
22+
name: pg-test
23+
namespace: tenant-test
24+
25+
set:
26+
_cluster:
27+
scheduling: {}
28+
29+
tests:
30+
- it: "user is NOCREATEROLE by default"
31+
set:
32+
users:
33+
app:
34+
password: secret
35+
documentIndex: 1
36+
asserts:
37+
- matchRegex:
38+
path: stringData["init.sh"]
39+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* NOREPLICATION NOCREATEROLE NOBYPASSRLS;'
40+
41+
- it: "createRole=true emits CREATEROLE"
42+
set:
43+
version: v18
44+
users:
45+
app:
46+
password: secret
47+
createRole: true
48+
documentIndex: 1
49+
asserts:
50+
- matchRegex:
51+
path: stringData["init.sh"]
52+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* NOREPLICATION CREATEROLE NOBYPASSRLS;'
53+
54+
- it: "createRole=false emits NOCREATEROLE so the attribute is revoked"
55+
set:
56+
version: v18
57+
users:
58+
app:
59+
password: secret
60+
createRole: false
61+
documentIndex: 1
62+
asserts:
63+
- matchRegex:
64+
path: stringData["init.sh"]
65+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* NOCREATEROLE NOBYPASSRLS;'
66+
67+
- it: "createRole composes with replication"
68+
set:
69+
version: v18
70+
users:
71+
app:
72+
password: secret
73+
replication: true
74+
createRole: true
75+
documentIndex: 1
76+
asserts:
77+
- matchRegex:
78+
path: stringData["init.sh"]
79+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* REPLICATION CREATEROLE NOBYPASSRLS;'
80+
81+
- it: "createRole on PostgreSQL 15 fails fast"
82+
set:
83+
version: v15
84+
users:
85+
app:
86+
password: secret
87+
createRole: true
88+
asserts:
89+
- failedTemplate:
90+
errorMessage: "SECURITY: users.app.createRole requires PostgreSQL 16 or newer. On PostgreSQL 15 and older, a role holding CREATEROLE can grant itself any non-superuser role, including pg_execute_server_program, which escalates to command execution on the instance."
91+
92+
- it: "createRole on PostgreSQL 13 fails fast"
93+
set:
94+
version: v13
95+
users:
96+
app:
97+
password: secret
98+
createRole: true
99+
asserts:
100+
- failedTemplate:
101+
errorMessage: "SECURITY: users.app.createRole requires PostgreSQL 16 or newer. On PostgreSQL 15 and older, a role holding CREATEROLE can grant itself any non-superuser role, including pg_execute_server_program, which escalates to command execution on the instance."
102+
103+
- it: "PostgreSQL 15 renders fine as long as createRole is not requested"
104+
set:
105+
version: v15
106+
users:
107+
app:
108+
password: secret
109+
documentIndex: 1
110+
asserts:
111+
- matchRegex:
112+
path: stringData["init.sh"]
113+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* NOCREATEROLE NOBYPASSRLS;'
114+
115+
- it: "bypassRls=true emits BYPASSRLS"
116+
set:
117+
version: v18
118+
users:
119+
app:
120+
password: secret
121+
bypassRls: true
122+
documentIndex: 1
123+
asserts:
124+
- matchRegex:
125+
path: stringData["init.sh"]
126+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* NOCREATEROLE BYPASSRLS;'
127+
128+
- it: "bypassRls composes with createRole, which is what provisioning a privileged service role needs"
129+
set:
130+
version: v18
131+
users:
132+
app:
133+
password: secret
134+
createRole: true
135+
bypassRls: true
136+
documentIndex: 1
137+
asserts:
138+
- matchRegex:
139+
path: stringData["init.sh"]
140+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* CREATEROLE BYPASSRLS;'
141+
142+
- it: "bypassRls is allowed on PostgreSQL 15, it carries no escalation path of its own"
143+
set:
144+
version: v15
145+
users:
146+
app:
147+
password: secret
148+
bypassRls: true
149+
documentIndex: 1
150+
asserts:
151+
- matchRegex:
152+
path: stringData["init.sh"]
153+
pattern: 'ALTER ROLE "app" WITH PASSWORD .* NOCREATEROLE BYPASSRLS;'
154+
155+
- it: "createRole sets createrole_self_grant so the attribute is usable"
156+
set:
157+
version: v18
158+
users:
159+
app:
160+
password: secret
161+
createRole: true
162+
documentIndex: 1
163+
asserts:
164+
- matchRegex:
165+
path: stringData["init.sh"]
166+
pattern: "ALTER ROLE \"app\" SET createrole_self_grant = 'set, inherit';"
167+
168+
- it: "withdrawing createRole resets createrole_self_grant"
169+
set:
170+
version: v18
171+
users:
172+
app:
173+
password: secret
174+
createRole: false
175+
documentIndex: 1
176+
asserts:
177+
- matchRegex:
178+
path: stringData["init.sh"]
179+
pattern: 'ALTER ROLE "app" RESET createrole_self_grant;'

‎packages/apps/postgres/values.schema.json‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -271,6 +271,14 @@
271271
"additionalProperties": {
272272
"type": "object",
273273
"properties": {
274+
"bypassRls": {
275+
"description": "Whether the user can bypass row-level security policies (BYPASSRLS). A role can only create other roles carrying BYPASSRLS if it holds the attribute itself, so this is required alongside `createRole` to provision a privileged service role.",
276+
"type": "boolean"
277+
},
278+
"createRole": {
279+
"description": "Whether the user can create and manage roles (CREATEROLE). Requires PostgreSQL 16 or newer.",
280+
"type": "boolean"
281+
},
274282
"replication": {
275283
"description": "Whether the user has replication privileges.",
276284
"type": "boolean"

‎packages/apps/postgres/values.yaml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -231,6 +231,8 @@ autoscaling:
231231

232232
## @typedef {struct} User - User configuration.
233233
## @field {bool} [replication] - Whether the user has replication privileges.
234+
## @field {bool} [createRole] - Whether the user can create and manage roles (CREATEROLE). Requires PostgreSQL 16 or newer.
235+
## @field {bool} [bypassRls] - Whether the user can bypass row-level security policies (BYPASSRLS). A role can only create other roles carrying BYPASSRLS if it holds the attribute itself, so this is required alongside `createRole` to provision a privileged service role.
234236

235237
## @param {map[string]User} users - Users configuration map. Passwords are always auto-generated and stored in the `<release>-credentials` Secret; they cannot be set from values — read a user's password from that Secret. A `password` left over in values from before the field was removed is ignored by the render and draws an admission warning, but it is not inert on an upgraded release: the chart preserves whatever password is already in the Secret, which on the first upgrade is the value that was set before removal, so that value stays the live credential until it is rotated (dedicated rotation is tracked in cozystack/community#72). The live password lives only in the Secret.
236238
users: {}
@@ -241,6 +243,9 @@ users: {}
241243
## airflow: {}
242244
## debezium:
243245
## replication: true
246+
## migrator:
247+
## createRole: true
248+
## bypassRls: true
244249

245250
##
246251
## @section Databases configuration

‎packages/system/postgres-rd/cozyrds/postgres.yaml‎

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)