Databases & Users

CPO not only supports you in deploying your cluster, it also supports you in setting it up in terms of the database and users. CPO offers you three different options for this:

• Create roles
• Create databases
• preapared databases

The creation of users is based on the definition of the user name and the definition of the required rights for this user. Available rights are

• superuser
• inherit
• login
• nologin
• createrole
• createdb
• replication
• bypassrls

Unless explicitly defined via NOLOGIN, a created user automatically receives the LOGIN permission.

spec:
  users:
    db_owner:
    - login
    - createdb
    appl_user:
    - login

For each user created, CPO automatically creates a secret with username and password in the namespace of the cluster, which follows the following naming convention: [USERNAME].[CLUSTERNAME].credentials.postgresql.cpo.opensource.cybertec.at

If the secrets for an application are to be stored in a different namespace, for example, it is necessary to define the setting enable_cross_namespace_secret as true in the operator configuration. You can find more information about the operator configuration here.

The namespace must then be written before the user name.

spec:
  users:
    db_owner:
    - login
    - createdb
    app_namespace.appl_user:
    - login

Databases are basically created in a very similar way to users. The definition is based on the database name and the database owner.

spec:
  users:
    db_owner:
    - login
    - createdb
    app_namespace.appl_user:
    - login
  databases;
    app_db: app_namespace.appl_user

HINT: Be aware that the user name must be defined for the database owner in the same way as it is done in the users object.

The preparedDatabases object is available for a much more extensive setup of databases and users. In addition to the creation of databases and users, this also enables the creation of schemas and extensions. A more detailed rights management is also available.

Creating the preparedDatabases object already creates a database whose name is based on the cluster name. preparedDatabases: {}

HINT: For the database name, - is replaced with _ in the cluster name

To create your own database names and elements such as schemas and extensions within the database, an object must be created within preparedDatabases for each database.

spec:
  preparedDatabases:
    appl_db:
      extensions:
        dblink: public
      schemas:
        data: {}

This example creates a database with the name appl_db and creates a schema with the name data in it, as well as creating the dblink extension in the schema public.

For rights management, we distinguish between NOLOGIN roles and LOGIN roles. Users have login rights and inherit the other rights from the NOLOGIN role.

The roles are created if defaultroles is not explicitly set to false.

spec:
  preparedDatabases:
    appl_db:
      extensions:
        dblink: public
      schemas:
        data: {}

This creates roles for the schema owner, writer and reader

The roles described in the previous paragraph can be assigned to LOGIN roles via the users section in the manifest. Optionally, the Postgres operator can also create standard LOGIN roles for the database and each individual schema. These roles are given the suffix _user and inherit all rights from their NOLOGIN counterparts. Therefore, you cannot set defaultRoles to false and activate defaultUsers at the same time.

spec:
  preparedDatabases:
    appl_db:
      defaultUsers: true
      extensions:
        dblink: public
      schemas:
        data: {}
        history:
          defaultRoles: true
          defaultUsers: false

This example creates the following users and inheritances

Default access permissions are also defined for LOGIN roles when databases and schemas are created. This means that they are not currently set if defaultUsers (or defaultRoles for schemas) are activated at a later time.

For each user created by cpo with LOGIN permissions, the operator also creates a secret with username and password, as with the creation of roles via the users object.