User / Database Management

Crunchy Postgres for Kubernetes comes with some out-of-the-box conveniences for managing users and databases in your Postgres cluster. However, you may have requirements where you need to create additional users, adjust user privileges or add additional databases to your cluster.

For detailed information for how user and database management works in Crunchy Postgres for Kubernetes, please see the User Management section of the architecture guide.

Creating a New User

You can create a new user with the following snippet in the postgrescluster custom resource. Let's add this to our hippo database:

spec:
  users:
    - name: rhino
Copy

You can now apply the changes and see that the new user is created. Note the following:

• The user would only be able to connect to the default postgres database.
• The user will not have any connection credentials populated into the hippo-pguser-rhino Secret.
• The user is unprivileged.

Creating a New Database

Let's create a new database named zoo that we will let the rhino user access:

spec:
  users:
    - name: rhino
      databases:
        - zoo
Copy

Inspect the hippo-pguser-rhino Secret. You should now see that the dbname and uri fields are now populated!

We can set role privileges by using the standard role attributes that Postgres provides and adding them to the spec.users.options. Let's say we want the rhino to become a superuser (be careful about doling out Postgres superuser privileges!). You can add the following to the spec:

spec:
  users:
    - name: rhino
      databases:
        - zoo
      options: 'SUPERUSER'
Copy

There you have it: we have created a Postgres user named rhino with superuser privileges that has access to the zoo database (though a superuser has access to all databases!).

Adjusting Privileges

Let's say you want to revoke the superuser privilege from rhino. You can do so with the following:

spec:
  users:
    - name: rhino
      databases:
        - zoo
      options: 'NOSUPERUSER'
Copy

If you want to add multiple privileges, you can add each privilege with a space between them in options, e.g.:

spec:
  users:
    - name: rhino
      databases:
        - zoo
      options: 'CREATEDB CREATEROLE'
Copy

Managing the postgres User

By default, Crunchy Postgres for Kubernetes does not give you access to the postgres user. However, you can get access to this account by doing the following:

spec:
  users:
    - name: postgres
Copy

This will create a Secret of the pattern <clusterName>-pguser-postgres that contains the credentials of the postgres account. For our hippo cluster, this would be hippo-pguser-postgres.

Skipping user and database creation

In this tutorial, we've described two different PGO behaviors:

• if you leave out the spec.users section, the default user and database get created;
• if you fill in the spec.users section, those custom users and databases get created, but not the default user and database.

But what if you want to avoid creating the default user and database AND avoid creating custom users and databases? That can be accomplished by setting spec.users to an empty list:

spec:
  users: []
Copy

For example, if we created a PostgresCluster with the above empty list for spec.users, that cluster would have only the roles required by Crunchy Postgres for Kubernetes, and only the databases that a new PostgreSQL cluster would have.

Deleting a User

Crunchy Postgres for Kubernetes does not delete users automatically: after you remove the user from the spec, it will still exist in your cluster. To remove a user and all of its objects, as a superuser you will need to run DROP OWNED in each database the user has objects in, and DROP ROLE in your Postgres cluster.

For example, with the above rhino user, you would run the following:

DROP OWNED BY rhino;
DROP ROLE rhino;
Copy

Note that you may need to run DROP OWNED BY rhino CASCADE; based upon your object ownership structure -- be very careful with this command!

Deleting a Database

Crunchy Postgres for Kubernetes does not delete databases automatically: after you remove all instances of the database from the spec, it will still exist in your cluster. To completely remove the database, you must run the DROP DATABASE command as a Postgres superuser.

For example, to remove the zoo database, you would execute the following:

DROP DATABASE zoo;
Copy

Creating a Declarative Password for a New User

You can declaratively create a password for a new user by creating a Secret. This allows you to easily predefine passwords for your various Postgres users per your specific password requirements/needs. This also means you can also keep passwords for your various users consistent when creating and recreating PostgresClusters.

Let's create a secret by using the following manifest:

apiVersion: v1
kind: Secret
metadata:
  name: hippo-pguser-rhino
  labels:
    postgres-operator.crunchydata.com/cluster: hippo
    postgres-operator.crunchydata.com/pguser: rhino
stringData:
  password: river
Copy

Note that this Secret has a name that matches the pattern of our other user secrets: <clusterName>-pguser-<userName>. Also note that this Secret has two labels:

postgres-operator.crunchydata.com/cluster: hippo
postgres-operator.crunchydata.com/pguser: rhino
Copy

These labels associate this Secret with the hippo cluster and the rhino user.

To apply the secret manifest to your Kubernetes cluster, use the kubectl apply command:

kubectl apply -f my-secret.yaml
Copy

Now let's add the rhino user to your hippo PostgresCluster custom resource exactly as shown in the Creating a New User section above:

spec:
  users:
    - name: rhino
      databases: [grasslands, forest]
Copy

You can now apply the changes and see that the new user is created. This user is created with the same permissions and privileges as if you had created them without declaring a Secret first. For instance, in the above example, the rhino user has permissions to both the grasslands and forest databases. And just as if this Secret were created by the Operator, the Secret you've made will be connected to the PostgresCluster. This means that if you delete the PostgresCluster, the Secret will also be deleted.

