Re: localhost ssl - Mailing list pgsql-general

On 1/22/21 11:04 AM, Rob Sargent wrote:
> 
> I will need to enforce ssl/tls in my production environment so I thought 
> I would try setting things up on localhost to see how that went.
> 
> Then I noticed that my successful connections from 
> "/usr/lib/postgresql/12/bin/psql -U postgres -h localhost -P pager=off 
> postgres" report:
> 
>    psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1))
>    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, 
> bits: 256, compression: off)
>    Type "help" for help.
> 
> though my pg_hba.conf does not specify SSL at all

Yes it does(implied):

https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

"host

     This record matches connection attempts made using TCP/IP. host 
records match SSL or non-SSL connection attempts as well as GSSAPI 
encrypted or non-GSSAPI encrypted connection attempts."

Also I'm guessing you have ssl = on in postgresql.conf and server cert 
setup.

If you want to enforce SSL then:

"
hostssl

     This record matches connection attempts made using TCP/IP, but only 
when the connection is made with SSL encryption.

     To make use of this option the server must be built with SSL 
support. Furthermore, SSL must be enabled by setting the ssl 
configuration parameter (see Section 18.9 for more information). 
Otherwise, the hostssl record is ignored except for logging a warning 
that it cannot match any connections.
"

Read below for more information:

https://www.postgresql.org/docs/12/ssl-tcp.html


> 
>    # Database administrative login by Unix domain socket
> 
>    local   all             postgres                                peer
> 
> 
>    # TYPE  DATABASE        USER            ADDRESS METHOD
> 
>    # "local" is for Unix domain socket connections only
> 
>    local   all             all                                     peer
> 
>    # IPv4 local connections:
> 
>    host    all             all             127.0.0.1/32            md5
> 
>    host    all             all             127.0.1.1/32            md5
> 
>    # IPv6 local connections:
> 
>    host    all             all             ::1/128                 md5
> 
> 
> So to the questions:
> 1. Am I already getting encrypted connections and if so, how?
> 2. In production I hope to name the role with each connection as I want 
> the search_path set by the connecting role.  Will I need a cert per role 
> with CN=<rolename>?
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com