How to enforce client to use SSL for postgresql?-CodePudding

Environment:

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

I've read and done pg docs starting from

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

CodePudding user response：

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.