Olav Grønås Gjerde

I have earlier mentioned how to install pgAdmin on Linux and FreeBSD. Now we will enable OAuth2 authentication for Single Sign on support. Keycloak is a fantastic open source product for managing users. It is open source and there is plenty of great documentation and youtube videos about it.

This is not a tutorial about KeyCloak

Anyway we need to configure Keycloak. I assume you have already created a realm with users. If not read and do that first.

I am not going to cover the GUI, as GUI's change and layouts change. It only adds more confusing as this tutorial ages. We will use the REST Api instead.

First login with your keycloak admin user

bin/kcadm.sh config credentials --server http://mykeycloak:8080 --realm master --user admin --password secret

Generate a client secret

export CLIENT_SECRET=`tr -dc A-Za-z0-9 </dev/urandom | head -c 24 ; echo ''`

Create the client

bin/kcadm.sh create clients -r my-realm-name  -f - << EOF
        {
          "clientId": "pgadmin",
          "baseUrl": "/",
          "surrogateAuthRequired": false,
          "enabled": true,
          "alwaysDisplayInConsole": false,
          "clientAuthenticatorType": "client-secret",
          "secret": "${CLIENT_SECRET}",
          "redirectUris": [
            "https://my_pgadmin_server/oauth2/authorize"
          ],
          "webOrigins": ["+"],
          "bearerOnly": false,
          "consentRequired": false,
          "standardFlowEnabled": true,
          "implicitFlowEnabled": false,
          "directAccessGrantsEnabled": false,
          "serviceAccountsEnabled": true,
          "publicClient": false,
          "frontchannelLogout": false,
          "protocol": "openid-connect",
          "defaultClientScopes": ["web-origins","role_list","roles","profile","email"],
          "optionalClientScopes": ["address","phone","offline_access","microprofile-jwt"],
          "name" : "pgAdmin Client"
        }
      EOF

That's basically all you need todo with Keycloak.

Next step is configuring pgAdmin, edit following file /var/lib/pgadmin/.local/lib/python3.9/site-packages/pgadmin4/config_local.py Note: your install location of pgadmin may vary

SERVER_MODE = True
MASTER_PASSWORD_REQUIRED = True
AUTHENTICATION_SOURCES = ['oauth2', 'internal']
OAUTH2_AUTO_CREATE_USER = True
OAUTH2_CONFIG = [{
    'OAUTH2_NAME': 'MyKeyCloak',
    'OAUTH2_DISPLAY_NAME': 'MyKeyCloak',
    'OAUTH2_CLIENT_ID': 'pgadmin',
    'OAUTH2_CLIENT_SECRET': 'your-client-secret-here',
    'OAUTH2_TOKEN_URL': 'https://mykeycloak/realms/datahub/protocol/openid-connect/token',
    'OAUTH2_AUTHORIZATION_URL': 'https://mykeycloak/realms/datahub/protocol/openid-connect/auth',
    'OAUTH2_API_BASE_URL': 'https://mykeycloak/realms/datahub/',
    'OAUTH2_USERINFO_ENDPOINT': 'https://mykeycloak/realms/datahub/protocol/openid-connect/userinfo',
    'OAUTH2_SCOPE': 'openid email profile',
    'OAUTH2_BUTTON_COLOR': '#51c65b;',
    'OAUTH2_SERVER_METADATA_URL': 'https://mykeycloak/realms/datahub/.well-known/openid-configuration'
}]

Restart pgAdmin and test your login.