Thread: confirming security.
Hello all,
I’ve been following the instructions on http://www.postgresql.org/docs/9.1/static/ssl-tcp.html to enable SSL for postgres 9.1 which I have installed on linux.
When I start the server, there is no change in the authentication. I can still login using psql for the same person.
At the end, I need to connect to the DB through JDBC via cert. authentication.
1) Is there a log file which I can check to see if the security algorithm has been changed?
2) If not, is there a way that I can try to validate the changes.
-maz
On 2/21/2013 7:55 PM, Maz Mohammadi wrote: > > When I start the server, there is no change in the authentication. I > can still login using psql for the same person. > did you disable other authentication methods in pg_hba.conf ? I would leave the LOCAL line as peer, and use ssl for HOST lines, then to test, use psql -h localhost ..... -- john r pierce 37N 122W somewhere on the middle of the left coast
Thx John,
It got me a long way. I actually have a more complex installation (I think=
) that I originally thought on my test linux box. Looks like all the files=
that I modify are under /var/lib/post../coord.
I added the line.. to pg_hba.conf
hostssl all all 127.0.0.1/32 cert
and after restarting the coordinator node, it errored because I had to modi=
fy postgresql.conf (ssl=3Doff) . So I feel that the server is now running =
in SSL mode.
But when I used psql...I'm getting this....
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
postgres-xc@adminuser-VirtualBox:~/coord$ psql -h localhost testdb
psql (PGXC 1.0.0, based on PG 9.1.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
testdb=3D# select 2+2;
?column?
----------
4
(1 row)
testdb=3D# \q
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
It's telling me it's through an SSL connection, but I didn't specify any ke=
ystore on my side for psql? Does it pick it up from somewhere?
Any help is greatly appreciated :)
Postgresql isn't half bad ;)
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgr=
esql.org] On Behalf Of John R Pierce
Sent: Thursday, February 21, 2013 11:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] confirming security.
On 2/21/2013 7:55 PM, Maz Mohammadi wrote:
When I start the server, there is no change in the authentication. I can st=
ill login using psql for the same person.
did you disable other authentication methods in pg_hba.conf ? I would =
leave the LOCAL line as peer, and use ssl for HOST lines, then to test, use=
psql -h localhost .....
--
john r pierce 37N 122W
somewhere on the middle of the left coast
On 02/22/2013 07:50 AM, Maz Mohammadi wrote: > Thx John, > > It got me a long way. I actually have a more complex installation (I > think) that I originally thought on my test linux box. Looks like all > the files that I modify are under /var/lib/post../coord. > > I added the line.. to pg_hba.conf > > hostssl all all 127.0.0.1/32 cert > > and after restarting the coordinator node, it errored because I had to > modify postgresql.conf (ssl=off) . So I feel that the server is now > running in SSL mode. > > But when I used psql Im getting this . > > ============== > > postgres-xc@adminuser-VirtualBox:~/coord$ psql -h localhost testdb > > psql (PGXC 1.0.0, based on PG 9.1.4) > > SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) > > Type "help" for help. > > testdb=# select 2+2; > > ?column? > > ---------- > > 4 > > (1 row) > > testdb=# \q > > ============== > > Its telling me its through an SSL connection, but I didnt specify any > keystore on my side for psql? Does it pick it up from somewhere? > > Any help is greatly appreciated J > > Postgresql isnt half bad ;) > Is the above line from pg_hba.conf the only one in the file? If not could you post the entire file contents? Remember in pg_hba.conf first match wins. -- Adrian Klaver adrian.klaver@gmail.com
Ahhh yes....it is now... =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # 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 trust # IPv6 local connections: #host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres-xc trust #host replication postgres-xc 127.0.0.1/32 trust #host replication postgres-xc ::1/128 trust hostssl all all 127.0.0.1/32 cert =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D And the result... postgres-xc@adminuser-VirtualBox:~/coord$ psql -h localhost testdb psql: FATAL: connection requires a valid client certificate FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres-xc", data= base "testdb", SSL off Thank you so much! -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com]=20 Sent: Friday, February 22, 2013 10:58 AM To: Maz Mohammadi Cc: John R Pierce; pgsql-general@postgresql.org Subject: Re: [GENERAL] confirming security. On 02/22/2013 07:50 AM, Maz Mohammadi wrote: > Thx John, > > It got me a long way. I actually have a more complex installation (I > think) that I originally thought on my test linux box. Looks like all=20 > the files that I modify are under /var/lib/post../coord. > > I added the line.. to pg_hba.conf > > hostssl all all 127.0.0.1/32 cert > > and after restarting the coordinator node, it errored because I had to=20 > modify postgresql.conf (ssl=3Doff) . So I feel that the server is now=20 > running in SSL mode. > > But when I used psql...I'm getting this.... > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > postgres-xc@adminuser-VirtualBox:~/coord$ psql -h localhost testdb > > psql (PGXC 1.0.0, based on PG 9.1.4) > > SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) > > Type "help" for help. > > testdb=3D# select 2+2; > > ?column? > > ---------- > > 4 > > (1 row) > > testdb=3D# \q > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > It's telling me it's through an SSL connection, but I didn't specify=20 > any keystore on my side for psql? Does it pick it up from somewhere? > > Any help is greatly appreciated J > > Postgresql isn't half bad ;) > Is the above line from pg_hba.conf the only one in the file? If not could you post the entire file contents? Remember in pg_hba.conf first match wins. -- Adrian Klaver adrian.klaver@gmail.com
On 2/22/2013 8:13 AM, Maz Mohammadi wrote: > Ahhh yes....it is now... > > =========== > # 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 trust > # IPv6 local connections: > #host all all ::1/128 trust > # Allow replication connections from localhost, by a user with the > # replication privilege. > #local replication postgres-xc trust > #host replication postgres-xc 127.0.0.1/32 trust > #host replication postgres-xc ::1/128 trust > hostssl all all 127.0.0.1/32 cert I would leave a local line in front of that like.. local all postgres peer this will allow the postgres user to log on regardless when using unix sockets rather than tcp/ip (eg, when not specifying any -h hostname). handy for database administration and fixing problems. -- john r pierce 37N 122W somewhere on the middle of the left coast
Good tip! Thank you. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgr= esql.org] On Behalf Of John R Pierce Sent: Friday, February 22, 2013 2:35 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] confirming security. On 2/22/2013 8:13 AM, Maz Mohammadi wrote: > Ahhh yes....it is now... > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # 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 trust > # IPv6 local connections: > #host all all ::1/128 trust > # Allow replication connections from localhost, by a user with the #=20 > replication privilege. > #local replication postgres-xc trust > #host replication postgres-xc 127.0.0.1/32 trust > #host replication postgres-xc ::1/128 trust > hostssl all all 127.0.0.1/32 cert I would leave a local line in front of that like.. local all postgres peer this will allow the postgres user to log on regardless when using unix sock= ets rather than tcp/ip (eg, when not specifying any -h hostname). =20 handy for database administration and fixing problems. --=20 john r pierce 37N 122W somewhere on the middle of the left coast --=20 Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general