Users¶

Operator provides a feature to manage users and databases in your PostgreSQL cluster. This document describes this feature, defaults and ways to fine tune your users.

Defaults¶

When you create a PostgreSQL cluster with the Operator and do not specify any additional users or databases, the Operator will do the following:

Create a database that matches the name of your PostgreSQL cluster.
Create an unprivileged PostgreSQL user with the name of the cluster. This user has access to the database created in the previous step.
Create a Secret with the login credentials and connection details for the PostgreSQL user which is in relation to the database. This is stored in a Secret named <clusterName>-pguser-<clusterName>. These credentials include:
- user: The name of the user account.
- password: The password for the user account.
- dbname: The name of the database that the user has access to by default.
- host: The name of the host of the database. This references the Service of the primary PostgreSQL instance.
- port: The port that the database is listening on.
- uri: A PostgreSQL connection URI that provides all the information for logging into the PostgreSQL database via pgBouncer
- jdbc-uri: A PostgreSQL JDBC connection URI that provides all the information for logging into the PostgreSQL database via the JDBC driver.

As an example, using our cluster1 PostgreSQL cluster, we would see the following created:

A database named cluster1.
A PostgreSQL user named cluster1.
A Secret named cluster1-pguser-cluster1 that contains the user credentials and connection information.

Custom Users and Databases¶

Users and databases can be customized in spec.users section in the Custom Resource. Section can be changed at the cluster creation time and adjusted over time. Note the following:

If spec.users is set during the cluster creation, the Operator will not create any default users or databases except for PostgreSQL. If you want additional databases, you will need to specify them.
For each user added in spec.users, the Operator will create a Secret of the <clusterName>-pguser-<userName> format (such default Secret naming can be altered for the user with the spec.users.secretName option). This Secret will contain the user credentials.
If no databases are specified, dbname and uri will not be present in the Secret.
If at least one option under the spec.users.databases is specified, the first database in the list will be populated into the connection credentials.
The Operator does not automatically drop users in case of removed Custom Resource options to prevent accidental data loss.
Similarly, to prevent accidental data loss Operator does not automatically drop databases (see how to actually drop a database here).
Role attributes are not automatically dropped if you remove them. You need to set the inverse attribute to actually drop them (e.g. NOSUPERUSER).
The special postgres user can be added as one of the custom users; however, the privileges of this user cannot be adjusted.

Creating a New User¶

Change PerconaPGCluster Custom Resource (e.g. by editing your YAML manifest in the deploy/cr.yaml configuration file):

...
spec:
  users:
    - name: perconapg

Apply the changes (e.g. with the usual `kubctl apply -f deploy/cr.yaml’ command) will create the new user:

The user will only be able to connect to the default postgres database.
The credentials of this user are populated in the <clusterName>-pguser-perconapg secret. There are no connection credentials.
The user is unprivileged.

The following example shows how to create a new pgtest database and let perconapg user access it. The appropriate Custom Resource fragment will look as follows:

...
spec:
  users:
    - name: perconapg
      databases: 
        - pgtest

If you inspect the <clusterName>-pguser-perconapg Secret after applying the changes, you will see dbname and uri options populated there, and the database is created as well.

Adjusting privileges¶

You can set role privileges by using the standard role attributes that PostgreSQL provides and adding them to the spec.users.options subsection in the Custom Resource. The following example will make the perconapg a superuser. You can add the following to the spec in your deploy/cr.yaml:

...
spec:
  users:
    - name: perconapg
      databases:
        - pgtest
      options: "SUPERUSER"

Apply changes with the usual `kubctl apply -f deploy/cr.yaml’ command.

To actually revoke the superuser privilege afterwards, you will need to do and apply the following change:

...
spec:
  users:
    - name: perconapg
      databases:
        - pgtest
      options: "NOSUPERUSER"

If you want to add multiple privileges, you can use a space-separated list as follows:

...
spec:
  users:
    - name: perconapg
      databases:
        - pgtest
      options: "CREATEDB CREATEROLE"

`postgres` User¶

By default, the Operator does not create the postgres user. You can create it by applying the following change to your Custom Resource:

...
spec:
  users:
    - name: postgres

This will create a Secret named <clusterName>-pguser-postgres that contains the credentials of the postgres account.

Deleting users and databases¶

The Operator does not delete users and databases automatically. After you remove the user from the Custom Resource, it will continue to 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 PostgreSQL cluster.

DROP OWNED BY perconapg;
DROP ROLE perconapg;

For databases, you should run the DROP DATABASE command as a superuser:

DROP DATABASE pgtest;

Managing user passwords¶

If you want to rotate user’s password, just remove the old password in the correspondent Secret: the Operator will immediately generate a new password and save it to the appropriate Secret. You can remove the old password with the kubectl patch secret command:

$ kubectl patch secret <clusterName>-pguser-<userName> -p '{"data":{"password":""}}'

Also, you can set a custom password for the user. Do it as follows:

$ kubectl patch secret <clusterName>-pguser-<userName> -p '{"stringData":{"password":"<custom_password>", "verifier":""}}'

Superuser and pgBouncer¶

For security reasons we do not allow superusers to connect to cluster through pgBouncer by default. You can connect through primary service (read more in exposure documentation).

Otherwise you can use the proxy.pgBouncer.exposeSuperusers Custom Resource option to enable superusers connection via pgBouncer.

Get expert help¶

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services. Join K8S Squad to benefit from early access to features and “ask me anything” sessions with the Experts.

Community Forum Get a Percona Expert Join K8S Squad

Last update: 2023-09-20