Integrating pgAdmin with Authelia

Posted Oct 2, 2023 Updated Dec 7, 2023

By Alexander Henderson 7 min read

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.

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.

  


identity_providers:
  oidc:
    hmac_secret:
    issuer_certificate_chain:
    issuer_private_key:
    access_token_lifespan: 1h
    authorize_code_lifespan: 1m
    id_token_lifespan: 1h
    refresh_token_lifespan: 90m
    enable_client_debug_messages: true
    cors:
      allowed_origins:
        - https://yourdomain.com

    clients:
      - id: pgadmin
        description: pgAdmin
        secret: client_secret
        ## The line above will look like the below one when populated.
        # secret: 5ePwB5bZ7WMEHyiKAmSkws6UuluX7W5kueXi7oZkGJRlOis6Zj2ueJPIs3zGWIPBk1vLEcP3I1eSzf4zGyTU1n391whP9yHhH0I3hbXpqK3czWeIjaCBplqQsa7hTqkt
        authorization_policy: two_factor
        consent_mode: auto
        pre_configured_consent_duration: 1w
        scopes:
          - openid
          - groups
          - email
          - profile
        redirect_uris:
          - https://yourdomain.com/pgadmin/oauth2/authorize
        grant_types:
          - refresh_token
          - authorization_code
        response_modes:
          - query

I have two_factor enabled for pgAdmin, so it requires my Yubikey. However, if you do not want to use 2FA you can change the authorization_policy to one_factor.

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

aI84lHge7keZhU7sh7gLZgCUcAIpr9qQruya5O8mkcd5UlZ9tubi4fQs8Ps1peQNbahHaTCQ0OOEftEB6q5E2Hk8WLozX6xeBjhAtqihWZcYnwSBuUeAs2jwCus4uc2S

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

I know I shouldn’t have to say it but

DO NOT USE THE ABOVE VALUES. THESE ARE EXAMPLES.

# 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.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# First time setup / default user
[email protected]
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

If you only want SSO enabled for pgAdmin you can remove 'internal' from PGADMIN_CONFIG_AUTHENTICATION_SOURCES. I’d recommend doing this only after verifying successful authentication via Authelia.

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

If you have the pgAdmin environment variable PGADMIN_CONFIG_OAUTH2_AUTO_CREATE_USER set to True then you can click the “Login with Authelia” button. Otherwise, you will need to first log in with your internal user account and set up a new user for the authentication source “oauth”. The email must match the one for the user you are signing in with from Authelia!

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.

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/

homelab

This post is licensed under CC BY 4.0 by the author.