pgAdmin with Kerberos and Active Directory

March 21, 2023

pgAdmin supports Kerberos authentication for user logins as well as connecting to databases. Kerberos is a popular authentication method but many people find it difficult to set up especially with Windows Active Directory. In this blog, I will walk through the steps to set up Kerberos with pgAdmin and Active Directory.

For this blog, prerequisites are:

Set up

Follow the steps below to get everything set up.

The following assumptions are made:

  • Domain name is PGADMIN.LOCAL
  • Hostname is mytest.pgadmin.local
  • HTTP SPN is pgadmin
  • Postgres SPN is pgpostgres.
  1. Create a user/UPN  “pgadmin” in AD to map with HTTP service principal 
  2. Create a keytab file which will map HTTP service principal with the above created user.
>ktpass /out pgadmin.keytab /princ HTTP/mytest.pgadmin.local@PGADMIN.LOCAL /mapuser pgadmin /crypto AES256-SHA1 +rndpass /target PGADMIN.LOCAL -ptype KRB5_NT_PRINCIPAL

3. Once you map the HTTP service principal, you can enable delegation for Kerberos. Go to the pgadmin user properties through AD, click on the Delegation tab and choose the correct option as below.

4. Now copy the keytab file to the pgadmin server and set below parameters into config_local.py. Ensure that the operating system user owning the pgAdmin webserver is the owner of this file and should be accessible by that user.

AUTHENTICATION_SOURCES = ['kerberos']
KERBEROS_AUTO_CREATE_USER = True

KRB_APP_HOST_NAME = 'mytest.pgadmin.local'
KRB_KTNAME = '<key_tab_location>/pgadmin.keytab’

NOTE: If KRB_APP_HOST_NAME is not set, pgAdmin will use the value of the DEFAULT_SERVER configuration parameter.

5. Now, create a keytab file for Postgres server and map with the user/UPN pgpostgres.

> ktpass /out postgres.keytab /princ postgres/postgres.pgadmin.local@PGADMIN.LOCAL /mapuser pgpostgres /crypto AES256-SHA1 +rndpass /target PGADMIN.LOCAL -ptype KRB5_NT_PRINCIPAL

6. Copy the postgres keytab file to the postgres server at the preferred location, e.g: /etc/postgresql, and edit the PostgreSQL config file.

krb_server_keyfile = '/etc/postgresql/postgres.keytab'

7. Edit the PostgreSQL pg_hba.conf file

hostgssenc  database  user  address  auth-method [auth-options]

This record matches connection attempts made using TCP/IP, but only when the connection is made with GSSAPI encryption.

8. Restart PostgreSQL server

9. To access the pgAdmin app, login to any machine within the Domain.

10. Configure the browser to use the Spnego/Kerberos. I am using Firefox here, but you can use any browser and keep in mind that the configuration will be different for each browser.

  • Open Firefox and enter about:config in the address bar. 
  • In the Filter field, enter negotiate.
  • Double-click the network.negotiate-auth.trusted-uris preference.
  • In the dialog box, enter the domain against which you want to authenticate,    .pgadmin.local and Clock Ok.
  • Repeat the above procedure for the network.negotiate-auth.delegation-uris entry, using the same domain.

 11. Restart Firefox and browse to mytest.pgadmin.local; you will be logged into the application. All the  Kerberos Authentication processing will be done between the client (browser), pgAdmin server and Active Directory in the background.

12. Once you logged into pgAdmin, you can add the PostgreSQL server to use Kerberos authentication method for the PostgreSQL server.

Once you save the settings, it will be connected and you can start using it.

Troubleshooting

There are 2 errors which people commonly face.
1. Kerberos ticket mismatch. 

  • Make sure you choose the proper encryption while creating the Keytab file
  • There is a need to create multiple keytab files, so make sure you don't get them mixed up.

2. Delegated credentials not supplied

  • As described in the 3rd step, do not forget to enable delegation in AD.
  • Do ensure the proper browser settings are configured as described in step no 11.
     

For Kerberos server settings on Linux, refer https://www.youtube.com/watch?v=EDg6I21kCak 

Share this

More Blogs

PGVector as Embedding Store in PrivateGPT

EDB has a long history of open source contributions, and while we’re best known for our contributions to Postgres, that’s not the only project we contribute to. e.g Barman, CloudNativePG...
June 05, 2024

pgAdmin User Management in Server Mode

pgAdmin can be deployed as a web application by configuring the app to run in server mode. One can check out server deployment on how to run pgAdmin in server...
August 24, 2023

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023