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 remove a user and its database, you need to remove it from the access array in the instance.

A user or database will be removed from the instance, if there are no more references in the access array.

Example Deleting users and databases

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 would remove the `app1` user and the grant giving it access to the app1 database. It will not remove the database, as it’s still referenced by user app2.
2	Removing this entry would remove the user `app2` and the grants giving access to database app1. It will not remove the database app1, as it’s still referenced by user `app1`
3	Removing this entry will remove the user `app3` and its database.

Non-reversible operation

If you remove a user completely from the array, the user and the database will be removed from the instance! Also, renaming is not possible. If you rename a user it will effectively be deleted!

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.