Thread: localhost ssl

localhost ssl

From
Rob Sargent
Date:
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

   # 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>?



Re: localhost ssl

From
Adrian Klaver
Date:
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



Re: localhost ssl

From
Rob Sargent
Date:

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

Sorry, here's a likely explaination from postgresql.conf

ssl = on
#ssl_ca_file = '' 
 

ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
#ssl_crl_file = '' 
 

ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

I have no recollection of making those choices (or what I had for 
breakfast).

> 
> 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.

Do you have any thoughts on question #2?



Re: localhost ssl

From
Adrian Klaver
Date:
On 1/22/21 11:49 AM, Rob Sargent wrote:
> 
> 
>> > Also I'm guessing you have ssl = on in postgresql.conf and server 
>> cert setup.
> 
> Sorry, here's a likely explaination from postgresql.conf
> 
> ssl = on
> #ssl_ca_file = ''
> 
> ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
> #ssl_crl_file = ''
> 
> ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
> 
> I have no recollection of making those choices (or what I had for 
> breakfast).
> 
>>
>> 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.
> 
> Do you have any thoughts on question #2?

No, as I really have no idea what:

"In production I hope to name the role with each connection as I want 
the search_path set by the connecting role. ..."

means?

I would point out this:

https://www.postgresql.org/docs/12/auth-cert.html

"User name mapping can be used to allow cn to be different from the 
database user name."

which leads to this:

https://www.postgresql.org/docs/12/auth-username-maps.html



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: localhost ssl

From
Rob Sargent
Date:
> No, as I really have no idea what:
> 
> "In production I hope to name the role with each connection as I want 
> the search_path set by the connecting role. ..."
> 
> means?
My apologies: I rely on the search_path being set according to the role 
(--user).

Perhaps what I was missing is that the connection string uses both 
username and certificate?

> 
> I would point out this:
> 
> https://www.postgresql.org/docs/12/auth-cert.html
> 
> "User name mapping can be used to allow cn to be different from the 
> database user name."
> 
Just prior to that quote is
"The cn (Common Name) attribute of the certificate will be compared to 
the requested database user name, and if they match the login will be 
allowed."
which leads to me to believe I would need a cert per role.

> which leads to this:
> 
> https://www.postgresql.org/docs/12/auth-username-maps.html
I don't think the mapping tricks help me, but happy to be convinced 
otherwise.

I have specific roles accessing specific schemas via sql which is not 
schema qualified.




Re: localhost ssl

From
Adrian Klaver
Date:
On 1/22/21 1:11 PM, Rob Sargent wrote:
> 

> Just prior to that quote is
> "The cn (Common Name) attribute of the certificate will be compared to 
> the requested database user name, and if they match the login will be 
> allowed."
> which leads to me to believe I would need a cert per role.
> 
>> which leads to this:
>>
>> https://www.postgresql.org/docs/12/auth-username-maps.html
> I don't think the mapping tricks help me, but happy to be convinced 
> otherwise.

Check out this section:

https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CLIENT-CERTIFICATES

"...  the cn (Common Name) in the certificate matches the user name or 
an applicable mapping."

This section spells out what is needed for the various forms of client 
cert SSL authentication.

> 
> I have specific roles accessing specific schemas via sql which is not 
> schema qualified.
> 

I'm  assuming this is some sort of security. Just wondering if there is 
provision made for people who know how to do SET search_path or \dn or 
schema qualify objects?


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: localhost ssl

From
Rob Sargent
Date:
> Check out this section:
> 
> https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CLIENT-CERTIFICATES
> 
> "...  the cn (Common Name) in the certificate matches the user name or 
> an applicable mapping."
> 
> This section spells out what is needed for the various forms of client 
> cert SSL authentication.
> 
>>
>> I have specific roles accessing specific schemas via sql which is not 
>> schema qualified.
>>
> 
> I'm  assuming this is some sort of security. Just wondering if there is 
> provision made for people who know how to do SET search_path or \dn or 
> schema qualify objects?
> 
> 
Honest, I've been reading 18.9 but as you can see it uses CN for host 
and then 20.12 suggests using CN for role.

Yes, I'm confused.  As I said in reply to Jeff, I would rather not need 
to remember to set the search_path, which I can avoid if I login as "role".



Re: localhost ssl

From
Adrian Klaver
Date:
On 1/22/21 2:48 PM, Rob Sargent wrote:
> 
>> Check out this section:
>>
>> https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CLIENT-CERTIFICATES
>>
>> "...  the cn (Common Name) in the certificate matches the user name or 
>> an applicable mapping."
>>
>> This section spells out what is needed for the various forms of client 
>> cert SSL authentication.
>>
>>>
>>> I have specific roles accessing specific schemas via sql which is not 
>>> schema qualified.
>>>
>>
>> I'm  assuming this is some sort of security. Just wondering if there 
>> is provision made for people who know how to do SET search_path or \dn 
>> or schema qualify objects?
>>
>>
> Honest, I've been reading 18.9 but as you can see it uses CN for host 
> and then 20.12 suggests using CN for role.

