Creating a custom EDB Postgres user name and password

You can create custom credentials for application users and superusers to connect to your EDB Postgres database instances on IBM Cloud Pak for Data.

About this task

By default, EDB Postgres credentials are automatically generated. Any custom user names and passwords you create are stored as a secret for each database instance and user type.

Procedure

  1. Create a secret to store your custom credentials. The commands that you use depend on whether you are creating custom credentials for application users or superusers or both.
    Application users
    Option 1:
    1. Set the EDB_USERNAME and EDB_PASSWORD environment variable to your base64-encoded username and password.
      export EDB_USERNAME=$(echo -n "<username>" | base64)
export EDB_PASSWORD=$(echo -n "<password>" | base64)
    2. Create a secret to hold your credentials by running the following command. Enter your choice of password and username in the respective fields.
      cat <<EOF | oc apply -n ${PROJECT_CPD_INST_OPERANDS} -f -
apiVersion: v1
data:
  username: ${EDB_USERNAME}
  password: ${EDB_PASSWORD}
kind: Secret
metadata:
  name: edb-advanced-app-user
type: kubernetes.io/basic-auth
EOF
    3. Check your new secret by running the following command:
      oc get secret edb-advanced-app-user -o jsonpath="{.data.username}" | base64 --decode
oc get secret edb-advanced-app-user -o jsonpath="{.data.password}" | base64 --decode
    Option 2:
    1. Create a secret to hold your credentials by running the following command. Enter your choice of password and username in the respective fields.
      oc create secret generic edb-advanced-app-user --from-literal='username=<username>' --from-literal='password=<password>' -n ${PROJECT_CPD_INST_OPERANDS}
    2. Check your new secret by running the following command:
      oc get secret edb-advanced-app-user -o jsonpath="{.data.username}" | base64 --decode
oc get secret edb-advanced-app-user -o jsonpath="{.data.password}" | base64 --decode
    Superusers
    Option 1:
    1. Set the EDB_USERNAME and EDB_PASSWORD environment variable to your base64-encoded username.
      export EDB_USERNAME=$(echo -n "<username>" | base64)
export EDB_PASSWORD=$(echo -n "<password>" | base64)
    2. Create a secret to hold your credentials by running the following command. Enter your choice of password and username in the respective fields.
      cat <<EOF | oc apply -n ${PROJECT_CPD_INST_OPERANDS} -f -
apiVersion: v1
data:
  username: ${EDB_USERNAME}
  password: ${EDB_PASSWORD}
kind: Secret
metadata:
  name: edb-advanced-app-superuser
type: kubernetes.io/basic-auth
EOF
    3. Check your new secret by running the following command:
      oc get secret edb-advanced-app-superuser -o jsonpath="{.data.username}" | base64 --decode
oc get secret edb-advanced-app-superuser -o jsonpath="{.data.password}" | base64 --decode
    Option 2:
    1. Create a secret to hold your credentials by running the following command. Enter your choice of password and username in the respective fields.
      oc create secret generic edb-advanced-app-superuser --from-literal='username=<username>' --from-literal='password=<password>' -n ${PROJECT_CPD_INST_OPERANDS}
    2. Check your new secret by running the following command:
      oc get secret edb-advanced-app-superuser -o jsonpath="{.data.username}" | base64 --decode
oc get secret edb-advanced-app-superuser -o jsonpath="{.data.password}" | base64 --decode
  2. Apply the secret to your EDB Postgres database instance by using the web console or custom resource.
    Using the web console:
    1. In the web console, click Add custom credentials (Optional).
    2. Enter your custom secret. For example, edb-advanced-superuser for an application superuser.
    Using a custom resource:
    1. Set the EDB_INSTANCE_NAME environment variable to the name of the database instance for which you want to set the custom credentials: 
      export EDB_INSTANCE_NAME=<name>
    2. Set the EDB_INSTANCE_TYPE environment variable to the EDB Postgres database type. Valid values are Enterprise or Standard. 
      export EDB_INSTANCE_TYPE=<EDBInstanceType>
    3. To apply custom credentials for application users or superusers or both for your database instance, modify your custom resource YAML file and apply it to your project. 
      apiVersion: edb.cpd.ibm.com/v1
kind: CPDEdbInstance
metadata:
  name: ${EDB_INSTANCE_NAME}
spec:
  type: ${EDB_INSTANCE_TYPE}
  imageRepository: "cp.stg.icr.io/cp/cpd"
  # Travis pipeline will insert here
  members: 3
  version: "12.9"
  storageClass: nfs-client
  storageSize: 100Gi
  resources:
    requests:
      cpu: 1
      memory: 4Gi
    limits:
      cpu: 1
      memory: 4Gi
  # # Use custom secret 
  userSecret: edb-advanced-app-user

  # # Use custom superuserSecret 
  superuserSecret: edb-advanced-superuser
  3. Decode the secret with the following command: 
    echo '<password>' | base64 -d

    The output you see should be similar to the following example:

    uN3i1Hh0bYbCc4TyVAacBhmSzgqtzqxmOVjAn0QIChsJrSmu9PY2fw2q8EKKLpZ9

    You have now decoded the username and password.

  4. Verify that the new credentials have been applied with the following command:
    Application users:
    oc exec -it ${EDB_INSTANCE_NAME}-user-edb-db-1 -n ${PROJECT_CPD_INST_OPERANDS} bash

    The output you see should be similar to the following example:

    kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl kubectl exec [POD] -- [COMMAND] instead. bash-4.4$ psql -p 5432 -h 127.0.0.1 -U app Password for user app: password psql (12.9) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. app=>
    Superusers:
    oc exec -it ${EDB_INSTANCE_NAME}-superuser-edb-db-1 -n ${PROJECT_CPD_INST_OPERANDS} bash

    The output you see should be similar to the following example:

    kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl kubectl exec [POD] -- [COMMAND] instead. bash-4.4$ psql -p 5432 -h 127.0.0.1 -U postgres Password for user postgres: uN3i1Hh0bYbCc4TyVAacBhmSzgqtzqxmOVjAn0QIChsJrSmu9PY2fw2q8EKKLpZ9 psql (12.9) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=#

Results

Users can now use the custom username and password that you set to access the EDB Postgres database instance.