Обсуждение: localhost ssl
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>?
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
> > 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?
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
> 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.
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
> 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".
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
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. >
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
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