Re: [Postgres-xc-general] can't access server through SSL

[Maz M. <mmo...@pe...>]

Tada....

postgres-xc@adminuser-VirtualBox:~/.postgresql$ psql -h localhost -p 5432 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=#

now I gotta get it working from tomcat ;)

-maz

-----Original Message-----
From: Jim Mlodgenski [mailto:ji...@gm...] 
Sent: Monday, February 25, 2013 11:31 AM
To: Maz Mohammadi
Cc: Nikhil Sontakke; pos...@li...
Subject: Re: [Postgres-xc-general] can't access server through SSL

On Mon, Feb 25, 2013 at 11:12 AM, Maz Mohammadi <mmo...@pe...> wrote:
> Something tells me, I might not be doing things right :(

It does appear things are a little twisted up.

>
> postgres-xc@adminuser-VirtualBox:~/coord$ echo $PGSSLKEY 
> /var/lib/postgres-xc/.postgresql/client.key
> postgres-xc@adminuser-VirtualBox:~/coord$ echo $PGSSLCERT 
> /var/lib/postgres-xc/coord/server.crt
> postgres-xc@adminuser-VirtualBox:~/coord$ openssl verify -CAfile 
> ../coord/root.crt client.crt
> client.crt: OK
> postgres-xc@adminuser-VirtualBox:~/coord$ psql -U postgres-xc -h 
> localhost -p 5432
> psql: could not load private key file 
> "/var/lib/postgres-xc/.postgresql/client.key": key values mismatch 
> postgres-xc@adminuser-VirtualBox:~/coord$
>

Start first with just making sure you can connect via SSL on the server before adding in the certificate authentication. Try changing your pg_hba to:
hostssl all             postgres-xc             127.0.0.1/32            trust

And see if you can connect via psql. There will be a message displayed when connected that it is an ssl connection.

Once you get past that, you can revert back to the original pg_hba and focus on the cert auth. Getting the client certificate correct is a little tricky. I believe you need to sign it using the server certificate, but I need to look that up to be sure. I think you also need to make sure you have the proper mapping in the pg_ident file even though the names might be the same.

> when I generated the key for the client, I used 'postgres-xc' for Common Name, and when I generated it for the server, I used 'localhost'
>
> Do you think I'm digging myself into a whole?  Should I start from scratch and install postgres?

You'll have the same issue using PostgreSQL. Its not a XC vs PG issue, just a severe lack of documentation on how to do it properly.

>
> -maz
>
> -----Original Message-----
> From: Nikhil Sontakke [mailto:ni...@st...]
> Sent: Monday, February 25, 2013 10:21 AM
> To: Maz Mohammadi
> Cc: Michael Paquier; pos...@li...
> Subject: Re: [Postgres-xc-general] can't access server through SSL
>
> Try using
>
> PGSSLKEY=/path/to/agent.key in psql.
>
> Regards,
> Nikhils
>
> On Mon, Feb 25, 2013 at 7:51 PM, Maz Mohammadi <mmo...@pe...> wrote:
>> Well,  it seems that this is the way to connect it from the standard 
>> psql client.  But I'm getting there ;(
>>
>>
>>
>> --------------
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$ whoami
>>
>> postgres-xc
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$ echo $PSGSSLMODE
>>
>> require
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$ echo $PGSSLCERT
>>
>> /var/lib/postgres-xc/datanode1/server.crt
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$ ls -l server.*
>>
>> -rw-rw-r-- 1 postgres-xc postgres-xc 4608 Feb 25 09:00 server.crt
>>
>> -rw------- 1 postgres-xc postgres-xc 1679 Feb 25 09:00 server.key
>>
>> -rw-rw-r-- 1 postgres-xc postgres-xc 3587 Feb 25 09:00 server.req
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$ psql -U postgres-xc -h 
>> localhost -p 5432
>>
>> psql: certificate present, but not private key file 
>> "/var/lib/postgres-xc/.postgresql/postgresql.key"
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$ pwd
>>
>> /var/lib/postgres-xc/coord
>>
>> postgres-xc@adminuser-VirtualBox:~/coord$
>>
>> --------------
>>
>>
>>
>> Does the user 'postgres-xc' need to generate a private key file?
>>
>>
>>
>> postgresql.key does not exist.  I take it that it IS NOT the same as 
>> server.key which is under /var/lib/postgres-xc/coord.  Am I correct?
>>
>>
>>
>>
>>
>> From: Michael Paquier [mailto:mic...@gm...]
>> Sent: Monday, February 25, 2013 2:17 AM
>> To: Nikhil Sontakke
>> Cc: Maz Mohammadi; pos...@li...
>>
>>
>> Subject: Re: [Postgres-xc-general] can't access server through SSL
>>
>>
>>
>>
>>
>> On Mon, Feb 25, 2013 at 4:10 PM, Nikhil Sontakke 
>> <ni...@st...>
>> wrote:
>>
>> Hi Maz,
>>
>>
>>> Is there a way to connect to the server using the psql by using a 
>>> certificate?
>>>
>>
>> Does the below work for you?
>>
>> PSGSSLMODE=require PGSSLCERT=/path/to/agent.crt psql -d postgres -h 
>> localhost -p 5432
>>
>> Change PSGSSLMODE by PGSSLMODE. My 2c.
>>
>> --
>> Michael
>
>
>
> --
> StormDB - http://www.stormdb.com
> The Database Cloud
> Postgres-XC Support and Service
>
> ----------------------------------------------------------------------
> -------- Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics Download AppDynamics Lite 
> for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general