Message-ID: <gh-postgresql-interfaces-psqlodbc-39@github.com>
From: "calcium90 (@calcium90)" <noreply+calcium90@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Thu, 29 Aug 2024 16:57:30 +0000
Subject: [postgresql-interfaces/psqlodbc] issue #39: Presence of kerberos ticket impacts query/connection performance
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Id: 37874903
X-GitHub-Author-Login: calcium90
X-GitHub-Issue: 39
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/39
Content-Type: text/plain; charset=utf-8

_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.