Обсуждение: Need a wee bit more info on PostgreSQL's SSL security options

Поиск
Список
Период
Сортировка

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

От
Andreas
Дата:
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


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

От
Ray Stell
Дата:
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@postgresql.org so that your
>       message can get through to the mailing list cleanly

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

От
Andreas
Дата:
Hi Ray,

I can connect and it seams to work in my current test condition.

My goal is to provide a PG server for the employees of a small company.
They do project based services with quite a bit of fluctuation in staff.
The near term goal is to let users access the DB from home until the
company gets big enough to carry an own office.
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. See, it's not that the employees weren't trustworthy but I'd
rather make something impossible to be done than to trust that it won't
be done.

The next point is to prohibit former users access to the server, even
though they might still posess userid+password and the ssl-credentials.
With PG's ssl facility I can just delete the db-user or change his/her
db-password. The former user's ssl-key would still work since it is
validly signed by the server. So this ex-user could connect and try to
brute force entry into the dbms or possibly has userid+pw of a work mate.

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.

So the goal is to have a connection secured against anyone from the
World Evil Web and against (nice) people who have no business relation
to the company anymore.

You could find such a scenario in many companies or even
schools/univeresities, I guess.
I'd like to have some input from people who allready went through this
issues.


Regards
Andreas



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@postgresql.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
>
>


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

От
Ray Stell
Дата:
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@postgresql.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.

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

От
Andreas
Дата:
Well, I think I got this CertificateRevocationList file right but
PostgreSQL 8.1.8 seems to ignore it.
At least if it is enough to host this root.crl file in the DB's data
directory and restart postmaster.

OpenSSL says my test client's certificate was revoked but I still get a
ssl-connection without complaints with pgAdmin.

Well ... checking again ... actually ... those CRL files aren't
mentioned in the 8.1.x docu.
Maybe it isn't present in 8.1.x at all?


There are 1001 ways to bust ones reputation ...