#4174 Add full PostgreSQL 16 support
Closed: Dropped 2 months ago by mikem. Opened 6 months ago by frank-mdc.

Since version 16 the special word "system" can be used for the ca root store.
https://www.postgresql.org/docs/16/libpq-connect.html
So that the connect string of:
https://docs.pagure.org/koji/database_howto/
can be changed from:
DBConnectionString = postgresql://koji:example_password@kojidev.example.com/koji?sslmode=verify-full&sslrootcert=/etc/pki/tls/certs/ca-bundle.trust.crt
to
DBConnectionString = postgresql://koji:example_password@kojidev.example.com/koji?sslmode=verify-full&sslrootcert=system

Trying it with koji 1.34.0 under RHEL-9 fails with:

[Tue Aug 20 06:50:58.676701 2024] [wsgi:error] [pid 452038:tid 452196] [client 141.80.124.200:34914]   File "/usr/lib/python3.9/site-packages/kojihub/db.py", line 199, in connect
[Tue Aug 20 06:50:58.676726 2024] [wsgi:error] [pid 452038:tid 452196] [client 141.80.124.200:34914]     conn = psycopg2.connect(dsn=opts['dsn'])
[Tue Aug 20 06:50:58.676744 2024] [wsgi:error] [pid 452038:tid 452196] [client 141.80.124.200:34914]   File "/usr/lib64/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect
[Tue Aug 20 06:50:58.676762 2024] [wsgi:error] [pid 452038:tid 452196] [client 141.80.124.200:34914]     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
[Tue Aug 20 06:50:58.676780 2024] [wsgi:error] [pid 452038:tid 452196] [client 141.80.124.200:34914] psycopg2.OperationalError: root certificate file "system" does not exist
[Tue Aug 20 06:50:58.676797 2024] [wsgi:error] [pid 452038:tid 452196] [client 141.80.124.200:34914] Either provide the file or change sslmode to disable server certificate verification.

Metadata Update from @tkopecek:
- Custom field Size adjusted to None
- Issue set to the milestone: 1.36

6 months ago

We're passing this string straight to psycopg2. If the underlying lib doesn't support this, then Koji can't really do much about that. Koji does not appear to be the problem here. This is probably somewhere between psycopg2 and libpq in your rhel-9 env. On el9, you're probably getting libpq-13, not 16.

I finally managed to do it again.
The solution was quite simple. Install PostgresSQL 16 and make sure that psycopg2 also uses this client library.
ldd /usr/lib64/python3.9/site-packages/psycopg2/_psycopg.cpython-39-x86_64-linux-gnu.so
libpq.so.5 => /usr/pgsql-16/lib/libpq.so.5 (0x00007f2d77fbd000)
Then it works with the option "sslrootcert=system"

Ah, thanks for the update. Will close this.

Metadata Update from @mikem:
- Issue close_status updated to: Dropped
- Issue status updated to: Closed (was: Open)

2 months ago

Metadata Update from @tkopecek:
- Issue set to the milestone: None (was: 1.36)

a month ago

Log in to comment on this ticket.

Metadata