unable to set sslmode

[cazitouni]

Hello,

I’m encountering an issue because pg2b3dm seems to try to connect to PostgreSQL without SSL. This is a problem since my organization’s database requires SSL connections.

However, I can’t find any option to configure sslmode for the PostgreSQL connection (for example sslmode=prefer or sslmode=require).

Did I miss something, or is there currently no option to configure this?

Kind regards,
Clément

[bertt]

Hi..

The complete connection string to the database is defined in https://github.com/Geodan/pg2b3dm/blob/master/src/pg2b3dm/Program.cs#L36

It has no value for option SslMode, so the default 'Prefer' is used - description 'Try an SSL connection first; if that fails, fall back to non-SSL.'. But it looks like this gives problems in your situation?

Other options for SslMode are:

disable | Do not use SSL. The connection is unencrypted.
allow | Try a non-SSL connection first; if that fails, try SSL.
prefer | Try an SSL connection first; if that fails, fall back to non-SSL. (Default in many clients)
require | Always use SSL. The connection fails if SSL cannot be established.
verify-ca | Use SSL and verify that the server certificate is signed by a trusted Certificate Authority (CA).
verify-full | Use SSL, verify the CA, and verify that the server hostname matches the certificate.

I'm thinking of making the whole connection string configurable, but right now only parts can be configured (not the ssl mode). One
way is to change the connection string, rebuild and see what works in your case.

[cazitouni]

Yes for now I use this command for example

.\pg2b3dm.exe -h bdsigli.cus.fr -U sigli_ddref -c geometrie -t doref.dr_3d_batiment -d sigli_ddref -g 100,0 -p 34000

Which doesn't work since our data force ssl in pg_hba.conf.

Unhandled exception. Npgsql.PostgresException (0x80004005): 28000: no pg_hba.conf entry for host "172.25.20.65", user "sigli_ddref", database "sigli_ddref", no encryption

Reading the output it seems to pass the request with a no_encryption but maybe I'm wrong.

Using the same command with another database where I can pass connection without SSL, it worked great.

It doesn't look like that the request is made using sslmode=prefer, but maybe I missed something.

[bertt]

#256

[bertt]

Before we proceed some questions:

• Are you able to connect to the database from host 172.25.20.65 using another tool (like DBeaver / PGAdmin)?

• What version of pg2b3dm are you using?

[cazitouni]

Hello @bertt,

Thanks for the PR, Yes I can connect to the db but I'm going to test again next monday to be 100% sure if you want.

[bertt]

Ok great, if you're able to build the PR code you can try to use with sslmode=require and see if it works. I'm not able to reproduce
the issue without access to the database so it's a game of try and error at this moment.

[cazitouni]

Hello @bertt,

Sorry I got time to test only this morning.

It work well with the following :

pg2b3dm --connection "Host=bdsigli.cus.fr;Username=sigli_ddref;Database=sigli_ddref;Port=34000;Ssl Mode=Require" -c geometrie -t doref.dr_3d_batiment -g 100,0

But maybe using sslmode instead of Ssl Mode and lowercase everywhere would be more intuitive, I don't know.

Otherwise everything seems fine and I confir it still doesnt pass sslmode with the former method of connection.

Thanks for this PR.

Regards,
Clément.

[bertt]

Ok good we got it working now, I think both 'sllmode' and 'Ssl Mode' is allowed (in lowercase or mixed case). PR will be in next release.

[bertt]

it's in release 2.27.0 https://github.com/Geodan/pg2b3dm/releases/tag/v2.27.0