Thread: 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.

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