How to enforce client to use SSL for postgresql?

Question

Environment:

Windows 10, localhost, same machine
pg 12
node 14
openssl 1.1.1k

I've read and done pg docs starting from this.

postgresql.conf (in C:\Program Files\PostgreSQL\12\data, my understanding is it controls pg DB server)

ssl = on # per pg doc: server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' 
ssl_prefer_server_ciphers = on
ssl_ca_file = 'root.crt' # per pg doc, 18.9.3: To require the client to supply a trusted certificate
ssl_crl_file = ''

pg_hba.conf (in C:\Program Files\PostgreSQL\12\data, my understanding is its effect is on client such as web API or any DB consumers, not DB server)

...
hostssl all             all             127.0.0.1/32 cert clientcert=1
...

pSQL shows it's communicating over SSL:

But a simple node project can connect without SSL:

require('dotenv').config({ path: './environment/PostgreSql.env'});

const pgp = require('pg-promise')();    

const db = pgp(
    {
        user: process.env.PGuser,
        host: process.env.PGhost,
        database: process.env.PGdatabase,
        password: process.env.PGpassword,
        port: process.env.PGport,
        
        ssl: false  // optional, but true gets code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE'
    }
);

var sql = 'select * from current.testssl()';  
db.any(sql)
    .then
    (
        good => 
        { 
            console.log(good); // ssl false gets data 
        },
        bad => 
        { 
            console.log(bad); 
/* ssl true gets 
at TLSWrap.callbackTrampoline (internal/async_hooks.js:130:17) 
{
code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE', 
stack: 'Error: unable to verify the first certificate…ckTrampoline (internal/async_hooks.js:130:17)', 
message: 'unable to verify the first certificate'
}
*/
            
        }
    );

Final Solution based on @Lauranz Albe's and @jjanes, pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostnossl  all  all  0.0.0.0/0  reject  # must be the 1st line!
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
hostssl all             all             0.0.0.0/0   cert clientcert=verify-full

The problem is in something you are not showing us. What is in your .env file? What are the rest of the lines of your pg_hba.conf in order? — jjanes
– jjanes, Commented Dec 2, 2021 at 0:59
@jjanes .env contains things shown in my code above: user, host, databasename, password, etc. Please complete content of pg_hba.conf above. — Jeb50
– Jeb50, Commented Dec 2, 2021 at 1:48
Reject has to be the first line, not the last. But what is the host? It matters what it actually is (well, with that pg_hba it doesn't maybe, but in general. 127.0.0.1 is different from 192.168.0.37) — jjanes
– jjanes, Commented Dec 2, 2021 at 2:34
@jjanes Host? localhost. It works for the node project. pgAdmin is still able to connect without httpS on http://127.0.0.1:53819/browser/. Thank you! — Jeb50
– Jeb50, Commented Dec 2, 2021 at 5:11

Laurenz Albe · Accepted Answer · 2021-12-01 21:02:25Z

4

Add the following line at the beginning of your pg_hba.conf:

hostnossl  all  all  0.0.0.0/0  reject

Then you have to reload PostgreSQL (check the log file if the reload caused any errors).

That will reject all connection attempts that use an unencrypted TCP connection.

See the documentation for details.

edited Dec 1, 2021 at 21:02

answered Dec 1, 2021 at 20:31

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeb50 Over a year ago

still the same, also tried hostnossl all all 127.0.0.1/32 reject with machine restarted. Is it because I'm on the same machine?

Laurenz Albe Over a year ago

I forgot to mention that you have to reload PostgreSQL. If that still does not work, you are editing the wrong pg_hba.conf.

Jeb50 Over a year ago

You're right to reload. After each edit, I "shut down" the machine for at least 10 seconds before turning back on. Tested hostnossl all all 0.0.0.0/0 reject and hostnossl all all 127.0.0.1/32 reject. There is only one pg_hba.conf in my machine, located at C:\Program Files\PostgreSQL\12\data (the data directory of pg). I also tested by adding some extra, got LOG F0000 authentication option not in name=value format: md5 FATAL XX000 could not load pg_hba.conf.

Laurenz Albe Over a year ago

Then you don't have the entries in the correct order.

Collectives™ on Stack Overflow

How to enforce client to use SSL for postgresql?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related