Skip to content

PgInstance

Resource Definition

To manage databases and users on an instance, the PgInstance resource has to be created at first. The PgInstance allows the operator to connect to the postgres instance. The connection details can be provided as secret, config map or directly in the value field. After the PgInstance was created successfully, databases and users can be managed on the referenced instance.

apiVersion: postgres.brose.bike/v1
kind: PgInstance
metadata:
  name: my-instance
spec:
  host:
    secretKeyRef: 
      name: "my-secret"
      key: "hostname"
  port:
    secretKeyRef: 
      name: "my-secret"
      key: "port"
  username:
    secretKeyRef: 
      name: "my-secret"
      key: "user"
  password:
    secretKeyRef: 
      name: "my-secret"
      key: "password"
  database:
    secretKeyRef: 
      name: "my-secret"
      key: "dbname"
  sslMode:
    secretKeyRef: 
      name: "my-secret"
      key: "sslmode"

apiVersion: postgres.brose.bike/v1
kind: PgInstance
metadata:
  name: my-instance
spec:
  host:
    configMapKeyRef:
      name: "my-config-map"
      key: "hostname"
  port:
    configMapKeyRef:
      name: "my-config-map"
      key: "port"
  username:
    configMapKeyRef:
      name: "my-config-map"
      key: "user"
  password:
    configMapKeyRef:
      name: "my-config-map"
      key: "password"
  database:
    configMapKeyRef:
      name: "my-config-map"
      key: "dbname"
  sslMode:
    configMapKeyRef:
      name: "my-config-map"
      key: "sslmode"

apiVersion: postgres.brose.bike/v1
kind: PgInstance
metadata:
  name: my-instance
spec:
  host:
    value: "hostname"
  port:
    value: "5432"
  username:
    value: "user"
  password:
    value: "password"
  database:
    value: "dbname"
  sslMode:
    value: "sslmode"

Attribute Description

The sources of the attribute values can differ. For example host, port, database and sslmode can be stored in the value. The username and password can be stored in a secret. The operator will resolve all attribute values when the resource gets applied. Its better to avoid having defined multiple sources for an attribute, buts its permitted by the operator.

Attribute Decription Optional Default
hostname The hostname of the postgres instance to which the operator should connect ❌ -
port The port of the postgres instance to which the operator should connect ✅ 8432
username The username of the administration user which should be used by the operator ❌ -
password The password of the administration user which should be used by the operator ❌ -
database The maintenance database which should be used to establish the connection to ✅ postgres
sslmode The SSLMode which should be used for the connection to the postgres instance ✅ none

Required Privileges

The user which is provided for the PgInstance to connect to the instance needs to have at least superuser like privileges. The minimal privileges are described below. Usually the superuser permission should be assigned to the user, which should be used by the operator.

Superuser Privileges

The superuser permission can be assigned easily by executing following sql statement, after replacing [USERNAME] with the name of the user which should be used by the operator:

ALTER USER [USERNAME] WITH SUPERUSER;

Minimal Privileges

The following privileges need to be assigned to the user or to one of the assigned roles of the user:

  • Role Privileges:
    • CREATEDB is needed to create new database, which can be managed by the PgDatabase resource
    • CREATEROLE is needed to create new roles, which are created when using the PgUser resource
    • CREATEUSER is needed to create new users, which can be managed by the PgUser resource
  • Database Privileges:
    • CONNECT for the given database is needed to establish the initial connection