SSL confirmation

To answer my own question I included the -l flag:

/usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data

No errors were reported, which I guess there would be if:

1. postgreSQL had not ben built with SSL support? or
2. the certificate has not been properly setup?

regards

Andrew
On 5 Dec 2004, at 16:12, Andrew M wrote:

Andrew M. writes:

To answer my own question I included the -l flag:

/usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data

No errors were reported, which I guess there would be if:

1. postgreSQL had not ben built with SSL support? or
2. the certificate has not been properly setup?

You could also use openssl's utilities to diagnose the SSL part of the
connection. For example:

$ openssl s_client -host localhost -port <port>

will show you details about the authentication and encryption in use.

HTH
Andreas

On Sun, Dec 05, 2004 at 04:12:38PM +0000, Andrew M wrote:

I am running postgreSQL and just wanted to know how I confirm that SSL
is fully functional? I have placed server.key, server.crt and root.crt
in the data folder and am able to launch postgreSQL with no problems. I
m launching postgreSQl with the following command:

/usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data

You can omit the -i if you have "tcpip_socket = true" (or set
listen_address if you're using 8.0) in postgresql.conf

Is that sufficient to start SSL, how can I check?

You should have "ssl = true" in postgresql.conf (restart the backend
after making a change). When you make an SSL connection with psql,
psql should print a message like the following:

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Make sure you're using a TCP connection instead of a local (Unix-domain)
connection. You can use psql's -h option or the PGHOST environment
variable to force a TCP connection (e.g., psql -h localhost).

See also the hostssl and hostnossl connection types in pg_hba.conf.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Andreas,
this what I get when I issue the openssl command:

6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake
failure:s23_lib.c:226:

could you explain what this means if you know?

When I do:

/usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data

I get:

LOG: checkpoint record is at 0/DAB280
LOG: redo record is at 0/DAB280; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 1216; next OID: 17668
LOG: database system is ready
LOG: invalid length of startup packet

There is no mention of SSL at all

regards

Andrew
On 5 Dec 2004, at 17:03, Andreas Seltenreich wrote:

Andrew M. writes:

this what I get when I issue the openssl command:

6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake
failure:s23_lib.c:226:

could you explain what this means if you know?

I'm afraid, I think my suggestion to use openssl's s_client with the
postmaster's builtin SSL support was bogus, since Magnus Hagander
writes in an older message: "SSL is not enabled at connection time in
pgsql - it is negotiatied with the postmaster, and enabled later."

<URL:http://groups.google.de/groups?as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentuna.se&gt;

So using the openssl tools won't help here.

Sorry for the inconvenience
Andreas

On Sun, Dec 05, 2004 at 06:59:41PM +0100, Andreas Seltenreich wrote:

Andrew M. writes:

this what I get when I issue the openssl command:

6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake
failure:s23_lib.c:226:

could you explain what this means if you know?

I'm afraid, I think my suggestion to use openssl's s_client with the
postmaster's builtin SSL support was bogus, since Magnus Hagander
writes in an older message: "SSL is not enabled at connection time in
pgsql - it is negotiatied with the postmaster, and enabled later."

<URL:http://groups.google.de/groups?as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentuna.se&gt;

So using the openssl tools won't help here.

Right -- see the "Frontend/Backend Protocol" chapter in the
documentation, in particular the "SSL Session Encryption" section:

http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52782

You can use psql to check if SSL is working. Psql prints a message
like the following if SSL was successfully negotiated:

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Ok,
is it possible to trace/monitor processes as they come into postgresql
tables like you can in windows SQLServer?

regards

Andrew
On 5 Dec 2004, at 18:27, Michael Fuhr wrote:

On Sun, Dec 05, 2004 at 06:37:57PM +0000, Andrew M wrote:

is it possible to trace/monitor processes as they come into postgresql
tables like you can in windows SQLServer?

See the "Run-time Configuration" section in the "Server Run-time
Environment" chapter of the PostgreSQL documentation. Look for
the variables to configure logging.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sun, Dec 05, 2004 at 11:27:57AM -0700, Michael Fuhr wrote:

Right -- see the "Frontend/Backend Protocol" chapter in the
documentation, in particular the "SSL Session Encryption" section:

http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52782

You can use psql to check if SSL is working. Psql prints a message
like the following if SSL was successfully negotiated:

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

I tend to fire up ethereal and look at the data stream to make absolutely
sure that my app is doing SSL to postgresql. I've been burnt once or
twice by the libpq my app uses not negotiating SSL correctly while
the version of libpq that psql uses being just fine (dumb build problems
on my part, but I'd probably have missed them without the sanity check
of sniffing the connection).

Cheers,
Steve

On Sun, Dec 05, 2004 at 11:02:33AM -0800, Steve Atkins wrote:

On Sun, Dec 05, 2004 at 11:27:57AM -0700, Michael Fuhr wrote:

You can use psql to check if SSL is working. Psql prints a message
like the following if SSL was successfully negotiated:

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

I tend to fire up ethereal and look at the data stream to make absolutely
sure that my app is doing SSL to postgresql.

Doesn't hurt to be sure.

I've been burnt once or twice by the libpq my app uses not negotiating
SSL correctly while the version of libpq that psql uses being just
fine (dumb build problems on my part, but I'd probably have missed
them without the sanity check of sniffing the connection).

On the backend side you can force SSL by using "hostssl" in
pg_hba.conf; connections that don't use SSL should then fail instead
of silently proceeding unencrypted. On the client side you could
set the PGSSLMODE environment variable to "require" (or the older
PGREQUIRESSL to "1"), which should tell libpq to attempt only SSL
connections.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sun, Dec 05, 2004 at 12:27:33PM -0700, Michael Fuhr wrote:

On the client side you could set the PGSSLMODE environment variable
to "require" (or the older PGREQUIRESSL to "1"), which should tell
libpq to attempt only SSL connections.

I forgot to mention that you could also use "sslmode=require" or
"requiressl=1" (deprecated in 7.4 and later) in your connect string
if you're using libpq directly or your interface to libpq allows it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael,
are you saying I need to do:

'select * from myTable where x=y sslmode=require'

to make a request to the database a secure one? If so, as I am using
Hibernate, do you know how I would alter my maps to reflect this?

regards

Andrew
On 5 Dec 2004, at 19:41, Michael Fuhr wrote:

On Sun, Dec 05, 2004 at 08:07:26PM +0000, Andrew M wrote:

are you saying I need to do:

'select * from myTable where x=y sslmode=require'

to make a request to the database a secure one? If so, as I am using
Hibernate, do you know how I would alter my maps to reflect this?

No, "sslmode=require" would be part of the string that gets passed
to libpq's PQconnectdb() or its ilk, i.e., the functions that make
the initial connection to the database. If you're using an abstraction
layer that sits above libpq or an interface that implements the
communications protocol without using libpq, then you may or may
not have a way to specify such connection options. Check your
interface's documentation.

If your interface sits above libpq but doesn't allow you to specify
connection options like "sslmode=require", then setting environment
variables might still work. See the "Environment Variables" section
of the "libpq - C Library" chapter in the PostgreSQL documentation.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Ah... ok

I need something like:

<datasources>
<local-tx-datasource>
<jndi-name>PostgresDS</jndi-name>

<connection-url>jdbc:postgresql://localhost:5432/beyarecords</
connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>myName</user-name>
<password>myPass</password>

<sslmode>require</sslmode>

</local-tx-datasource>

</datasources>

would that do the trick? Sorry for so many questions.. i have a lot to
learn about postgreSQL ;-)

