PgAdmin

PgAdmin serves as the administration and management tool for PostgreSQL databases within the organization. It provides a centralized interface for managing servers, databases, and user access configurations.

For logging into PgAdmin, the Keycloak user credentials must be used.

Initial Configuration

All databases are pre-registered in PgAdmin. To this end, their respective servers are defined within PgAdmin, including the databases.

The users are configured with passwords, which are not automatically saved during the initial setup.

All connections in PgAdmin are pre-configured as _owner_user (see next section).

Retrieving database credentials

PgAdmin comes pre-configured, i.e., all known databases are automatically added during platform deployment. The passwords for accessing these databases must be provided by the platform's central admin. Passwords can be retrieved using a kubectl command.

The following example assumes that you have installed the central database for all components in the namespace ccp-database-stack.

First, list the existing secrets on the namespace with the following command.

kubectl -n <namespace> get secrets

Example:

kubectl -n ccp-database-stack get secrets

NAME                                                                               TYPE     DATA   AGE
frost-owner-user.central-db.credentials.postgresql.acid.zalan.do                   Opaque   2      46h
frost-reader-user.central-db.credentials.postgresql.acid.zalan.do                  Opaque   2      46h
frost-writer-user.central-db.credentials.postgresql.acid.zalan.do                  Opaque   2      46h
geodata-owner-user.central-db.credentials.postgresql.acid.zalan.do                 Opaque   2      46h
geodata-reader-user.central-db.credentials.postgresql.acid.zalan.do                Opaque   2      46h
geodata-writer-user.central-db.credentials.postgresql.acid.zalan.do                Opaque   2      46h
keycloak-owner-user.central-db.credentials.postgresql.acid.zalan.do                Opaque   2      46h
keycloak-reader-user.central-db.credentials.postgresql.acid.zalan.do               Opaque   2      46h
keycloak-writer-user.central-db.credentials.postgresql.acid.zalan.do               Opaque   2      46h
postgres.central-db.credentials.postgresql.acid.zalan.do                           Opaque   2      46h
quantumleap-owner-user.central-db.credentials.postgresql.acid.zalan.do             Opaque   2      46h
quantumleap-reader-user.central-db.credentials.postgresql.acid.zalan.do            Opaque   2      46h
quantumleap-writer-user.central-db.credentials.postgresql.acid.zalan.do            Opaque   2      46h
standby.central-db.credentials.postgresql.acid.zalan.do                            Opaque   2      46h
stellio-search-owner-user.central-db.credentials.postgresql.acid.zalan.do          Opaque   2      46h
stellio-search-reader-user.central-db.credentials.postgresql.acid.zalan.do         Opaque   2      46h
stellio-search-writer-user.central-db.credentials.postgresql.acid.zalan.do         Opaque   2      46h
stellio-subscription-owner-user.central-db.credentials.postgresql.acid.zalan.do    Opaque   2      46h
stellio-subscription-reader-user.central-db.credentials.postgresql.acid.zalan.do   Opaque   2      46h
stellio-subscription-writer-user.central-db.credentials.postgresql.acid.zalan.do   Opaque   2      46h
superset-owner-user.central-db.credentials.postgresql.acid.zalan.do                Opaque   2      46h
superset-reader-user.central-db.credentials.postgresql.acid.zalan.do               Opaque   2      46h
superset-upload-owner-user.central-db.credentials.postgresql.acid.zalan.do         Opaque   2      46h
superset-upload-reader-user.central-db.credentials.postgresql.acid.zalan.do        Opaque   2      46h
superset-upload-writer-user.central-db.credentials.postgresql.acid.zalan.do        Opaque   2      46h
superset-writer-user.central-db.credentials.postgresql.acid.zalan.do               Opaque   2      46h

The postgres operator creates three users per database. The usernames start with the respective database names.

• <database>_reader_user
• <database>_writer_user
• <database>_owner_user

For example, database Keycloak has the following three users assigned, with read, write and owner rights on the full database:

• keycloak-owner-user
• keycloak-reader-user
• keycloak-writer-user

To get a username or password for one secret the following two commands can be used:

Username:

kubectl -n <namespace> get secrets/<secret-name>  --template={{.data.username}} | base64 -d    

Example:

kubectl -n ccp-database-stack get secrets/keycloak-writer-user.central-db.credentials.postgresql.acid.zalan.do  --template={{.data.username}} | base64 -d
keycloak_writer_user

This prints the decoded value of the secret.

Same for the passwords:

kubectl -n <namespace> get secrets/<secret-name>  --template={{.data.password}} | base64 -d    

Example:

kubectl -n ccp-database-stack get secrets/keycloak-writer-user.central-db.credentials.postgresql.acid.zalan.do  --template={{.data.password}} | base64 -d
hrIRc9cLzxzWt3LMthwQdeAFdhutn77kUNiUziw7A57qsg3NvAPjTeqOdx2Ds2bC

Both values can then be used to connect to the respective database.