Access PostgreSQL server via SSL/Internet
Is anybody considering this? So that people can write program which access
a database via the Internet. What I'm getting at is that we have
applications which run on our Intranet. They query and update
databases. There is interest in a work at home solution. Since the company
has multiple T1 Internet connections, they are interested in allowing
people to use their home ISP to connect. We are looking at a VPN solution
as well, but they all seem to have a "per seat" or "concurrent
use" restriction. The more users, the higher the cost. Also, some ISPs
have stated that using a VPN over their facility is forbidden and will
result in termination of the service. Another possibility is to simply
use a secure Web server and rewrite the applications as CGI's or something
similiar.
More of a curiousity question at present,
John
Is anybody considering this? So that people can write program which access
a database via the Internet. What I'm getting at is that we have
applications which run on our Intranet. They query and update
databases. There is interest in a work at home solution. Since the company
has multiple T1 Internet connections, they are interested in allowing
people to use their home ISP to connect. We are looking at a VPN solution
as well, but they all seem to have a "per seat" or "concurrent
use" restriction. The more users, the higher the cost. Also, some ISPs
have stated that using a VPN over their facility is forbidden and will
result in termination of the service. Another possibility is to simply
use a secure Web server and rewrite the applications as CGI's or something
similiar.
It is trivial to connect clients and servers across an ssh-piped
connection. I'm not sure of the details as far as getting things set up
to be automated for turnkey installations.
- Thomas
It is trivial to connect clients and servers across an ssh-piped
connection. I'm not sure of the details as far as getting things set up
to be automated for turnkey installations.
OTOH, people using ssh-piped connections need actual accounts on
the database server, opposed to just database accounts. That's
something that isn't necessarily a good idea. Also, ssh-piped
connections are decent to setup, but you must always ssh in before
you want to do anything else.
On Sat, 26 Aug 2000, Thomas Lockhart wrote:
have stated that using a VPN over their facility is forbidden and will
result in termination of the service. Another possibility is to simply
use a secure Web server and rewrite the applications as CGI's or something
similiar.It is trivial to connect clients and servers across an ssh-piped
connection. I'm not sure of the details as far as getting things set up
to be automated for turnkey installations.
Thomas,
Thanks for the thought. I just found something called "stunnel" which may
do the trick.
John
On Sat, 26 Aug 2000, John McKown wrote:
On Sat, 26 Aug 2000, Thomas Lockhart wrote:
It is trivial to connect clients and servers across an ssh-piped
connection. I'm not sure of the details as far as getting things set up
to be automated for turnkey installations.Thomas,
Thanks for the thought. I just found something called "stunnel" which may
do the trick.
Also look into "vpnd" - we're using it for a project for a client until I
can get the SSL connection stuff working properly... (Hint, hint... It
would be nice if it was better documented :)
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
* Andrew Selle <aselle@upl.cs.wisc.edu> [000826 07:50] wrote:
It is trivial to connect clients and servers across an ssh-piped
connection. I'm not sure of the details as far as getting things set up
to be automated for turnkey installations.OTOH, people using ssh-piped connections need actual accounts on
the database server, opposed to just database accounts. That's
something that isn't necessarily a good idea. Also, ssh-piped
connections are decent to setup, but you must always ssh in before
you want to do anything else.
Actually I'm pretty sure you can get around this problem with
host keys, but I haven't tried that.
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."
It is trivial to connect clients and servers across an ssh-piped
connection. I'm not sure of the details as far as gettingthings set up
to be automated for turnkey installations.
Thomas,
Thanks for the thought. I just found something called
"stunnel" which may
do the trick.
Also look into "vpnd" - we're using it for a project for a
client until I
can get the SSL connection stuff working properly... (Hint, hint... It
would be nice if it was better documented :)
Docs of the SSL stuff is coming up as soon as I get "final approval" of
the patch that brings SSL up to working (e.g. either applying or
rejectnig :-). I have a very rough outline so far, but I don't want
to put down too much work into it until I know I am documenting the
right thing (the version that will eventually go in, that is).
But it's on it's way.
//Magnus
Import Notes
Resolved by subject fallback
Docs of the SSL stuff is coming up as soon as I get "final
approval" of
the patch that brings SSL up to working (e.g. either applying or
rejectnig :-). I have a very rough outline so far, but I don't want
to put down too much work into it until I know I am documenting the
right thing (the version that will eventually go in, that is).Your patch looked fine to me, the details can be hammered out later.
Ok. Great. That's what I needed to hear.
What I'd like to see is some at least informal documentation
on how to use
this at all. We can't put in any patches that we don't know
how to use.
Here is a patch against the same cvs tree as the SSL patch (Aug 20).
I hope I didn't mess the SGML up too bad, but somebody should definitly
look that over. I tried to steal as much as I could from around :-)
This patch updates:
* Installation instructions (paragraph on how to compile with openssl)
* Documentation of pg_hba.conf (added "hostssl" record docs)
* Libpq documentation (added connection option, documentation of
PQgetssl() function)
* Add section on SSL to "Server Runtime Environment"
If you beleive any particular area needs more attention, please let me know.
//Magnus
Attachments:
ssldoc_patchapplication/octet-stream; name=ssldoc_patchDownload
diff -cr pgsql/doc/src/sgml/client-auth.sgml pgsql_ssl/doc/src/sgml/client-auth.sgml
*** pgsql/doc/src/sgml/client-auth.sgml Sat Jul 15 23:35:47 2000
--- pgsql_ssl/doc/src/sgml/client-auth.sgml Sun Aug 27 15:24:39 2000
***************
*** 53,58 ****
--- 53,59 ----
<synopsis>
local <replaceable>database</replaceable> <replaceable>authentication-method</replaceable> [ <replaceable>authentication-option</replaceable> ]
host <replaceable>database</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> [ <replaceable>authentication-option</replaceable> ]
+ hostssl <replaceable>database</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> [ <replaceable>authentication-option</replaceable> ]
</synopsis>
The meaning of the fields is as follows:
***************
*** 75,80 ****
--- 76,95 ----
networks. Note that TCP/IP connections are completely disabled
unless the server is started with the <option>-i</option> or
the equivalent configuration parameter is set.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>hostssl</literal></term>
+ <listitem>
+ <para>
+ This record pertains to connection attemps with SSL over
+ TCP/IP. Note that SSL connections are completely disabled
+ unless the server is started with the <option>-i</option>,
+ and also require ordinary TCP/IP connections to be enabled.
+ SSL connections also require SSL support to be enabled in
+ the backend at compile time.
</para>
</listitem>
</varlistentry>
diff -cr pgsql/doc/src/sgml/installation.sgml pgsql_ssl/doc/src/sgml/installation.sgml
*** pgsql/doc/src/sgml/installation.sgml Sat Jul 22 16:48:01 2000
--- pgsql_ssl/doc/src/sgml/installation.sgml Sun Aug 27 15:38:41 2000
***************
*** 592,597 ****
--- 592,615 ----
</varlistentry>
<varlistentry>
+ <term>--with-openssl=<replaceable>DIRECTORY</></term>
+ <listitem>
+ <para>
+ Build with support for SSL (encrypted) connections.
+ This requires the OpenSSL library to be installed.
+ The <replaceable>DIRECTORY</> argument specifies the
+ root directory of the OpenSSL installation.
+ </para>
+
+ <para>
+ <filename>configure</> will check for the required header
+ files and libraries to make sure that your OpenSSL
+ installation is sufficient before proceeding.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>--enable-syslog</term>
<listitem>
<para>
diff -cr pgsql/doc/src/sgml/libpq.sgml pgsql_ssl/doc/src/sgml/libpq.sgml
*** pgsql/doc/src/sgml/libpq.sgml Tue May 2 22:01:52 2000
--- pgsql_ssl/doc/src/sgml/libpq.sgml Sun Aug 27 15:42:06 2000
***************
*** 177,182 ****
--- 177,193 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>requiressl</literal></term>
+ <listitem>
+ <para>
+ Set to '1' to require SSL connection to the backend. Libpq
+ will then refuse to connect if the server does not support
+ SSL. Set to '0' (default) to negotiate with server.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
If any parameter is unspecified, then the corresponding
***************
*** 631,636 ****
--- 642,666 ----
Note that the <acronym>PID</acronym> belongs to a process
executing on the database
server host, not the local host!
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <function>PQgetssl</function>
+ Returns the SSL structure used in the connection, or NULL
+ if SSL is not in use.
+ <synopsis>
+ SSL *PQgetssl(const PGconn *conn);
+ </synopsis>
+ This structure can be used to verify encryption levels, check
+ server certificate and more. Refer to the OpenSSL documentation
+ for information about this structure.
+ </para>
+ <para>
+ You must define <literal>USE_SSL</literal> in order to get the
+ prototype for this function. Doing this will also
+ automatically include <filename>ssl.h</filename> from OpenSSL.
</para>
</listitem>
</itemizedlist>
diff -cr pgsql/doc/src/sgml/runtime.sgml pgsql_ssl/doc/src/sgml/runtime.sgml
*** pgsql/doc/src/sgml/runtime.sgml Fri Aug 11 20:31:06 2000
--- pgsql_ssl/doc/src/sgml/runtime.sgml Sun Aug 27 15:29:27 2000
***************
*** 1710,1715 ****
--- 1710,1771 ----
</sect1>
<sect1>
+ <title>Secure TCP/IP Connection with SSL</title>
+
+ <para>
+ PostgreSQL has native support for connections over SSL to encrypt
+ client/server communications for increased security. This requires
+ <productname>OpenSSL</productname> to be installed on both client
+ and server systems and support enabled at compile-time using
+ the configure script.
+ </para>
+
+ <para>
+ With SSL support compiled in, the Postgres backend can be
+ started with argument -l to enable SSL connections.
+ When starting in SSL mode, the postmaster will look for the
+ files <filename>server.key</filename> and
+ <filename>server.cert</filename> in the <envar>PGDATA</envar>
+ directory. These files should contain the server private key and
+ certificate respectively. If the private key is protected with a
+ passphrase, the postmaster will prompt for the passphrase and not
+ start until it has been provided.
+ </para>
+
+ <para>
+ The postmaster will listen for both standard and SSL connections
+ on the same TCP/IP port, and will negotiate with any connecting
+ client wether to use SSL or not. Use the <filename>pg_hba.conf</filename>
+ file to optionally require SSL in order to accept a connection.
+ </para>
+
+ <para>
+ For details on how to create your server private key and certificate,
+ refer to the OpenSSL documentation. A simple self-signed certificate
+ can be used to get started testing, but a certificate signed by a CA
+ (either one of the global CAs or a local one) should be used in
+ production so the client can verify the servers identity. To create
+ a quick self-signed certificate, use the <filename>CA.pl</filename>
+ script included in OpenSSL:
+ <programlisting>
+ CA.pl -newcert
+ </programlisting>
+ Fill out the information the script asks for. Make sure to enter
+ the local hostname as Common Name. The script will generate a key
+ which is passphrase protected. To remove the passphrase (required
+ if you want automatic startup of the postmaster), run the command
+ <programlisting>
+ openssl x509 -inform PEM -outform PEM -in newreq.pem -out newkey_no_passphrase.pem
+ </programlisting>
+ Enter the old passphrase to unlock the existing key. Copy the file
+ <filename>newreq.pem</filename> to <filename>PGDATA/server.cert</filename>
+ and <filename>newkey_no_passphrase.pem</filename> to
+ <filename>PGDATA/server.key</filename>. Remove the PRIVATE KEY part
+ from the <filename>server.cert</filename> using any text editor.
+ </para>
+ </sect1>
+
+ <sect1>
<title>Secure TCP/IP Connection with SSH</title>
<note>
Import Notes
Resolved by subject fallback
Applied. Thanks.
[ Charset ISO-8859-1 unsupported, converting... ]
Docs of the SSL stuff is coming up as soon as I get "final
approval" of
the patch that brings SSL up to working (e.g. either applying or
rejectnig :-). I have a very rough outline so far, but I don't want
to put down too much work into it until I know I am documenting the
right thing (the version that will eventually go in, that is).Your patch looked fine to me, the details can be hammered out later.
Ok. Great. That's what I needed to hear.
What I'd like to see is some at least informal documentation
on how to use
this at all. We can't put in any patches that we don't know
how to use.Here is a patch against the same cvs tree as the SSL patch (Aug 20).
I hope I didn't mess the SGML up too bad, but somebody should definitly
look that over. I tried to steal as much as I could from around :-)This patch updates:
* Installation instructions (paragraph on how to compile with openssl)
* Documentation of pg_hba.conf (added "hostssl" record docs)
* Libpq documentation (added connection option, documentation of
PQgetssl() function)
* Add section on SSL to "Server Runtime Environment"If you beleive any particular area needs more attention, please let me know.
//Magnus
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Applied. Thanks. I always love doc patches.
[ Charset ISO-8859-1 unsupported, converting... ]
Docs of the SSL stuff is coming up as soon as I get "final
approval" of
the patch that brings SSL up to working (e.g. either applying or
rejectnig :-). I have a very rough outline so far, but I don't want
to put down too much work into it until I know I am documenting the
right thing (the version that will eventually go in, that is).Your patch looked fine to me, the details can be hammered out later.
Ok. Great. That's what I needed to hear.
What I'd like to see is some at least informal documentation
on how to use
this at all. We can't put in any patches that we don't know
how to use.Here is a patch against the same cvs tree as the SSL patch (Aug 20).
I hope I didn't mess the SGML up too bad, but somebody should definitly
look that over. I tried to steal as much as I could from around :-)This patch updates:
* Installation instructions (paragraph on how to compile with openssl)
* Documentation of pg_hba.conf (added "hostssl" record docs)
* Libpq documentation (added connection option, documentation of
PQgetssl() function)
* Add section on SSL to "Server Runtime Environment"If you beleive any particular area needs more attention, please let me know.
//Magnus
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026