Thread: confirming security.

confirming security.

From
Maz Mohammadi
Date:

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

 

Re: confirming security.

From
John R Pierce
Date:
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

Re: confirming security.

From
Maz Mohammadi
Date:
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

Re: confirming security.

From
Adrian Klaver
Date:
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…I’m 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
>
> ==============
>
> It’s telling me it’s through an SSL connection, but I didn’t specify 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

Re: confirming security.

From
Maz Mohammadi
Date:
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

Re: confirming security.

From
John R Pierce
Date:
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

Re: confirming security.

From
Maz Mohammadi
Date:
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