Difference between server certificate and client certificate.

To get a handle on this is going to take an outline of what your 
authentication needs are?


> 
> Yes, I'm confused.  As I said in reply to Jeff, I would rather not need 
> to remember to set the search_path, which I can avoid if I login as "role".

I have not seen that conversation and I do not see it in the archive 
either. Is that off-list, different thread, something else?



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: localhost ssl

From
Rob Sargent
Date:

On 1/22/21 3:54 PM, Adrian Klaver wrote:
> On 1/22/21 2:48 PM, Rob Sargent wrote:
>>
>> Honest, I've been reading 18.9 but as you can see it uses CN for host 
>> and then 20.12 suggests using CN for role.
> 
> Difference between server certificate and client certificate.
> 
> To get a handle on this is going to take an outline of what your 
> authentication needs are?
> 
> 
>>
>> Yes, I'm confused.  As I said in reply to Jeff, I would rather not 
>> need to remember to set the search_path, which I can avoid if I login 
>> as "role".
> 
> I have not seen that conversation and I do not see it in the archive 
> either. Is that off-list, different thread, something else?
> 
> 
I missed reply-all on responding to Jeff.  Here is that for the list:

 > This is what I use.  We have a user and schema per client in a 
multi->tenant database, as in user = bob, schema = bob, database = 
our_company
 >
 > alter user bob set search_path to bob,our_company_common,public;
 >
 > Not sure your use case is the same though...

Yes, my case is very similar.  I just want to avoid needing to remember 
to set the search_path every time.  Since I alter the role to have a 
specific search_path as follows, all I need to do is login as "bob":

create schema if not exists sgstemplate\p\g
create role sgstemplate with login encrypted password '<SOMETHING>'\p\g
alter role sgstemplate set search_path=sgstemplate,base,public\p\g
grant connect on database PROJDB to sgstemplate\p\g

"sgstemplate" gets reset by "sed s/sgstemplate/bob/g"

I only wish I could set a default database as well, but IMHO that's a 
failing of JDBC as much as postgres.
> 



Re: localhost ssl

From
Paul Förster
Date:
Hi Rob,

> On 22. Jan, 2021, at 23:48, Rob Sargent <robjsargent@gmail.com> wrote:
>
> Yes, I'm confused.  As I said in reply to Jeff, I would rather not need to remember to set the search_path, which I
canavoid if I login as "role". 

I didn't follow the whole discussion, so sorry, to just jump in here.

You don't need to remember the search_path once your role is set up correctly. The following example demonstrates that
youcan set a default search_path for a role. But keep in mind that it is a *default* search_path for that role, which
meansa) it's for that role only and b) since it's a default, it is in effect only after the next login to that role. 

db01=# \conninfo
You are connected to database "db01" as user "paul" on host ...

db01=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

db01=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

db01=# create schema s1;
CREATE SCHEMA
db01=# create schema s2;
CREATE SCHEMA
db01=# create schema s3;
CREATE SCHEMA
db01=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 s1     | paul
 s2     | paul
 s3     | paul
(4 rows)

Now comes the crucial part. Notice that the search path still shows the old value until I reconnect:

db01=# alter role paul set search_path to s2, s3;
ALTER ROLE
db01=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

db01=# \c db01
psql (13.1, server 12.5)
You are now connected to database "db01" as user "paul".
db01=# show search_path;
 search_path
-------------
 s2, s3
(1 row)

See? No $user, public or s1 after connecting to the database, only s2 and s3 as specified by me.

The same applies to resetting it to its default values "$user", public:

db01=# alter role paul reset search_path;
ALTER ROLE
db01=# show search_path;
 search_path
-------------
 s2, s3
(1 row)

db01=# \c db01
psql (13.1, server 12.5)
You are now connected to database "db01" as user "paul".
db01=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

There's a lot of descriptions on how this works. Just google for something like "postgres set default search path".

Also, see chapter 5.9.3 of the docs: The Schema Search Path
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

Hope this helps.

Cheers,
Paul


Re: localhost ssl

From
Paul Förster
Date:
Hi Rob,

> On 23. Jan, 2021, at 00:02, Rob Sargent <robjsargent@gmail.com> wrote:
>
> I only wish I could set a default database as well, but IMHO that's a failing of JDBC as much as postgres.

you can't for a role or a user. You'd have to specify the database in the jdbc connection string. It's all here:

https://jdbc.postgresql.org/documentation/head/connect.html

You must include a database name, and optionally a role to connect to, in the jdbc connection string.

Cheers,
Paul