Databases¶

Database might be considered the most important resource in the operator. It should be used to manage the lifecycle of databases and a users associated with them on the server.

How to create and use a database¶

To create a Database, you first would need to have a running DbInstance. You can read about the DbInstances here.

After you have a working DbInstance you can start managing databases.

Let's start by defining an instance on which the database should be deployed. Let's assume you have an instance called cloudnative-pg

apiVersion: kinda.rocks/v1beta1
kind: Database
metadata:
  name: my-database
spec:
  instance: cloudnative-pg

Then we need to define a name that is going to be used by the operator to create a ConfigMap and a Secret. If a secret with this name already exists, db-operator will try to use it, but let's talk about it later.

apiVersion: kinda.rocks/v1beta1
kind: Database
metadata:
  name: my-database
spec:
    instance: cloudnative-pg
    secretName: my-database-creds

And this is already enough to start using a database, after you apply this manifest, operator will create a database and a user on a server, assign required permissions, and create a Secret and a ConfigMap in Kubernetes. Let's check

$ kubectl get db my-database
NAME          STATUS   PROTECTED   DBINSTANCE         OPERATORVERSION       AGE
my-database   true     false       cloudnative-pg     2.19.0                3m33s

$ kubectl get secret my-database-creds
NAME                TYPE     DATA   AGE
my-database-creds   Opaque   4      4m44s

$ kubectl get cm my-database-creds
NAME                DATA   AGE
my-database-creds   4      4m46s

The Secret should contain credentials to connect to the database generated by operator.

For PostgreSQL:

apiVersion: v1
kind: Secret
metadata:
  labels:
    app.kubernetes.io/managed-by: db-operator
    kinda.rocks/used-by-kind: Database
    kinda.rocks/used-by-name: my-database
  name: my-database-creds
type: Opaque
data:
  POSTGRES_DB: <base64 encoded database name (generated by db operator)>
  POSTGRES_PASSWORD: <base64 encoded password (generated by db operator)>
  POSTGRES_USER: <base64 encoded user name (generated by db operator)>
  CONNECTION_STRING: <base64 encoded database connection string>

For MySQL:

apiVersion: v1
kind: Secret
metadata:
  labels:
    app.kubernetes.io/managed-by: db-operator
    kinda.rocks/used-by-kind: Database
    kinda.rocks/used-by-name: my-database
  name: my-database-creds
type: Opaque
data:
  DB: <base64 encoded database name (generated by db operator)>
  PASSWORD: <base64 encoded password (generated by db operator)>
  USER: <base64 encoded user name (generated by db operator)>
  CONNECTION_STRING: <base64 encoded database connection string>

And the ConfigMap should contain connection information for database server access.

apiVersion: v1
kind: ConfigMap
metadata:
  labels:
    app.kubernetes.io/managed-by: db-operator
    kinda.rocks/used-by-kind: Database
    kinda.rocks/used-by-name: my-database
  name: my-database-creds
data:
  DB_CONN: <database server address>
  DB_PORT: <database server port>
  DB_PUBLIC_IP: <database server public ip>
  ...

By default, ConfigMap and Secret are created without owner references, so they won't be removed once the Database resource is removed. It's a safety measure that should prevent your application from losing a database connection if the operator is accidentally uninstalled.

If you want them to be deleted too, you need to turn on the cleanup feature.

apiVersion: "kinda.rocks/v1beta1"
kind: "Database"
metadata:
  name: "example-db"
spec:
  cleanup: true

If ArgoCD is used to manage Databases and the cleanup is set to true, please make sure that the PrunePropagationPolicy is not set to foreground, because db-operator is using secrets to understand which Database must be removed, and with the foreground policy the secret is removed before the Database, that makes it impossible for the operator to finish the reconciliation.

If this feature is enabled, then Database becomes an owner of Secrets and ConfigMaps, and by removing a database, you'll also remove them.

With these Secret and ConfigMap, we can connect to the database. Let's create a PostgreSQL Pod to test the connection.

apiVersion: v1
kind: Pod
metadata:
  name: my-app
spec:
  containers:
  - name: postgres-create-table
    image: postgres
    command:
      - psql
    args:
      - -c
      - "CREATE TABLE array_int (vector int[][]);"
    env:
      - name: PGPASSWORD
        valueFrom:
          secretKeyRef:
            name: my-database-creds
            key: POSTGRES_PASSWORD
      - name: PGUSER
        valueFrom:
          secretKeyRef:
            name: my-database-creds
            key: POSTGRES_USER
      - name: PGDATABASE
        valueFrom:
          secretKeyRef:
            name: my-database-creds
            key: POSTGRES_DB
      - name: PGHOST
        valueFrom:
          configMapKeyRef:
            name: my-database-creds
            key: DB_CONN
    imagePullPolicy: IfNotPresent
  restartPolicy: Never

Generic additional options¶

Deletion Protection¶

For production databases you might want to set .spec.deletionProtected to true. With this setting, db-operator will not remove the database from the server, if a Kubernetes resource is deleted.

Credentials Templates¶

DB operator is capable of generating custom connections strings using the information it has about a database, here you can read more about templates.

Extra Grants¶

It is possible to apply extra grants to databases, when it's enabled on the db-instance level. To enable it, one must set .spec.allowExtraGrants to true on an instance.

We have two types of access defined in the code: - readOnly - readWrite

It reflects the DbUser types of access.

