Skip to content

Commit d76203b

Browse files
FxKuFxKu
authored
Bootstrapped databases with best practice role setup (zalando#843)
* PreparedDatabases with default role setup * merge changes from master * include preparedDatabases spec check when syncing databases * create a default preparedDB if not specified * add more default privileges for schemas * use empty brackets block for undefined objects * cover more default privilege scenarios and always define admin role * add DefaultUsers flag * support extensions and defaultUsers for preparedDatabases * remove exact version in deployment manifest * enable CRD validation for new field * update generated code * reflect code review * fix typo in SQL command * add documentation for preparedDatabases feature + minor changes * some datname should stay * add unit tests * reflect some feedback * init users for preparedDatabases also on update * only change DB default privileges on creation * add one more section in user docs * one more sentence
1 parent cc635a0 commit d76203b

File tree

17 files changed

+927
-65
lines changed

17 files changed

+927
-65
lines changed

‎charts/postgres-operator/crds/postgresqls.yaml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,26 @@ spec:
273273
type: object
274274
additionalProperties:
275275
type: string
276+
preparedDatabases:
277+
type: object
278+
additionalProperties:
279+
type: object
280+
properties:
281+
defaultUsers:
282+
type: boolean
283+
extensions:
284+
type: object
285+
additionalProperties:
286+
type: string
287+
schemas:
288+
type: object
289+
additionalProperties:
290+
type: object
291+
properties:
292+
defaultUsers:
293+
type: boolean
294+
defaultRoles:
295+
type: boolean
276296
replicaLoadBalancer: # deprecated
277297
type: boolean
278298
resources:

‎docs/user.md

Lines changed: 166 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -94,7 +94,10 @@ created on every cluster managed by the operator.
9494
* `teams API roles`: automatically create users for every member of the team
9595
owning the database cluster.
9696

97-
In the next sections, we will cover those use cases in more details.
97+
In the next sections, we will cover those use cases in more details. Note, that
98+
the Postgres Operator can also create databases with pre-defined owner, reader
99+
and writer roles which saves you the manual setup. Read more in the next
100+
chapter.
98101

99102
### Manifest roles
100103

@@ -216,6 +219,166 @@ to choose superusers, group roles, [PAM configuration](https://github.com/CyberD
216219
etc. An OAuth2 token can be passed to the Teams API via a secret. The name for
217220
this secret is configurable with the `oauth_token_secret_name` parameter.
218221

222+
## Prepared databases with roles and default privileges
223+
224+
The `users` section in the manifests only allows for creating database roles
225+
with global privileges. Fine-grained data access control or role membership can
226+
not be defined and must be set up by the user in the database. But, the Postgres
227+
Operator offers a separate section to specify `preparedDatabases` that will be
228+
created with pre-defined owner, reader and writer roles for each individual
229+
database and, optionally, for each database schema, too. `preparedDatabases`
230+
also enable users to specify PostgreSQL extensions that shall be created in a
231+
given database schema.
232+
233+
### Default database and schema
234+
235+
A prepared database is already created by adding an empty `preparedDatabases`
236+
section to the manifest. The database will then be called like the Postgres
237+
cluster manifest (`-` are replaced with `_`) and will also contain a schema
238+
called `data`.
239+
240+
```yaml
241+
spec:
242+
preparedDatabases: {}
243+
```
244+
245+
### Default NOLOGIN roles
246+
247+
Given an example with a specified database and schema:
248+
249+
```yaml
250+
spec:
251+
preparedDatabases:
252+
foo:
253+
schemas:
254+
bar: {}
255+
```
256+
257+
Postgres Operator will create the following NOLOGIN roles:
258+
259+
| Role name | Member of | Admin |
260+
| -------------- | -------------- | ------------- |
261+
| foo_owner | | admin |
262+
| foo_reader | | foo_owner |
263+
| foo_writer | foo_reader | foo_owner |
264+
| foo_bar_owner | | foo_owner |
265+
| foo_bar_reader | | foo_bar_owner |
266+
| foo_bar_writer | foo_bar_reader | foo_bar_owner |
267+
268+
The `<dbname>_owner` role is the database owner and should be used when creating
269+
new database objects. All members of the `admin` role, e.g. teams API roles, can
270+
become the owner with the `SET ROLE` command. [Default privileges](https://www.postgresql.org/docs/12/sql-alterdefaultprivileges.html)
271+
are configured for the owner role so that the `<dbname>_reader` role
272+
automatically gets read-access (SELECT) to new tables and sequences and the
273+
`<dbname>_writer` receives write-access (INSERT, UPDATE, DELETE on tables,
274+
USAGE and UPDATE on sequences). Both get USAGE on types and EXECUTE on
275+
functions.
276+
277+
The same principle applies for database schemas which are owned by the
278+
`<dbname>_<schema>_owner` role. `<dbname>_<schema>_reader` is read-only,
279+
`<dbname>_<schema>_writer` has write access and inherit reading from the reader
280+
role. Note, that the `<dbname>_*` roles have access incl. default privileges on
281+
all schemas, too. If you don't need the dedicated schema roles - i.e. you only
282+
use one schema - you can disable the creation like this:
283+
284+
```yaml
285+
spec:
286+
preparedDatabases:
287+
foo:
288+
schemas:
289+
bar:
290+
defaultRoles: false
291+
```
292+
293+
Then, the schemas are owned by the database owner, too.
294+
295+
### Default LOGIN roles
296+
297+
The roles described in the previous paragraph can be granted to LOGIN roles from
298+
the `users` section in the manifest. Optionally, the Postgres Operator can also
299+
create default LOGIN roles for the database an each schema individually. These
300+
roles will get the `_user` suffix and they inherit all rights from their NOLOGIN
301+
counterparts.
302+
303+
| Role name | Member of | Admin |
304+
| ------------------- | -------------- | ------------- |
305+
| foo_owner_user | foo_owner | admin |
306+
| foo_reader_user | foo_reader | foo_owner |
307+
| foo_writer_user | foo_writer | foo_owner |
308+
| foo_bar_owner_user | foo_bar_owner | foo_owner |
309+
| foo_bar_reader_user | foo_bar_reader | foo_bar_owner |
310+
| foo_bar_writer_user | foo_bar_writer | foo_bar_owner |
311+
312+
These default users are enabled in the manifest with the `defaultUsers` flag:
313+
314+
```yaml
315+
spec:
316+
preparedDatabases:
317+
foo:
318+
defaultUsers: true
319+
schemas:
320+
bar:
321+
defaultUsers: true
322+
```
323+
324+
### Database extensions
325+
326+
Prepared databases also allow for creating Postgres extensions. They will be
327+
created by the database owner in the specified schema.
328+
329+
```yaml
330+
spec:
331+
preparedDatabases:
332+
foo:
333+
extensions:
334+
pg_partman: public
335+
postgis: data
336+
```
337+
338+
Some extensions require SUPERUSER rights on creation unless they are not
339+
whitelisted by the [pgextwlist](https://github.com/dimitri/pgextwlist)
340+
extension, that is shipped with the Spilo image. To see which extensions are
341+
on the list check the `extwlist.extension` parameter in the postgresql.conf
342+
file.
343+
344+
```bash
345+
SHOW extwlist.extensions;
346+
```
347+
348+
Make sure that `pgextlist` is also listed under `shared_preload_libraries` in
349+
the PostgreSQL configuration. Then the database owner should be able to create
350+
the extension specified in the manifest.
351+
352+
### From `databases` to `preparedDatabases`
353+
354+
If you wish to create the role setup described above for databases listed under
355+
the `databases` key, you have to make sure that the owner role follows the
356+
`<dbname>_owner` naming convention of `preparedDatabases`. As roles are synced
357+
first, this can be done with one edit:
358+
359+
```yaml
360+
# before
361+
spec:
362+
databases:
363+
foo: db_owner
364+
365+
# after
366+
spec:
367+
databases:
368+
foo: foo_owner
369+
preparedDatabases:
370+
foo:
371+
schemas:
372+
my_existing_schema: {}
373+
```
374+
375+
Adding existing database schemas to the manifest to create roles for them as
376+
well is up the user and not done by the operator. Remember that if you don't
377+
specify any schema a new database schema called `data` will be created. When
378+
everything got synced (roles, schemas, extensions), you are free to remove the
379+
database from the `databases` section. Note, that the operator does not delete
380+
database objects or revoke privileges when removed from the manifest.
381+
219382
## Resource definition
220383

221384
The compute resources to be used for the Postgres containers in the pods can be
@@ -586,8 +749,8 @@ don't know the value, use `103` which is the GID from the default spilo image
586749
OpenShift allocates the users and groups dynamically (based on scc), and their
587750
range is different in every namespace. Due to this dynamic behaviour, it's not
588751
trivial to know at deploy time the uid/gid of the user in the cluster.
589-
Therefore, instead of using a global `spilo_fsgroup` setting, use the `spiloFSGroup` field
590-
per Postgres cluster.
752+
Therefore, instead of using a global `spilo_fsgroup` setting, use the
753+
`spiloFSGroup` field per Postgres cluster.
591754

592755
Upload the cert as a kubernetes secret:
593756
```sh

‎manifests/complete-postgres-manifest.yaml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,17 @@ spec:
2121
- 127.0.0.1/32
2222
databases:
2323
foo: zalando
24+
preparedDatabases:
25+
bar:
26+
defaultUsers: true
27+
extensions:
28+
pg_partman: public
29+
pgcrypto: public
30+
schemas:
31+
data: {}
32+
history:
33+
defaultRoles: true
34+
defaultUsers: false
2435
postgresql:
2536
version: "12"
2637
parameters: # Expert section

‎manifests/minimal-postgres-manifest.yaml

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,5 +15,7 @@ spec:
1515
foo_user: [] # role for application foo
1616
databases:
1717
foo: zalando # dbname: owner
18+
preparedDatabases:
19+
bar: {}
1820
postgresql:
1921
version: "12"

‎manifests/postgresql.crd.yaml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -237,6 +237,26 @@ spec:
237237
type: object
238238
additionalProperties:
239239
type: string
240+
preparedDatabases:
241+
type: object
242+
additionalProperties:
243+
type: object
244+
properties:
245+
defaultUsers:
246+
type: boolean
247+
extensions:
248+
type: object
249+
additionalProperties:
250+
type: string
251+
schemas:
252+
type: object
253+
additionalProperties:
254+
type: object
255+
properties:
256+
defaultUsers:
257+
type: boolean
258+
defaultRoles:
259+
type: boolean
240260
replicaLoadBalancer: # deprecated
241261
type: boolean
242262
resources:

‎pkg/apis/acid.zalan.do/v1/crds.go

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -421,6 +421,43 @@ var PostgresCRDResourceValidation = apiextv1beta1.CustomResourceValidation{
421421
},
422422
},
423423
},
424+
"preparedDatabases": {
425+
Type: "object",
426+
AdditionalProperties: &apiextv1beta1.JSONSchemaPropsOrBool{
427+
Schema: &apiextv1beta1.JSONSchemaProps{
428+
Type: "object",
429+
Properties: map[string]apiextv1beta1.JSONSchemaProps{
430+
"defaultUsers": {
431+
Type: "boolean",
432+
},
433+
"extensions": {
434+
Type: "object",
435+
AdditionalProperties: &apiextv1beta1.JSONSchemaPropsOrBool{
436+
Schema: &apiextv1beta1.JSONSchemaProps{
437+
Type: "string",
438+
},
439+
},
440+
},
441+
"schemas": {
442+
Type: "object",
443+
AdditionalProperties: &apiextv1beta1.JSONSchemaPropsOrBool{
444+
Schema: &apiextv1beta1.JSONSchemaProps{
445+
Type: "object",
446+
Properties: map[string]apiextv1beta1.JSONSchemaProps{
447+
"defaultUsers": {
448+
Type: "boolean",
449+
},
450+
"defaultRoles": {
451+
Type: "boolean",
452+
},
453+
},
454+
},
455+
},
456+
},
457+
},
458+
},
459+
},
460+
},
424461
"replicaLoadBalancer": {
425462
Type: "boolean",
426463
Description: "Deprecated",

‎pkg/apis/acid.zalan.do/v1/postgresql_type.go

Lines changed: 32 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -50,24 +50,25 @@ type PostgresSpec struct {
5050
// load balancers' source ranges are the same for master and replica services
5151
AllowedSourceRanges []string `json:"allowedSourceRanges"`
5252

53-
NumberOfInstances int32 `json:"numberOfInstances"`
54-
Users map[string]UserFlags `json:"users"`
55-
MaintenanceWindows []MaintenanceWindow `json:"maintenanceWindows,omitempty"`
56-
Clone CloneDescription `json:"clone"`
57-
ClusterName string `json:"-"`
58-
Databases map[string]string `json:"databases,omitempty"`
59-
Tolerations []v1.Toleration `json:"tolerations,omitempty"`
60-
Sidecars []Sidecar `json:"sidecars,omitempty"`
61-
InitContainers []v1.Container `json:"initContainers,omitempty"`
62-
PodPriorityClassName string `json:"podPriorityClassName,omitempty"`
63-
ShmVolume *bool `json:"enableShmVolume,omitempty"`
64-
EnableLogicalBackup bool `json:"enableLogicalBackup,omitempty"`
65-
LogicalBackupSchedule string `json:"logicalBackupSchedule,omitempty"`
66-
StandbyCluster *StandbyDescription `json:"standby"`
67-
PodAnnotations map[string]string `json:"podAnnotations"`
68-
ServiceAnnotations map[string]string `json:"serviceAnnotations"`
69-
TLS *TLSDescription `json:"tls"`
70-
AdditionalVolumes []AdditionalVolume `json:"additionalVolumes,omitempty"`
53+
NumberOfInstances int32 `json:"numberOfInstances"`
54+
Users map[string]UserFlags `json:"users"`
55+
MaintenanceWindows []MaintenanceWindow `json:"maintenanceWindows,omitempty"`
56+
Clone CloneDescription `json:"clone"`
57+
ClusterName string `json:"-"`
58+
Databases map[string]string `json:"databases,omitempty"`
59+
PreparedDatabases map[string]PreparedDatabase `json:"preparedDatabases,omitempty"`
60+
Tolerations []v1.Toleration `json:"tolerations,omitempty"`
61+
Sidecars []Sidecar `json:"sidecars,omitempty"`
62+
InitContainers []v1.Container `json:"initContainers,omitempty"`
63+
PodPriorityClassName string `json:"podPriorityClassName,omitempty"`
64+
ShmVolume *bool `json:"enableShmVolume,omitempty"`
65+
EnableLogicalBackup bool `json:"enableLogicalBackup,omitempty"`
66+
LogicalBackupSchedule string `json:"logicalBackupSchedule,omitempty"`
67+
StandbyCluster *StandbyDescription `json:"standby"`
68+
PodAnnotations map[string]string `json:"podAnnotations"`
69+
ServiceAnnotations map[string]string `json:"serviceAnnotations"`
70+
TLS *TLSDescription `json:"tls"`
71+
AdditionalVolumes []AdditionalVolume `json:"additionalVolumes,omitempty"`
7172

7273
// deprecated json tags
7374
InitContainersOld []v1.Container `json:"init_containers,omitempty"`
@@ -84,6 +85,19 @@ type PostgresqlList struct {
8485
Items []Postgresql `json:"items"`
8586
}
8687

88+
// PreparedDatabase describes elements to be bootstrapped
89+
type PreparedDatabase struct {
90+
PreparedSchemas map[string]PreparedSchema `json:"schemas,omitempty"`
91+
DefaultUsers bool `json:"defaultUsers,omitempty" defaults:"false"`
92+
Extensions map[string]string `json:"extensions,omitempty"`
93+
}
94+
95+
// PreparedSchema describes elements to be bootstrapped per schema
96+
type PreparedSchema struct {
97+
DefaultRoles *bool `json:"defaultRoles,omitempty" defaults:"true"`
98+
DefaultUsers bool `json:"defaultUsers,omitempty" defaults:"false"`
99+
}
100+
87101
// MaintenanceWindow describes the time window when the operator is allowed to do maintenance on a cluster.
88102
type MaintenanceWindow struct {
89103
Everyday bool

0 commit comments

Comments
 (0)