I'm writing a (windows) C++/cli program directly accessing a database and want to optimize security.
Hoster and myself share a good deal of paranoia.
The client(program) does not permanently put certificates and key in a fixed spot, such as %apdata%\postgresql folder.
It generates them temporarily from encrypted source and deletes them immediately after the connection has been established or failed.
The connection requires SSL.
In the case of an attempt to connect to a server without SSL, this produces a modal alert, exposing the certs etc. indefinitely.
I tried to prevent this by using pqping() from libpq.dll, inserting user name and sslmode=verify_ca (or full at some point).
This also produces a modal dialog when failing, however, the certs have not been put out at that point, so that is ok.
Pgping() with sslmode=.... fails when no certificate etc. can be found on the client machine, which defeats my purpose of passing this hurdle before exposing the certs.
Now I could possibly put any old certs and key (temporarily) in %apdata%\postgresql, haven't tried yet because I would prefer not to put senseless stuff on the client machine
(although this would offer even more obfuscation, eh ...). This also is something that might fail on a badly configured system or perhaps, on XP/W7, not sure.
(this idea fails because pqping() only returns ok when finding the correct certs and key)
Are there other ways to achieve this purpose with pqping() or do you have another suggestion to discover the SSL-ness of the server (before actually connecting), so that, by the time of the final connection attempt, failure of the (scram-sha-256) connection attempt will return immediately to my client program?
EDIT: sslmode=require (instead of verify_...) does not appear to require certs in pqping(). Still have to test whether this fails when the server has ssl=off.
All suggestions are welcome!
the setup uses a single DB role for all (end)users, hidden from the enduser. The primary connection does not help the user, who has to log in a second time (he/she do not notice the first) as application user.
The role in postgresql is limited to login and executing specific functions which are then executed with owner privileges appropriate to the job.
This seems a quite secure setup and saves db administration relative to application admin.
Nevertheless we do not want users to try stuff with these minimal privileges outside the client program, using psql or pgAdmin or who knows what.
The libpq will send the certificates stored in file %APPDATA%\postgresql\ in the user's home directory while making the connections.
However, the location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey or the environment variables PGSSLCERT and PGSSLKEY.
For more details refer below link :
Hope this helps.