Integrating pgAdmin with Authelia

This is also another post outside my planned posts, but I wanted to drop this as a quickie. I started deploying SSO, via Authelia, through the lab and ended up having a few PEBKAC errors with pgAdmin and figured I’d post a simple ‘guide’ to hopefully keep others from experiencing them. My pain, your gain 😅. Also, big shout out to my brother for some SSO insight since he deals with configuring it constantly at his job! 💪

Environment

For my lab I have Authelia and pgAdmin deployed via Docker Compose. Both are attached to a Swarm Overlay network for them to communicate with Caddy. For the sake of keeping this post quick I am going to skip over all the installation for pgAdmin, Authelia, and Caddy. My Caddy guide (coming soon!) will include setup instructions for Caddy and Authelia behind Cloudflare.

I will also be assuming that you already have a user/users configured in Authelia already. Either via an integration or the file provider.

Configuring Authelia for OpenID Connect (OIDC)

In order to set up OpenID Connect (OIDC) in Authelia we need to create a few secrets and a key/cert pair. To do this run the following commands while substituting yourdomain.com with the root domain you are using for Authelia as necessary. Copy the outputs and save them for later.

docker run --rm authelia/authelia sh -c "authelia crypto certificate rsa generate --common-name yourdomain.com && cat private.pem && cat public.crt"

You will get an output containing both the private key and the public certificate. See the example below.

OIDC Key + Cert Example output for private.key and public.crt

Next generate the hmac secret.

docker run --rm authelia/authelia:latest authelia crypto rand --length 128 --charset alphanumeric

Example of a hmac secret

Finally, generate the client secret for pgAdmin. I left the charset as alphanumeric as some applications do not like special characters in client secrets.

docker run --rm authelia/authelia:latest authelia crypto rand --length 128 --charset alphanumeric

Example of a generated client secret

Now we need to edit our Authelia configuration.yml to enable OIDC and create the pgAdmin client. Edit the file and add the following. Make sure to substitute yourdomain.com with yours, and replace client_secret with the one generated above. Leave the hmac, issuer cert chain and private key blank. We will be using secret file loading to populate those. You can change the client id, just note it as it will be needed later.

The OIDC redirect URI for pgAdmin is: {DOMAIN}/oauth2/authorize. In my case I have pgAdmin running as a subdirectory. Make sure to change this to match your environment.

1
identity_providers:
2
  oidc:
3
    hmac_secret:
4
    issuer_certificate_chain:
5
    issuer_private_key:
6
    access_token_lifespan: 1h
7
    authorize_code_lifespan: 1m
8
    id_token_lifespan: 1h
9
    refresh_token_lifespan: 90m
10
    enable_client_debug_messages: true
11
    cors:
12
      allowed_origins:
13
        - https://yourdomain.com
14

15
    clients:
16
      - id: pgadmin
17
        description: pgAdmin
18
        secret: client_secret
19
        ## The line above will look like the below one when populated.
20
        # secret: 5ePwB5bZ7WMEHyiKAmSkws6UuluX7W5kueXi7oZkGJRlOis6Zj2ueJPIs3zGWIPBk1vLEcP3I1eSzf4zGyTU1n391whP9yHhH0I3hbXpqK3czWeIjaCBplqQsa7hTqkt
21
        authorization_policy: two_factor
22
        consent_mode: auto
23
        pre_configured_consent_duration: 1w
24
        scopes:
25
          - openid
26
          - groups
27
          - email
28
          - profile
29
        redirect_uris:
30
          - https://yourdomain.com/pgadmin/oauth2/authorize
31
        grant_types:
32
          - refresh_token
33
          - authorization_code
34
        response_modes:
35
          - query

Save and close the file.

Adding file secrets to Authelia

Create a new directory in Authelia’s called .secrets. Then create the following files with the data generated above into the new directory.

mkdir ~/authelia/.secrets

Create the following files with their respective contents:

aI84lHge7keZhU7sh7gLZgCUcAIpr9qQruya5O8mkcd5UlZ9tubi4fQs8Ps1peQNbahHaTCQ0OOEftEB6q5E2Hk8WLozX6xeBjhAtqihWZcYnwSBuUeAs2jwCus4uc2S

Now edit your Authelia .env file and add the following OIDC variables.

# JWT Secret
AUTHELIA_JWT_SECRET_FILE=/secrets/jwt_secret

# OIDC Secrets
AUTHELIA_IDENTITY_PROVIDERS_OIDC_HMAC_SECRET_FILE=/secrets/oidc_hmac_secret
AUTHELIA_IDENTITY_PROVIDERS_OIDC_ISSUER_CERTIFICATE_CHAIN_FILE=/secrets/oidc_issuer_cert
AUTHELIA_IDENTITY_PROVIDERS_OIDC_ISSUER_PRIVATE_KEY_FILE=/secrets/oidc_private_key

