Manage Users and Databases

Creating User and Databases

It’s possible to create additional databases and users right in the specification for the PostgreSQL instance.

If only a username is provided, a database with the same name will be provisioned automatically. If a user needs access to another user’s database, it can be specified by the database field.

Example PostgreSQL instance with users
apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  parameters:
    service:
      access:
        - user: app1 (1)
        - user: app2
          database: app1 (2)
        - user: app3
          database: app1
          privileges:
            - SELECT (3)
        - user: app4
          writeConnectionSecretToRef: (4)
            name: my-secret
            namespace: app4
  writeConnectionSecretToRef:
    name: postgres-creds
1 Create a user and a database called app1, the user gets all privileges for the database
2 Create a user app2 and giving it all privileges to database app1
3 Create a user app3 and giving it select privileges on database app1
4 Write the connection secret to another namespace. If you want to connect from another namespace, please make sure that you configure the allowed namespaces accordingly.

Please see the official PostgreSQL docs for all available privileges. Only Grants applicable to databases are supported.

Deleting User and Databases

To stop managing a user in the PostgreSQL instance, you need to remove it from the access array in the instance.

Manual cleanup required

Removing users from the access array does NOT automatically delete the user, database, or grants from the PostgreSQL instance. These resources will remain in the database and must be manually deleted if no longer needed.
Example Removing users from management
apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  parameters:
    service:
      access:
        - user: app1 (1)
        - user: app2
          database: app1 (2)
        - user: app3 (3)
  writeConnectionSecretToRef:
    name: postgres-creds
1 Removing this entry will stop managing the app1 user, but the user and database will remain in PostgreSQL
2 Removing this entry will stop managing the user app2, but the user and its grants will remain in PostgreSQL
3 Removing this entry will stop managing the user app3, but the user and database will remain in PostgreSQL

To manually clean up users, databases, and grants that are no longer managed:

  1. Connect to your PostgreSQL instance using the connection details

  2. Revoke grants: REVOKE ALL PRIVILEGES ON DATABASE databasename FROM username;

  3. Drop the user: DROP USER username;

  4. Drop the database (if no longer needed): DROP DATABASE databasename;

Manual deletion is irreversible

Manual deletion of users and databases is permanent and cannot be undone. Ensure you have backups and that the resources are truly no longer needed before deleting them. Renaming is also not possible. If a user is renamed, it will be treated as a new user, and the old user will remain in the database.

Create a database with a specific locale

The default locale when databases are created is C.UTF-8. If an application needs a specific locale that is different, then you can do the following:

Create a user and database in the instance
apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  parameters:
    service:
      access:
        - user: myapp (1)
  writeConnectionSecretToRef:
    name: postgres-creds
1 This will create a user and a database myapp

After that, we create a pod that will re-create the database with the desired settings.

This will drop the database and recreate it. Make sure you have a backup of the data before running this pod.
Run a pod with SQL commands to create custom database
---
apiVersion: v1
kind: Pod
metadata:
  name: postgres-client
  namespace: prod-app
spec:
  containers:
  - name: postgres
    image: postgres:16
    resources:
      limits:
        memory: "128Mi"
        cpu: "500m"
    command:
      - /bin/sh
      - -c
    args:
        - PGPASSWORD=$POSTGRESQL_PASSWORD psql "sslmode=verify-ca sslrootcert=/etc/secret-volume/ca.crt host=$POSTGRESQL_HOST port=$POSTGRESQL_PORT dbname=template1" -U $POSTGRESQL_USER -c "drop database if exists $DBNAME with (force);" -c "create database $DBNAME LC_COLLATE='C' LC_CTYPE='C' ENCODING='UTF-8' TEMPLATE='template0';"
    envFrom:
      - secretRef:
          name: postgres-creds (1)
    env:
      - name: DBNAME
        value: myapp (2)
    volumeMounts:
    - name: secret-volume
      readOnly: true
      mountPath: "/etc/secret-volume"
  volumes:
  - name: secret-volume
    secret:
      defaultMode: 0600
      secretName: postgres-creds (1)
  restartPolicy: OnFailure
1 The pod will use the secret to connect to the instance. Has to match the writeConnectionSecretToRef field of the instance.
2 Change the name to the database name you want

You can use this postgres-client pod as a template for any SQL commands you want to run against your AppCat VSHNPostgreSQL instance.