Re: Need a wee bit more info on PostgreSQL's SSL security options

On Mon, Jun 04, 2007 at 04:39:18PM +0200, Andreas wrote:
> I can connect and it seams to work in my current test condition.

Good, please write the tutorial you were looking for, since you now
understand how it goes together. I had intended to do the same, but
have not. I won't remember how it works next time I need it.

> My first idea was to secure the connection with a ssh-tunnel but then I
> can't stop a user to finger for other internal server ports besides
> 5432.

host based firewall port rules might be used to address this.

> The next point is to prohibit former users access to the server, even
> though they might still posess userid+password and the ssl-credentials.

Layers are a good thing in network security.

A vpn with user mgmt seems called for, move the AAA
(http://en.wikipedia.org/wiki/AAA_protocol) off to another system.
OpenVPN might fit here. We use a router based, commercial solution for this
along with an external authentication system.

A vpn is not the whole answer, it might add a layer to keep the good
guys and the dumb, bad guys out. The smart, bad guys will require more
layers from you and ssl seems like a good choice.

> There ssh seems to have the advantage, that one can take away the
> ssl-certificate and allready stop the unwanted guest entering ssh one
> step before the dbms' password check.
> Probaply this can be done with this CertificateRevocationList.

I haven't used this, so I'll defer to the wise.

> Ray Stell schrieb:
> >Read the entries listed here:
> >http://archives.postgresql.org/pgsql-admin/2006-10/msg00103.php
> >
> >Everything came together for me with:
> >http://www.postgresql.org/docs/8.1/interactive/libpq-ssl.html
> >
> >You might want to state your goals, because the config varies depending
> >on what you are trying to accomplish.
> >
> >
> >
> >
> >On Sun, Jun 03, 2007 at 12:20:25AM +0200, Andreas wrote:
> >
> >>Hi,
> >>
> >>I've got it so far:
> >>Server-OS: Debian 3.1 sarge
> >>PostgreSQL: Debian's binary PG 8.1.8 (still the most recent version
> >>available)
> >>
> >>Following a tutorial (actually for OpenVPN as I didn't find any for PG
> >>that goes beyond what is found in the main docu) I created a CA, server
> >>and client certificate, updated postgresql.conf and pg_hba.conf, did a
> >>restart of PG and connected from a windows box with pgAdmin.
> >>NICE :)
> >>
> >>Now as far as I see, even though I have my postgresql.crt+key in place,
> >>I still have to provide username and password, right?
> >>
> >>The server rejects my connection attempt if I move postgresql.crt+key
> >>away. Thats to be expected.
> >>Can I further check the security of the server? The aim will be to have
> >>the port open to the Internet.
> >>
> >>How can I check that PG accepts only keys produced by my CA?
> >>
> >>What would be the correct "Common Name" of a client?
> >>
> >>I read that the client can maintain a file root.crt to check the
> >>identity of the db-server.
> >>Is this the root.crt that sits in PG's data-directory or is it the
> >>server.crt ?
> >>
> >>In the documentation there is a certificate-revocation-list-file
> >>mentioned.
> >>I suspect this is to revoke a formerly granted key that got lost or is
> >>owned by a person who shouldn't be allowed to access the dbms anymore.
> >>How is this CRL file set up?
> >>
> >>
> >>Is there a documentation, that covers those matters more deeply than
> >>chapter 16.8 and 20.1 of PG's main documentation?
> >>Especially the whole client-side topic is rather thin for a newbie.
> >>
> >>
> >>Regards
> >>Andreas
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: if posting/reading through Usenet, please send an appropriate
> >> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> >> message can get through to the mailing list cleanly
> >>
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: don't forget to increase your free space map settings
> >
> >

--
Lost time is when we learn nothing from the experiences of life. Time
gained is when we grow to have a wisdom that is tested in the reality of life.