Connecting to a DB using SSL ROOT CERTIFICATE

[fvermelho]

Guys, I have some PostgreSQL that was provisioned in two differente cloud providers and the access mode for those DB is by using an SSLROOTCERT.
I didn't find where to put the certificate in hammerdb. It's possible to do ?

[sm-shaw]

HammerDB includes the option to set the SSL mode:

hammerdb>print dict
Dictionary Settings for PostgreSQL
connection {
 pg_host    = localhost
 pg_port    = 5432
 pg_sslmode = prefer
}

as described here: https://www.postgresql.org/docs/current/libpq-ssl.html
The default location for the SSL ROOTCERT is here ~/.postgresql/root.crt or you can set PGSSLROOTCERT

[fvermelho]

Thank you. Now I'm able to connect to my instances using the SSL certificate ;-)

[maddisurendra]

Hello ,

We are trying to connect to postgresql via hammerdb using ssl certificate, but we are not finding any option on how to pass the certificates in hammerdb?

We dont have postgresql installed locally.

I didn't find where to put the certificate in hammerdb. It's possible to do ?

[sm-shaw]

Hi,
This is the same requirement as described in this discussion. You will have a PostgreSQL client installed rather than a server.
Follow the details in here https://www.postgresql.org/docs/current/libpq-ssl.html to configure the correct settings for the client.

[maddisurendra]

I have installed postgresql15 on my windows laptop and hammerdb as well.

Not really sure where to pass the 3 certificates , can you please guide me the location where can i pass the SSL certificates so that i can connect via hammerdb.

hammerdb>print dict
Dictionary Settings for PostgreSQL
connection {
pg_host = 10.19.15.2
pg_port = 5432
pg_sslmode = prefer
}
tpch {
pg_scale_fact = 1
pg_tpch_superuser =
pg_tpch_superuserpass =
pg_tpch_defaultdbase =
pg_tpch_user =
pg_tpch_pass =
pg_tpch_dbase = test
pg_tpch_tspace = pg_default
pg_tpch_gpcompat = false
pg_tpch_gpcompress = false
pg_num_tpch_threads = 1
pg_total_querysets = 1
pg_raise_query_error = false
pg_verbose = false
pg_refresh_on = false
pg_degree_of_parallel = 25
pg_update_sets = 1
pg_trickle_refresh = 1000
pg_refresh_verbose = false
pg_cloud_query = false
pg_rs_compat = false
}

[sm-shaw]

We are using libpq to connect. As above this successfully worked on Linux so should do the same on Windows using the correct file i.e. %APPDATA%\postgresql\root.crt.

The documentation is here: https://www.postgresql.org/docs/current/libpq-ssl.html look for sections on windows certificates e.g.

To allow server certificate verification, one or more root certificates must be placed in the file ~/.postgresql/root.crt in the user's home directory. (On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.)

34.19.2. Client Certificates

If the server attempts to verify the identity of the client by requesting the client's leaf certificate, libpq will send the certificate(s) stored in file ~/.postgresql/postgresql.crt in the user's home directory. The certificates must chain to the root certificate trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present.

On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key. The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey, or by the environment variables PGSSLCERT and PGSSLKEY.

[maddisurendra]

Thnq able to connect now

…

On Mon, Feb 27, 2023 at 12:48 PM Steve Shaw ***@***.***> wrote: We are using libpq to connect. As above this successfully worked on Linux so should do the same on Windows using the correct file i.e. %APPDATA%\postgresql\root.crt. The documentation is here: https://www.postgresql.org/docs/current/libpq-ssl.html look for sections on windows certificates e.g. To allow server certificate verification, one or more root certificates must be placed in the file ~/.postgresql/root.crt in the user's home directory. *(On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.)* 34.19.2. Client Certificates If the server attempts to verify the identity of the client by requesting the client's leaf certificate, libpq will send the certificate(s) stored in file ~/.postgresql/postgresql.crt in the user's home directory. The certificates must chain to the root certificate trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. On Microsoft Windows these files are named *%APPDATA%\postgresql\postgresql.crt* and *%APPDATA%\postgresql\postgresql.key*. The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey, or by the environment variables PGSSLCERT and PGSSLKEY. — Reply to this email directly, view it on GitHub <#361 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A56E3KSHENWX5E5VM3W5GS3WZTSH3ANCNFSM5WBLWJMQ> . You are receiving this because you commented.Message ID: ***@***.***>

[sm-shaw]

Great, thank-you for updating that it works on Windows as well as Linux

[maddisurendra]

Do you have the documen

…

On Tue, Feb 28, 2023 at 4:17 AM Steve Shaw ***@***.***> wrote: Great, thank-you for updating that it works on Windows as well as Linux — Reply to this email directly, view it on GitHub <#361 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A56E3KVY7TR2I5D26T7A6Q3WZW7CNANCNFSM5WBLWJMQ> . You are receiving this because you commented.Message ID: ***@***.***>

[maddisurendra]

Do you have documentation on how to install hammerdb on linux i am facing issue with display on linux, On Tue, Feb 28, 2023 at 6:07 PM Surendra Maddi ***@***.***> wrote:

…

Do you have the documen On Tue, Feb 28, 2023 at 4:17 AM Steve Shaw ***@***.***> wrote: > Great, thank-you for updating that it works on Windows as well as Linux > > — > Reply to this email directly, view it on GitHub > <#361 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/A56E3KVY7TR2I5D26T7A6Q3WZW7CNANCNFSM5WBLWJMQ> > . > You are receiving this because you commented.Message ID: > ***@***.***> >

[sm-shaw]

Lets make this a new discussion topic