SSL Connection to Postgres (Scalegrid)

Hi all!

I am trying to connect to postgres via bipp, and my provider requires SSL but only provides one certificate.

The UI for connecting postgres requires uploading 3 certificates, and all I have available to me is the base cert (no key, no ca chain).

I have worked with Scalegrid support on this, and neither the required mode or verify-ca work, as the error is thrown that the key and ca certs are invalid (I tried to upload the base cert to those 2, blank files, et.c.) nothing works.

Basically, I need the UI to not require the key or CA chain certs for an ssl connection, and we should work, as all I need is the sslmode=require keyword. Technically a cert isn’t even required for that.

Would love to try bipp and use it against my data, but this is a blocking issue for that, and SSL is a requirement for the connection.

Thanks for any help!

hello @wsargent , we looked at this issue today.

We earlier mandated all the certs even when sslmode=require because we assumed that would be more secure.

However, we now understand that depending on how the db server is configured, some of all of the certs are not needed.

We are planning the following changes

  • If sslmode = default ; a) remove the sslrootcert setting, b) make the sslkey and sslcert optional
  • if sslmode = verify-ca or verify-full ; make all the 3 settings mandatory. However there are cases.

Does this plan of action work for you?

If yes, you will have to wait for a few days till our next deployment cycle. We also plan to have unified static IP list for all scenarios by then for you too.

Hi @vj1 ,

This is a good plan. I would recommend that sslmode = require also be the same as default.

For verify CA, I believe only a single cert is actually required, to do that (sslcert) but the CA could be optional in that case.) However I’m not a total SSL expert on that front… I am led to believe that from the scalegrid docs which seem to indicate that verify-ca will work with just the sslcert, since they recommend that setting and do not provide a root cert.

Either way for me, if the sslmode = require has sslkey and sslcert optional, all should be well.

No problem on the wait time, I have plenty on my todo list that’s unrelated! Thanks!

@wsargent We have deployed new version of our platform. We have changed mandatory certificates/keys for Postgres SSL connection.

Please let us know if you face any issues.

Hi @shubhsharma10

That SSL config worked (require), thanks to the team for figuring this out!

Two further items:

It looks like the source IP may have changed, can we get that information so that I can update my firewall as i had to open it to to get it to connect.

Second item, would be any hints or guidance you can give on postgres permissions needed would be good, so that I don’t over-permit.

Hi @wsargent , I will look into IP address issue and guide on how to give permission is below

Create a new role for the read-only user, assuming bipp_db_user is username

CREATE ROLE bipp_db_user LOGIN PASSWORD '<any_strong_password>';

Grant the privileges for the new user to connect to your database:

GRANT CONNECT ON DATABASE pgdatabase TO bipp_db_user;
GRANT USAGE ON SCHEMA public TO bipp_db_user;
Grant SELECT on the tables you want accessible to the read-only user:

Grant SELECT on the tables you want accessible to the read-only user:

GRANT SELECT ON mytable TO bipp_db_user;
If you want to Grant SELECT on all the tables:

If you want to Grant SELECT on all the tables:

   GRANT SELECT ON TABLES TO bipp_db_user;

Link for your reference: create and grant permission

Hi @wsargent You can whitelist this IP address