pg_ssl
As you might know, generating SSL certificates for postgres (to be used
by pgadmin, for example...) can be quite a bear; especially if you need
more than one, since they are based on the username of the postgres user.
I have made two command-line utilities written in python 3.6 to do just
that (I, as a number of other developers do, appreciate python for its
ease of code inspection...); one is called *pg_ssl_server*, and the
other is called *pg_ssl_client*. Packaged together, they are referred to
by the name "*pg_ssl*". They are issued under the postgres license.
They have been tested out on Ubuntu 18 and python 3.6.7 with postgres
11. They were designed to be cross-platform, but they have not been
tested yet on Windows, OSx, BSD, or distros other than Ubuntu. [My
immediate concern is with their ability to run cross-platform; as for
downlevel versions of postgres or python, that is not a priority right
now. The "subprocess" module in python used by the utilities has
inconsistencies working cross-platform in older versions of python; _for
now_, people should just upgrade if they really need to use them...]
If anyone would be interested in testing these and sending back a notice
as to what problems were encountered on their platform, it would be much
appreciated. The availability of these utilities will remove a rather
rough spot from the administration of postgres. To keep noise on this
mail thread to a minimum, please report any problems encountered
directly to my address.
Also, if anyone is a security fanatic and facile with python, a code
review would not be a bad idea (the two utilities check in at ~1,500
lines; but since it's python, it's an easy read...)
The latest version of the utility can be retrieved here:
https://osfda.org/downloads/pg_ssl.zip
You can also use the Contact Form at osfda.org to report issues.
On Sat, Apr 27, 2019 at 12:54:07PM -0400, Steve wrote:
As you might know, generating SSL certificates for postgres (to be
used by pgadmin, for example...) can be quite a bear; especially if
you need more than one, since they are based on the username of the
postgres user.
Thanks for sending this along!
Is there a public repo for this, in case people have patches they'd
like to contribute? If not, would you be so kind as to make one?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Will be doing in just a few days. I am taking _initial_ suggestions,
incorporating them, then I will be setting that up.
Show quoted text
On 4/28/2019 11:25 AM, David Fetter wrote:
On Sat, Apr 27, 2019 at 12:54:07PM -0400, Steve wrote:
As you might know, generating SSL certificates for postgres (to be
used by pgadmin, for example...) can be quite a bear; especially if
you need more than one, since they are based on the username of the
postgres user.Thanks for sending this along!
Is there a public repo for this, in case people have patches they'd
like to contribute? If not, would you be so kind as to make one?Best,
David.
Greetings,
* Steve (steve.b@osfda.org) wrote:
As you might know, generating SSL certificates for postgres (to be used by
pgadmin, for example...) can be quite a bear; especially if you need more
than one, since they are based on the username of the postgres user.
Well, you can map the common name in the client certificate to another
user if you want using pg_ident.conf.
I have made two command-line utilities written in python 3.6 to do just that
(I, as a number of other developers do, appreciate python for its ease of
code inspection...); one is called *pg_ssl_server*, and the other is called
*pg_ssl_client*. Packaged together, they are referred to by the name
"*pg_ssl*". They are issued under the postgres license.They have been tested out on Ubuntu 18 and python 3.6.7 with postgres 11.
If you're targeting PG11, I'd strongly recommend using 'scram' as the
password auth type and not md5.
If anyone would be interested in testing these and sending back a notice as
to what problems were encountered on their platform, it would be much
appreciated. The availability of these utilities will remove a rather rough
spot from the administration of postgres. To keep noise on this mail thread
to a minimum, please report any problems encountered directly to my address.Also, if anyone is a security fanatic and facile with python, a code review
would not be a bad idea (the two utilities check in at ~1,500 lines; but
since it's python, it's an easy read...)
I've only glanced through the code and haven't tested it myself, but it
seems like a pretty serious issue that you're just using clientcert=1
instead of using clientcert=verify-full, though unfortunately we didn't
get that until 0516c61b756e39ed6eb7a6bb54311a841002211a. Have you
tested that what you're doing here worked with latest HEAD and
clientcert=verify-full on the server side, and setting
sslmode=verify-full on the client side?
The latest version of the utility can be retrieved here:
https://osfda.org/downloads/pg_ssl.zip
Not sure what can be done about it, if anything, but calling this
'pg_ssl' seems awfully likely to lead to confusion when what you're
really doing here is creating SSL certificates and doing a bit of PG
configuration.. Maybe 'pg_setup_ssl' or similar would be better?
Thanks,
Stephen
Update: I have moved the previously contributed "pg_ssl" package to a
formal git, and have renamed it to "pg_ssl_init"
(at the request of initial reviewers, who were concerned about future
name collisions...)
"pg_ssl_init" is a set of command line scripts that conveniently
configures self-signed server and client keys and certificates to
accommodate secure SSL connections to a postgres server (typically, via
pgadmin...)
Its git is at: https://gitlab.com/osfda/pg_ssl_init