Re: Unexpected behavior from using default config value

So it turns out that it was indeed a problem with my configuration rather
than postgresql -- my bad.

It looks like Ubuntu 21.04 adds the following to the configs:
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

I was able to find this through the reset_val column in pg_settings. Thanks
for the help everyone!

On Wed, Sep 1, 2021 at 3:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yongqian Li <yongqli(at)kerrmetric(dot)com> writes:
> > I encountered this problem while I was trying to enable SSL on my
> > postgresql server. Since I was satisfied with the default values for the
> > "ssl_key_file" and "ssl_cert_file" settings I chose to not configure them
> > -- I simply turned on "ssl" and copied over the files to the default
> > locations. However, I kept getting certificate errors on the client.
> > Examining the certificate sent by the server using `openssl s_client
> > -starttls postgres -connect "$HOSTNAME:5432"` revealed that the server
> was
> > sending some auto-generated cert instead of the one in "server.crt".
> > Setting the "ssl_key_file" and "ssl_cert_file" settings explicitly to
> their
> > default value fixed the problem.
>
> This is pretty hard to believe, and I couldn't duplicate it in a simple
> test:
>
> 1. Make a server certificate as per the recipe at
>
>
> https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CERTIFICATE-CREATION
>
> (I followed the variant with a private certificate authority.)
>
> 2. Copy certificate and key into $PGDATA/server.crt & server.key,
> setting appropriate file permissions.
>
> 3. Edit postgresql.conf to set "ssl = on", touching nothing else.
>
> 4. "pg_ctl reload", check server log to verify that it turned SSL
> on. (On older PG versions you might need "pg_ctl restart".)
>
> 5. Probe with "openssl s_client".
>
> The certificate returned to s_client is visibly the same one
> I put into server.crt. openssl fails to verify it, but that's
> no surprise since I didn't tell openssl to trust the private
> certificate authority.
>
> I speculate that you forgot to do "pg_ctl reload" after modifying
> the server.crt file, or some similar error. If you can really
> reproduce this problem, please present an exact reproduction
> recipe, and tell us the PG version too.
>
> regards, tom lane
>