Skip to content

Commit 53823a3

Browse files
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: Claude <[email protected]> Co-Authored-By: Claude Opus 5 (1M context) <[email protected]> Signed-off-by: Mattia Eleuteri <[email protected]>
1 parent a1b32f2 commit 53823a3

7 files changed

Lines changed: 224 additions & 7 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: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -258,11 +258,13 @@ For `sslmode=verify-full` to work, the CA bundle retrieved above must be saved t
258258

259259
### Users configuration
260260

261-
| Name | Description | Type | Value |
262-
| ------------------------- | -------------------------------------------- | ------------------- | ------- |
263-
| `users` | Users configuration map. | `map[string]object` | `{}` |
264-
| `users[name].password` | Password for the user. | `string` | `""` |
265-
| `users[name].replication` | Whether the user has replication privileges. | `bool` | `false` |
261+
| Name | Description | Type | Value |
262+
| ------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------- | ------- |
263+
| `users` | Users configuration map. | `map[string]object` | `{}` |
264+
| `users[name].password` | Password for the user. | `string` | `""` |
265+
| `users[name].replication` | Whether the user has replication privileges. | `bool` | `false` |
266+
| `users[name].createRole` | Whether the user can create and manage roles (CREATEROLE). Requires PostgreSQL 16 or newer. | `bool` | `false` |
267+
| `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` |
266268

267269

268270
### Databases configuration

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

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -48,9 +48,27 @@ stringData:
4848
{{- if and .Values.users (not (hasKey .Values.users "postgres")) }}
4949
psql -v ON_ERROR_STOP=1 <<\EOT
5050
{{- range $user, $u := .Values.users }}
51+
{{- if and (default false $u.createRole) (lt (atoi (trimPrefix "v" $.Values.version)) 16) }}
52+
{{- 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) }}
53+
{{- end }}
5154
SELECT 'CREATE ROLE "{{ $user }}" LOGIN INHERIT;'
5255
WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $user }}')\gexec
53-
ALTER ROLE "{{ $user }}" WITH PASSWORD '{{ index $passwords $user }}' LOGIN INHERIT {{ ternary "REPLICATION" "NOREPLICATION" (default false $u.replication) }};
56+
ALTER ROLE "{{ $user }}" WITH PASSWORD '{{ index $passwords $user }}' LOGIN INHERIT {{ ternary "REPLICATION" "NOREPLICATION" (default false $u.replication) }} {{ ternary "CREATEROLE" "NOCREATEROLE" (default false $u.createRole) }} {{ ternary "BYPASSRLS" "NOBYPASSRLS" (default false $u.bypassRls) }};
57+
{{- /*
58+
PostgreSQL 16 grants a CREATEROLE holder only ADMIN OPTION on the roles it
59+
creates, governed by createrole_self_grant, whose default is empty. ADMIN
60+
OPTION alone allows granting the role onward but not SET ROLE to it, and
61+
creating an object owned by another role requires SET. Without this the
62+
attribute looks functional — CREATE ROLE succeeds — yet the very next
63+
statement a tenant writes, CREATE SCHEMA ... AUTHORIZATION <new role>,
64+
fails with "must be able to SET ROLE". Set as a role-level default so it
65+
applies to every session, and reset when the attribute is withdrawn.
66+
*/}}
67+
{{- if default false $u.createRole }}
68+
ALTER ROLE "{{ $user }}" SET createrole_self_grant = 'set, inherit';
69+
{{- else }}
70+
ALTER ROLE "{{ $user }}" RESET createrole_self_grant;
71+
{{- end }}
5472
COMMENT ON ROLE "{{ $user }}" IS 'user managed by helm';
5573
{{- end }}
5674
EOT
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
"password": {
275283
"description": "Password for the user.",
276284
"type": "string"

‎packages/apps/postgres/values.yaml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -232,6 +232,8 @@ autoscaling:
232232
## @typedef {struct} User - User configuration.
233233
## @field {string} [password] - Password for the user.
234234
## @field {bool} [replication] - Whether the user has replication privileges.
235+
## @field {bool} [createRole] - Whether the user can create and manage roles (CREATEROLE). Requires PostgreSQL 16 or newer.
236+
## @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.
235237

236238
## @param {map[string]User} users - Users configuration map.
237239
users: {}
@@ -245,6 +247,10 @@ users: {}
245247
## password: qwerty123
246248
## debezium:
247249
## replication: true
250+
## migrator:
251+
## password: qwerty123
252+
## createRole: true
253+
## bypassRls: true
248254

249255
##
250256
## @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)