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
spec: users: - name: rhino
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
- The user will not have any connection credentials populated into the
- The user is unprivileged.
Let’s create a new database named
zoo that we will let the
rhino user access:
spec: users: - name: rhino databases: - zoo
hippo-pguser-rhino Secret. You should now see that the
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"
There you have it: we have created a Postgres user named
rhino with superuser privileges that has access to the
rhino database (though a superuser has access to all databases!).
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"
If you want to add multiple privileges, you can add each privilege with a space between them in
spec: users: - name: rhino databases: - zoo options: "CREATEDB CREATEROLE"
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
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
Deleting a User
As mentioned earlier, PGO does not let you delete a user automatically: if 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
in your Postgres cluster.
For example, with the above
rhino user, you would run the following:
DROP OWNED BY rhino; DROP ROLE rhino;
Note that you may need to run
DROP OWNED BY rhino CASCADE; based upon your object ownership structure – be very careful with this command!
Once you have removed the user in the database, you can remove the user from the custom resource.
Deleting a Database
As mentioned earlier, PGO does not let you delete a database automatically: if 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
command as a Postgres superuser.
For example, to remove the
zoo database, you would execute the following:
DROP DATABASE zoo;
Once you have removed the database, you can remove any references to the database from the custom resource.
You now know how to manage users and databases in your cluster and have now a well-rounded set of tools to support your “Day 1” operations. Let’s start looking at some of the “Day 2” work you can do with PGO, such as updating to the next Postgres version, in the next section.