0

TEST 1 - PostgreSQL with Active Directory authentication

  1. I have installed PotgreSQL Server v15.3 on Red Hat 8.8 and integrate PostgreSQL authentication using Active Directory.

  2. In pg_hba.conf I have set only one settings (all other are commented out): hostgssenc all all all gss

  3. On separate Red Hat 8.8 machine I have installed PostgreSQL Client v15.3 and I have successfully connected to PostgreSQL server using Kerberos authentication and Active Directory using psql tool as user01.

  4. On separate Windows 2022 Server I have set static IP and DNS address and added computer to Active Directory domain and I can successfully login to Windows with user user01. On this PC I have installed PostgreSQL Client v15.3 and I have tried to connect to PostgreSQL server using psql tool with command: psql -h server.adexample.com -p 5432 -d postgres but error is returned:

    psql: error: connection to server at "server.adexample.com" (192.168.100.36), port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.100.41", user "user01", database "postgres", no encryption

    This is the PROBLEM I am trying to solve.

  5. On Windows where PostgreSQL client v15.3 is installed I monitored network traffic with Wireshark and I see psql client first sends "SSL/TLS" request and server refuses, then sends "clear text" request and server returns error (from step 4). From network packets it looks like psql client does not even tries to connect to server using Kerberos.

TEST 2 - IBM Db2 with Active Directory authentication

  1. Separate Red Hat 8.8 I have installed IBM Db2 database server v11.5 and configure it to authenticate with Active Directory using Kerberos.
  2. On separate Red Hat 8.8 I have installed IBM Db2 database client v11.5 and I can successfully connect to Db2 server using Kerberos authentication and Active Directory.
  3. On the same Windows 2022 Server (that I have already installed PostgreSQL client and Kerberos authentication fails) I have installed IBM Db2 database client v11.5 and I can successfully connect to IBM Db2 database server using Kerberos Active Directory with Windows user 'user01'. I conclude Windows Kerberos is correctly working.

Questions:

  1. What should I do on PostgreSQL Client on Windows to successfully connect to PostgreSQL Server on Red Hat using Active Directory?
  2. Is there some setting I additionally have to add to pg_hba.conf on PostgreSQL Server site?
  3. Does PostgreSQL Client support native Windows Kerberos implementation or do I need to additionally install some software? If yes, which one?
Improve this question
5
  • 11.5? Why are you using such an out-of-date minor release, and of a major release which is already almost EOL as well?
    – jjanes
    Jun 14 at 15:12
  • How did you install? Maybe your installed client software was not compiled with support for GSS.
    – jjanes
    Jun 14 at 15:14
  • @jjanes, I have mistyped the version. IBM DB2 = v11.5 both on server and client, PostgreSQL = v15.3 both on server and client. Now I edited the original post, to be more clear. Thanks for noticing this my mistake.
    – folow
    Jun 15 at 7:06
  • To download PostgreSQL client on Windows, I have checked the original web page: postgresql.org/download/windows where it points to enterprisedb.com/downloads/postgres-postgresql-downloads to download. Direct link I have downloaded from: sbp.enterprisedb.com/getfile.jsp?fileid=1258514 Is there some way to check if PostgreSQL was compiled with GSS?
    – folow
    Jun 15 at 7:08
  • On PostgreSQL client v15.3 on Windows I have executed command: set PGGSSENCMODE=require and then psql -h server.adexample.com -p 5432 -d postgres and I get error: "gssencmode value "require" invalid when GSSAPI support is not compiled in". Where to download PostgreSQL v15.3 for Windows client with GSSAPI support?
    – folow
    Jun 15 at 7:23

1 Answer 1

Reset to default
0

I think the problem here is that the Windows compilation will use Kerberos for authentication, but not for encryption. Your indicated pg_hba line demands use of Kerberos for encryption, so that line gets bypassed. But there are no other pg_hba which can match, so the connection attempt fails without really making an attempt. When I use the EDB installer for Windows and with your config, this is what I see as well.

If you add a host line (not a hostgssenc line) which calls for gss for authentication but not for encryption, then it should at least try to navigate the Kerberos authentication.

Improve this answer
2
  • In pg_hba.conf I have changed hostgssenc all all all gss to host all all all gss restarted PostgreSQL server and now from PostgreSQL/Windows connection is successful. SQL: select gss_authenticated, encrypted from pg_stat_gssapi where pid = pg_backend_pid(); in column gss_authenticated =t in column encrypted=f. Thanks for this. But I want to have Kerberos+encryption. Any idea how to set this on PostgreSQL/Windows?
    – folow
    Jun 16 at 11:00
  • I don't know. If it were easy to make Windows use krb for encryption, I assume it would have been done already. You can try to compile the code yourself, but I wouldn't be optimistic about succeeding. You could combine ssl for encryption with krb for authentication.
    – jjanes
    Jun 16 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.