User Management

PGO manages PostgreSQL users that you define in PostgresCluster.spec.users. There, you can list their role attributes and which databases they can access.

Below is some information on how the user and database management systems work. To try out some examples, please see the user and database management section of the tutorial.

Understanding Default User Management

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

• Create a database that matches the name of the Postgres cluster.
• Create an unprivileged Postgres 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 Postgres user 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 Postgres instance.
  • port: The port that the database is listening on.
  • uri: A PostgreSQL connection URI that provides all the information for logging into the Postgres database.

You can see this default behavior in the connect to a cluster portion of the tutorial.

As an example, using our hippo Postgres cluster, we would see the following created:

• A database named hippo.
• A Postgres user named hippo.
• A Secret named hippo-pguser-hippo that contains the user credentials and connection information.

While the above defaults may work for your application, there are certain cases where you may need to customize your user and databases:

• You may require access to the postgres superuser.
• You may need to define privileges for your users.
• You may need multiple databases in your cluster, e.g. in a multi-tenant application.
• Certain users may only be able to access certain databases.

Custom Users and Databases

Users and databases can be customized in the spec.users section of the custom resource. These can be adding during cluster creation and adjusted over time, but it’s important to note the following:

• If spec.users is set during cluster creation, PGO will not create any default users or databases except for postgres. If you want additional databases, you will need to specify them.
• For any users added in spec.users, PGO will created a Secret of the format <clusterName>-pguser-<userName>. This will contain the user credentials.
  • If no databases are specified, dbname and uri will not be present in the Secret.
  • If at least one spec.users.databases is specified, the first database in the list will be populated into the connection credentials.
• To prevent accidental data loss, PGO will not automatically drop users. We will see how to drop a user below.
• Similarly, to prevent accidental data loss PGO will not automatically drop databases. We will see how to drop a database below.
• Role attributes are not automatically dropped if you remove them. You will have to set the inverse attribute to drop them (e.g. NOSUPERUSER).
• The special postgres user can be added as one of the custom users; however, the privileges of the users cannot be adjusted.

For specific examples for how to manage users, please see the user and database management section of the tutorial.