Note: If multiple Secrets have the same labels, the Secret with a name in the <clusterName>-pguser-<userName> pattern will be used. Otherwise, the secrets will be ordered based on their creation timestamp and CPK will use the secret with the oldest timestamp.

Automatically Creating Per-User Schemas

You can set Crunchy Postgres for Kubernetes to automatically create schemas for users defined in the spec.users field of the PostgresCluster custom resource. If enabled for a cluster, Crunchy Postgres for Kubernetes will create a schema

• for every user defined in spec.users
• named after the user
• for every database that user is given access to in the spec.users[index].databases field.

Note: Crunchy Postgres for Kubernetes does not delete Postgres objects or revoke permissions. If you remove the annotation that led to schema creation for a user or remove a user from spec.users, Crunchy Postgres for Kubernetes will not remove that user's schema. By removing the annotation, you are telling Crunchy Postgres for Kubernetes not to create any new schemas, but the schemas that were created before will still exist.

Why is this feature here?

Postgres long recommended that permissions for the public schema be revoked to prevent one user from tricking another into using a different Postgres object. (See this CVE for more info.)

As of Postgres 15, this recommendation became the standard behavior. This change results in more secure database behavior, but it also introduced difficulties for people used to the behavior of public in Postgres 14 and below. That is, you could no longer start up a Postgres database and connect as a user to a database and start writing tables -- unless you set up your schemas somehow.

While the spec.databaseInitSQL field could be used to run SQL to create schemas for users, this solution didn't fit all use-cases, particularly those users who might be running a central Postgres database with several different applications attached.

This feature to automatically create schemas helps users start up a database that they can use to point their applications at, as well as presenting a way to keep the database schemas up to date with changing requirements.

Why is the schema named after the user?

Postgres search_path defaults to "$user", public, so by creating a schema with the same name as the user, we do not have to alter the search_path. By keeping changes minimal, we ensure a Postgres experience that is closer to the baseline.

How can I enable this feature for my cluster?

You can enable Crunchy Postgres for Kubernetes' automatic schema creation feature for any cluster by setting the postgres-operator.crunchydata.com/autoCreateUserSchema annotation:

kubectl annotate -n postgres-operator postgrescluster hippo \
  postgres-operator.crunchydata.com/autoCreateUserSchema=true
Copy

Once enabled for this cluster, Crunchy Postgres for Kubernetes will handle the schema creation for any user defined in spec.users as long as

• the user has some databases defined for them in the spec;
• the user is not named after a reserved schema name.

The reserved schema names are the names of schemas required for proper functioning: pgbouncer and monitor. Further, Postgres will reject any attempt to make a user named public.

For instance, if you were to create a PostgresCluster with the following users:

spec:
  users:
    - name: rhino
      databases: [grasslands, forest]
    - name: giraffe
      databases: [grasslands, river]
    - name: pgbouncer
      databases: [grasslands]
    - name: crocodile
Copy

This feature would then create the following:

• rhino schemas in database grasslands and database forest owned by rhino user;
• giraffe schemas in database grasslands and database river owned by giraffe user;
• no schema created for pgbouncer user since that is one of the reserved names;
• no schema created for crocodile user since that user has no databases defined for it.

If a schema named rhino already existed in the database grasslands but was owned by a different role, the Crunchy Postgres for Kubernetes operator would not recreate or change the existing schema.

How can I disable this feature for my cluster?

If you no longer want Crunchy Postgres for Kubernetes to automatically create schemas for users, you can remove the annotation or set it to false:

kubectl annotate -n postgres-operator postgrescluster hippo \
  postgres-operator.crunchydata.com/autoCreateUserSchema-

kubectl annotate -n postgres-operator postgrescluster hippo \
  postgres-operator.crunchydata.com/autoCreateUserSchema=false --overwrite
Copy

By removing the annotation or setting it to false, you will prevent the automatic creation of schemas for different users/databases. As noted above, turning this feature off does not remove any schemas that have already been created.

Custom LDAP Certificate Authorities

When using LDAP authentication with Postgres, you may need to use your own internal certificate authority (CA) when connecting to a TLS-enabled LDAP server. Consider the following configuration:

spec:
  config:
    files:
      - secret:
          name: ldapsecret
          items:
            - key: ca.crt
              path: ldap/ca.crt
  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - hostssl all myuser all ldap ldapserver=myhostname ldapport=636 ldapbasedn="dc=example,dc=org" ldapscheme=ldaps 
Copy

In the first section, spec.config.files will mount the ca.crt file from the Secret ldapsecret to /etc/postgres/ldap/ca.crt. This is the path expected by the LDAPTLS_CACERT environment variable and allows Postgres to utilize the provided CA when connecting to a LDAP server.

The second section, spec.patroni.dynamicConfiguration.postgresql.pg_hba, allows you to configure the appropriate settings for your LDAP server. For more information on the proper settings for your LDAP configuration, please see the pg_hba.conf and auth-ldap documentation.