pgAdmin with Kerberos and Active Directory

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:

• pgAdmin in web mode (multi user mode) on any supported OS.
  Download Ref: https://www.pgadmin.org/download/
  If pgAdmin is running behind Apache, refer https://www.pgadmin.org/docs/pgadmin4/6.21/kerberos.html for the Kerberos Apache settings.
• PostgreSQL server built with GSSAPI support
• Windows server with Active Directory Domain service

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