# Session Secret
AUTHELIA_SESSION_SECRET_FILE=/secrets/session_secret

# DB Secret
AUTHELIA_STORAGE_POSTGRES_PASSWORD_FILE=/secrets/storage_postgres_password
AUTHELIA_STORAGE_ENCRYPTION_KEY_FILE=/secrets/storage_encryption_key

Save and re-deploy Authelia to update the configuration.

Configuring pgAdmin for OAuth

Edit your pgAdmin .env file with the following environment variables. The pgAdmin config is annoying in that you need to specify the OAUTH_CONFIG settings inside an object. So it seems complicated but it is not. Each pair is defined as 'key_name': 'value' and is separated by ,. Go through and edit the object array to match your client config and root domain. All the API endpoints are correct for Authelia so just change the root domain! For more info on the config options for pgAdmin see the docs’ page here.

# First time setup / default user
PGADMIN_DEFAULT_EMAIL=temp@temp.com
PGADMIN_DEFAULT_PASSWORD=temp1234

# Subdir
SCRIPT_NAME=/pgadmin

# Authentication (oAuth)
PGADMIN_CONFIG_AUTHENTICATION_SOURCES=['oauth2', 'internal']
PGADMIN_CONFIG_OAUTH2_CONFIG="[{'OAUTH2_NAME': 'Authelia','OAUTH2_DISPLAY_NAME': 'Authelia','OAUTH2_CLIENT_ID': 'pgadmin','OAUTH2_CLIENT_SECRET': '5ePwB5bZ7WMEHyiKAmSkws6UuluX7W5kueXi7oZkGJRlOis6Zj2ueJPIs3zGWIPBk1vLEcP3I1eSzf4zGyTU1n391whP9yHhH0I3hbXpqK3czWeIjaCBplqQsa7hTqkt','OAUTH2_API_BASE_URL': 'https://auth.yourdoamin.com/','OAUTH2_TOKEN_URL': 'https://auth.yourdomain.com/api/oidc/token','OAUTH2_AUTHORIZATION_URL': 'https:/auth.yourdomain.com/api/oidc/authorization','OAUTH2_USERINFO_ENDPOINT': 'https://auth.yourdomain.com/api/oidc/userinfo','OAUTH2_SERVER_METADATA_URL': 'https://auth.yourdomain.com/.well-known/oauth-authorization-server','PGADMIN_CONFIG_OAUTH2_ICON': 'fa-sign-hanging','OAUTH2_SCOPE': 'openid email profile','OAUTH2_BUTTON_COLOR': '#1c3054'}]"

# Auto create OAUTH users in pgAdmin
# Set this to False if you only want to register the first user that signs on only after signing in with said user!
PGADMIN_CONFIG_OAUTH2_AUTO_CREATE_USER=True

Save and close the file. Then redeploy pgAdmin to use the new configuration. Once pgAdmin redeploys open your browser and navigate to pgAdmin. You should see a new option listed on the login page for logging in with Authelia.

Example of my sign-in page for pgAdmin while OAuth is enabled

Click “Log in with Authelia”. If everything is set up correctly you should be redirected to your Authelia sign in page. Once you sign in you should be redirected back to pgAdmin’s dashboard. You will be prompted to create a master password. This password is used for locking/unlocking saved server passwords when the authentication source is either OAuth2, Kerberos, or Web server. You can disable this but it is not recommended. See here for more details.

pgAdmin oAuth sign in flow Example of the pgAdmin sign in flow with Authelia 2FA and master password prompt

Conclusion

Hopefully this post keeps someone from hitting a wall with pgAdmin OAuth config like I did initially. I probably won’t be uploading any files to the blog-files repository since this isn’t really a proper “guide” post that I normally make. Though, like always, if you run into issues feel free to reach out via the comments, or email!

Oh, and you bet I will be looking for a way to have notifications sent to ntfy on successful authentications via Authelia.. haha…

Resources

pgAdmin docs - https://www.pgadmin.org/docs/pgadmin4/latest/index.html
Authelia OIDC docs - https://www.authelia.com/configuration/identity-providers/open-id-connect/
OpenID Connect docs - https://openid.net/developers/how-connect-works/

Integrating pgAdmin with Authelia

Environment

Configuring Authelia for OpenID Connect (OIDC)

Adding file secrets to Authelia

Configuring pgAdmin for OAuth

Conclusion

Resources

Comments

Related Posts

How I Manage My Media Server From My Phone with nzb360

Rebuilding My Home Lab

Upgrading Forgejo with S3 Object Storage and Actions!