The idea behind the extra grants is that there might be a database user that must have access to certain databases, that is not necessarily managed by the operator. Let's say the user is called database-admin.

Now for whatever reason, we want to let the admin access a database with readOnly permissions. In the db definition we need to add the following:

kind: Database
spec:
  extraGrants:
    - user: database-admin
      accessType: readOnly

Now the database-admin should have enough permissions for reading data from this database. If access needs to be revoked or changed to readWrite, it should be enough to remove the entry from the spec or modify it.

Extra Labels and Annotations¶

With credentials.metadata.extraLabels and credentials.metadata.extraAnnotations you can add custom metadata to the Secret that stores the generated user credentials. These values are merged with the metadata created by the operator. Keys defined in extraLabels or extraAnnotations overwrite existing values with the same key.

Example:

spec:
  credentials:
    metadata:
      extraLabels:
        environment: development
      extraAnnotations:
        reflector.v1.k8s.emberstack.com/reflection-allowed: "true"
        reflector.v1.k8s.emberstack.com/reflection-auto-enabled: "true"
        reflector.v1.k8s.emberstack.com/reflection-auto-namespaces: target-namespace

This metadata can be used by external controllers that watch annotations or require specific labels to enable Secret synchronization or reflection across namespaces.

Existing Users¶

You can tell the operator to use an existing user instead of creating a new one. It will then only assign required permissions. It can be used, for example, when you want users to be authorized passwordless, via Kubernetes ServiceAccounts. You can use an existing user like this:

kind: Database
metadata: {}
spec:
  existingUser: my-db-user

When using an existing user, DB Operator is not aware of the password anymore, hence the password field in the credentials Secret will be empty. Users must handle the authentication on their own.

It's also important to understand, that when you are switching from/to existing user to/from a generated one, you need to remove the Secret from the cluster, so it's recreated, otherwise it's either going to be empty for a generated user, or filled for an existing one. It will most probably be fixed in future versions.

On Postgres instances, if you have already created object by one user and want to switch to another, you'll also need to re-assign owned objects. You can connect as an admin and execute the following in the target database:

REASSIGN OWNED BY <OLD USERNAME> TO <NEW USERNAME>;

You can find more info here: https://www.postgresql.org/docs/current/sql-reassign-owned.html

Another thing that is worth mentioning, DB Operator is running a health check on each reconciliation, and with generated users it's trying to access a database using these users, but as DB Operator is not aware of a password of an existing users, it's performing a health check as an admin, and hence can't verify whether a user really has the required access.

Additional PostgreSQL options¶

PostgreSQL database can be additionally configured using the .spec.postgres{} section.

It's possible to drop ensure that the public schema is dropped by setting .spec.postgres.dropPublicSchema, and you can also create additional schemas that will be granted to the database user, managed by the operator, to do so, set the .spec.postgres.schemas[]

postgres:
  dropPublicSchema: true # Do not set it, or set to false if you don't want to drop the public schema
  schemas: # The user that's going to be created by db-operator, will be granted all privileges on these schemas
    - schema_1
    - schema_2

If you initialize a database with dropPublicSchema: false and then later change it to true, or add schemas with the schemas field and later try to remove them by updating the manifest, you may be unable to do that. Because db-operator won't use DROP CASCADE for removing schemas, and if there are objects depending on a schema, someone with admin access will have to remove these objects manually.

There is a support for Postgres Database Templates. To create a database from template, you need to set .spec.postgres.template. It's referencing to a database on the Postgres server, but not to the k8s Database resource that is created by operator, so there is no validation on the db-operator side that a template exists.

Reusing an existing secret¶

It's possible to connect DB Operator to an existing database. To do so you'll need to point the operator to an existing secret. It’s important that the secret follows the format expected by the operator.

For postgres:

POSTGRES_DB: <base64 encoded database name (generated by db operator)>
POSTGRES_PASSWORD: <base64 encoded password (generated by db operator)>
POSTGRES_USER: <base64 encoded user name (generated by db operator)>

For mysql:

DB: <base64 encoded database name (generated by db operator)>
PASSWORD: <base64 encoded password (generated by db operator)>
USER: <base64 encoded user name (generated by db operator)>

Then DB Operator will connect to an existing database and set up a user for it.

Experimental features¶

Experimental features are added via annotations, the following features are available for Databases

RDS IAM Impersonate¶

This annotation should be used, when a DbUser is not allowed to log in with password, should be used on the RDS instances, when the SSO is used for authentication.

For more info see this issue: https://github.com/db-operator/db-operator/issues/125

kinda.rocks/rds-iam-impersonate: "true"

Force Database Deletion¶

Delete a postgres database with present connections, might be useful when pgbouncer is used

kinda.rocks/postgres-force-delete-db: "true"

Reconciliation logic¶

By default db-operator checks if a database needs to be reconcilied.

First, use can force a full reconciliation by setting a following annotation:

kinda.rocks/db-force-full-reconcile: "true"

If it's set, operator will remove it and run the full reconciliation.

Then it checks if it's running in a mode, where it should not check changes and each resource should be fully reconcilied on each loop. Actually, by default it doesn't check and always reconciles databases, but to enable this mode, you need to run the operator with the --check-for-changes argument. It might be useful when you have a lot of databases, and updating each one of them might take a very long time.

If you do check for changes, the operator will first check the status of a database and a secret that is used for creating a user. If the status is false, or the secret was changed, it will also trigger reconciliation.