CODEWITHSUNDEEP
postgresqlssl
Jeb50
Jeb50

Reputation: 7077

How to enforce client to use SSL for postgresql?

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: enter image description here

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

Upvotes: 3

Views: 2542

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248295

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.

Upvotes: 4

Related Questions