keycloak.md

Preparing the Database for Keycloak

Step 1: Connect to PostgreSQL as a Superuser

1. Connect to PostgreSQL as a Superuser

   First, connect to your PostgreSQL instance as a superuser (usually postgres):

   export POSTGRES_HOST=postgres.postgres
   export POSTGRES_USER=postgres
   export POSTGRES_DB=postgres
   export POSTGRES_PASSWORD=$(kubectl get secret --namespace postgres postgres-secret -o jsonpath="{.data.adminPassword}" | base64 --decode)

   kubectl run postgres-client --rm --tty -i \
        --image bitnami/postgresql \
        --env="PGPASSWORD=$POSTGRES_PASSWORD" \
        -- psql --host $POSTGRES_HOST -U $POSTGRES_USER -d $POSTGRES_DB

Step 2: Create the Database and User

1. Create the Role and User

   Create a role (keycloak_dba) and a user with the necessary permissions:

   -- Create the role keycloak_dba
   CREATE ROLE keycloak_dba;
   
   -- Create the user keycloak with the keycloak_dba role and specified password
   CREATE USER keycloak WITH ENCRYPTED PASSWORD '9uwiPiMMAEQ8';
   ALTER USER keycloak WITH LOGIN;
   GRANT keycloak_dba TO keycloak;

2. Create the Database and Grant Permissions

   Create the keycloak database and grant the necessary permissions to the keycloak_dba role:

   -- Create the keycloak database
   CREATE DATABASE keycloak;
   
   -- Connect to the keycloak database
   \c keycloak
   
   -- Grant permissions to keycloak_dba on the keycloak database
   GRANT CONNECT ON DATABASE keycloak TO keycloak_dba;
   GRANT ALL PRIVILEGES ON DATABASE keycloak TO keycloak_dba;
   
   -- Grant all privileges on the public schema to the keycloak_dba role
   GRANT ALL ON SCHEMA public TO keycloak_dba WITH GRANT OPTION;
   

Step 3: Verify

1. To verify that the permissions are set correctly, you can list the permissions for the keycloak_dba role and keycloak user on the keycloak database:

       -- List the privileges on the keycloak database
       \l+ keycloak
       
       -- List the privileges on the public schema
       \dn+

2. To verify that the keycloak user can connect to the keycloak database, you can connect to the database as the keycloak user:

   export POSTGRES_USER=keycloak
   export POSTGRES_DB=keycloak
   export POSTGRES_PASSWORD=9uwiPiMMAEQ8

   kubectl run postgres-client --rm --tty -i \
       --image bitnami/postgresql \
       --env="PGPASSWORD=$POSTGRES_PASSWORD" \
       -- psql --host $POSTGRES_HOST -U $POSTGRES_USER -d $POSTGRES_DB