PostgreSQL Configuration – Force SSL Mode Verify-Full

configurationpostgresqlpostgresql-9.3

I have created my CA, Server and Clients certificates and keys. I have tried connections using sslmode flags with psql. I have edited the pg_hba.conf file to force SSL using hostssl only. Now I would like to set my server in order to force the verification to be sslmode=verify-full. I have read twice the documentation I and do not understand how to proceed. They say that it is controlled by an environment variable, but just cannot make it work. How must I set this variable and how would I make it persistent. Do anyone know how to proceed?

Update: I am runing a PostgreSQL 9.3 on Ubuntu Server 14.04

Best Answer

Maybe you want to limit the clients to those which present a given certificate, as described in Using client certificates:

To require the client to supply a trusted certificate, place certificates of the certificate authorities (CAs) you trust in the file root.crt in the data directory, set the parameter ssl_ca_file in postgresql.conf to root.crt, and set the clientcert parameter to 1 on the appropriate hostssl line(s) in pg_hba.conf

On the other hand, sslmode=verify-full is a client-side feature. It benefits a client by ensuring that it connects to the intended server. It does not benefit a server as it's the server that is being checked.

Having a server being configured to "refuse to not being checked" doesn't seem to make much sense, as if a server would say "I don't trust myself".

Related Solutions

Postgresql – Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

PgBarman – Using for PostgreSQL 9.3 Backup

You are looking for point in time recovery (for them awkward moments when you forget the where clause "delete from customers")

Barman sounds like a good way to go. Barman uses built in Postgres functionality but makes managing the base backups and WAL logs a lot easier (backup and WAL log cataloger, Point in time recovery manager, manage many servers from 1 location)

Your suggested strategy sounds fine, you can set the backup location in the barman conf to point at your NAS, you can then set the archive_command in postgresql.conf to rsync WAL's to your NAS as they are rotated.

There is documentation on their website http://docs.pgbarman.org/#introduction

Best Answer

Related Solutions

Postgresql – Authentification failure for PostgreSQL server (9.3)

PgBarman – Using for PostgreSQL 9.3 Backup

Related Question