Обсуждение: Need a wee bit more info on PostgreSQL's SSL security options
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
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
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 > >
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.
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 ...