Presence of kerberos ticket impacts query/connection performance

[calcium90]

OS: OpenSUSE Leap 15.6
Kernel: 6.4.0-150600.23.7-default
psqlodbc version: 16.00.0000
PostgreSQL server version: 15.2

I'm having an issue where the presence of a kerberos ticket (valid or expired) for the current user causes queries, or at least connections, to be slower, despite not even using gss as the authentication method.

~/.odbc.ini

[PGTEST]
Driver = /usr/lib64/psqlodbcw.so
Description = Test connection
Servername = test.pgsql.redacted.com
Port = 5432
Username = testuser
Password = redacted

Sample script (pgtest.py)

import pyodbc

conn = pyodbc.connect('DSN=PGTEST;DATABASE=testing')
cursor = conn.cursor()
cursor.execute('SELECT 1')
for row in cursor.fetchall():
    print(row)

Relevant line in pg_hba.conf on the server for testuser:
host all testuser 0.0.0.0/0 scram-sha-256

Now I create the necessary conditions and run the test script, with and without a kerberos ticket present.

With Kerberos Ticket Present

~> kinit -l 60m [email protected]
~> klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: [email protected]

Valid starting     Expires            Service principal
29/08/24 17:20:18  29/08/24 18:20:16  krbtgt/[email protected]

# Run the test script
~> time python3 pgtest.py
(1,)

real	0m0.258s
user	0m0.049s
sys	0m0.008s

Without Kerberos Ticket Present

~> kdestroy
~> klist
klist: No credentials cache found (filename: /tmp/krb5cc_1000)

# Run the test script
~> time python3 pgtest.py
(1,)

real	0m0.137s
user	0m0.039s
sys	0m0.001s

Repeated tests show the same result, with the script being quicker when no kerberos ticket is present.

This seems like a small difference and will have little to no impact in most cases I'd assume, but we do have some larger scripts where the difference adds up, one example being a script that takes 7 minutes with a ticket present, and 10 seconds without. Worth nothing I haven't inspected the detail of that particular script, it may well be that this only happens at connection time and the script in question is inefficiently creating fresh connections thousands of times.

But I think it's beside the point, which is that I don't expect to see any (noticeable) interaction with kerberos at all when I'm not even using gss to authenticate.

[davecramer]

Yes, so this is somewhat documented, albeit well buried https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-GSSENCMODE

Basically if there are credentials present then libpq will try to establish a GSSAPI connection which takes an extra round trip.

Dave