Support system option for sslrootcert

[dgilman-hrp]

Describe the solution you'd like

postgres v16 and on supports using system for sslrootcert where the system root certificate storage will be used instead of setting the path to a file.

Describe alternatives you've considered

The workaround is to set the path manually, which is workable, but less secure and not the desired behavior.

Additional context

Postgres docs here

[yogeshmahajan-1903]

@dgilman-hrp You can set the value for Root certificate by manually typing system.

[willluongo-vendia]

I am running pgadmin 8.12 on MacOS and it does not seem to honor the "system" value, I get this error:

Unable to connect to server: connection failed: connection to server at "<ip>", port 5432 failed: root certificate file "/Users/<user>/.postgresql/root.crt" does not exist Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification.

from these settings:

However, I can provide the path from this line in pathAbout PgAdmin 4: CA_FILE = "/Applications/pgAdmin 4.app/Contents/Resources/web/cacert.pem". The connection also works from psql using the sslrootcert=system option.

[yogeshmahajan-1903]

@willluongo-vendia

Does file - /Users/<user_name>/.postgresql/root.crt exists on you system? If yes can you please list permission for the same? Also does pgadmin is installed with the same user which is being used to create psql connection?

When I rename root certificate, at ~/.postgresql directory I get the error $ .postgresql % sudo mv root.crt root123.crt
$ .postgresql % psql "host=< ip_addr > port=<port_number> dbname=postgres user=postgres connect_timeout=10 sslmode=verify-full" psql: error: connection to server at "<ip_addr>, port< port_number > failed: root certificate file "/Users/<user_name>/.postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification.

[willluongo-vendia]

@yogeshmahajan-1903 No, that file does not exist on my machine. I am running pgadmin as the same user I installed it with.

[yogeshmahajan-1903]

@willluongo-vendia Have you set environmental variable SSL_CERT_FILE?

[kevin-kortum-trustedshops]

I everyone, i just wanted to confirm the issue for my mac (14.7) as well. I am currently trying to connect to and Postgres RDS instance on AWS using their certificate, but the documentation is quite unclear. So, i was hoping to add the certificate to the system truststore and be done but it seems to be ignored with the same error described above.

FYI: I have not set the env variable mentioned.

[adityatoshniwal]

Hi @dgilman-hrp @kevin-kortum-trustedshops, We've made some changes in this regard, but not entirely sure if it will work. Can you guys test v8.13 once and let us know?

[kevin-kortum-trustedshops]

Hi, i must admit that my assumption from two days ago might be incorrect as i was following the red herring. I learned today that my company IT was blocking TLS on the Postgres port. I never had a chance to test this properly. It now works fine for me but might be unrelated to the problem at all.

[khushboovashi]

No response from the author, closing the issue.