Re: Allow root ownership of client certificate key

On 3/1/22 3:15 AM, Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>> I'd be more eager to do that if we had some field complaints
>>> about it. Since we don't, my inclination is not to, but I'm
>>> only -0.1 or so; anybody else want to vote?
>
>> This patch was specifically developed in response to field complaints
>> about it working differently, so there's that.
>
> Anyway, I'd be happier about back-patching if we could document
> actual requests to make it work like the server side does.
>

This patch is tidy and addresses an incompatibility with Kubernetes, so
+1 from me for a back-patch.

PGO runs PostgreSQL 10 through 14 in Kubernetes, and we have to work
around this issue when using certificates for system accounts.

For example, we use certificates to encrypt and authenticate streaming
replication connections. We store certificates in the Kubernetes API as
Secrets.[1] Kubernetes then hands those certificates/secrets to a
running container by mounting them as files on the filesystem.

Those files and their directories are managed by Kubernetes (as root)
from outside the container, and processes inside the container (as
not-root) cannot change them. They are mounted with these permissions:

drwxrwsrwt root postgres /pgconf/tls
-rw-r----- root postgres /pgconf/tls/ca.crt
-rw-r----- root postgres /pgconf/tls/tls.crt
-rw-r----- root postgres /pgconf/tls/tls.key

drwxr-sr-x root postgres /pgconf/tls/replication
-rw-r----- root postgres /pgconf/tls/replication/ca.crt
-rw-r----- root postgres /pgconf/tls/replication/tls.crt
-rw-r----- root postgres /pgconf/tls/replication/tls.key

Kubernetes treats the server certificate (top) with the same ownership
and permissions as the client certificate for the replication user
(bottom). The server is happy but anything libpq, including walreceiver,
rejects the latter files for not being "u=rw (0600) or less".

There is an open request in the Kubernetes project to provide more
control over ownership and permissions of mounted secrets.[2] PostgreSQL
is mentioned repeatedly as motivation for the feature.

[1]: https://kubernetes.io/docs/concepts/configuration/secret/
[2]: https://issue.kubernetes.io/81089

-- Chris