Re: pg_ssl

Greetings,

* Steve (steve(dot)b(at)osfda(dot)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