regards

Andrew

On 5 Dec 2004, at 20:39, Michael Fuhr wrote:

On Sun, Dec 05, 2004 at 08:50:15PM +0000, Andrew M wrote:

<datasources>
<local-tx-datasource>
<jndi-name>PostgresDS</jndi-name>

<connection-url>jdbc:postgresql://localhost:5432/beyarecords</
connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>myName</user-name>
<password>myPass</password>

<sslmode>require</sslmode>

</local-tx-datasource>

</datasources>

would that do the trick? Sorry for so many questions.. i have a lot to
learn about postgreSQL ;-)

I don't know if that would work or not -- I'm not familiar with the
interface you're using.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

The map i make reference to is a Jboss map used to make a jndi
connection to postgreSQL. What document do I need to access to get hold
of the envireonment variables, namely PGSSLMODE?

regards

Andrew

On 5 Dec 2004, at 21:00, Michael Fuhr wrote:

On Sun, Dec 05, 2004 at 09:10:42PM +0000, Andrew M wrote:

The map i make reference to is a Jboss map used to make a jndi
connection to postgreSQL. What document do I need to access to get hold
of the envireonment variables, namely PGSSLMODE?

I don't know if the J-stuff wraps libpq or if it implements the
communications protocol on its own. If it uses libpq then see the
"libpq - C Library" chapter in the PostgreSQL documentation, in
particular the "Database Connection Control Functions" and "Environment
Variables" sections.

http://www.postgresql.org/docs/

Even if you're using an interface that abstracts libpq and you're
not calling its functions directly, it's useful to know how the
underlying library works.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:

On Sun, Dec 05, 2004 at 09:10:42PM +0000, Andrew M wrote:

The map i make reference to is a Jboss map used to make a jndi
connection to postgreSQL. What document do I need to access to get hold
of the envireonment variables, namely PGSSLMODE?

I don't know if the J-stuff wraps libpq or if it implements the
communications protocol on its own.

The latter. AFAIK it doesn't use environment variables. See the JDBC
driver docs for how to set options when connecting.

-Doug

Hi,
seems like I may have located the solution to my earlier problem:

http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php

many thanks

Andrew
On 5 Dec 2004, at 23:51, Doug McNaught wrote: