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

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
>
>