postgresql-interfaces / psqlodbc

Other
16 stars 15 forks source link

Presence of kerberos ticket impacts query/connection performance #39

Open calcium90 opened 3 months ago

calcium90 commented 3 months ago

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 someuser@REDACTED.COM
~> klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: someuser@REDACTED.COM

Valid starting     Expires            Service principal
29/08/24 17:20:18  29/08/24 18:20:16  krbtgt/REDACTED.COM@REDACTED.COM

# 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 commented 2 months ago

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