User / Database Management

PGO 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 PGO, 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, PGO 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.

Deleting a User

PGO 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

PGO 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, savanna]
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 savanna 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.