Re: PATCH: Configurable file mode mask

From: David Steele [mailto:david(at)pgmasters(dot)net]
> >> 3.The default location of the SSL key file is $PGDATA, so the permission
> of the key file is likely to become 0640. But the current postgres requires
> it to be 0600. See src/backend/libpq/be-secure-openssl.c.
> >
> > Yes, that needs to be addressed. There was discussion on another
> > thread that it would be useful to support the SSL key file having
> > group read access, but since this patch is handling the other files it
> > seems like it would make sense to do that change here also.
>
> Perhaps, but since these files are not setup by initdb I'm not sure if we
> should be handling their permissions. This seems to be a distro-specific
> issue.
>
> It seems to me that it would be best to advise in the docs that these files
> should be relocated if they won't be readable by the backup user.
> In any event, I'm not convinced that backing up server private keys is a
> good idea.

Maybe so, but it's convenient to be able to store the key and certificate files in $PGDATA and back them up together. If the database backup were stolen through the compromised backup OS user, then the malicious person can read the data anyway regardless of whether the key file is there or not. That's because the key is not for encrypting data at rest.

Related to this, please see:

https://www.postgresql.org/docs/devel/static/ssl-tcp.html

"On Unix systems, the permissions on server.key must disallow any access to world or group; achieve this by the command chmod 0600 server.key. Alternatively, the file can be owned by root and have group read access (that is, 0640 permissions). That setup is intended for installations where certificate and key files are managed by the operating system. The user under which the PostgreSQL server runs should then be made a member of the group that has access to those certificate and key files."

In the latter case, the file owner is root and the permission is 0640. At first I was a bit confused and misunderstood that the PostgreSQL user account and the backup OS user needs to belong to the same OS group. But that's not the case. The group of the key file can be, for example, "ssl_cert", the PostgreSQL user account belongs to the OS group "ssl_cert" and "dba", and the backup OS user only belongs to "backup." This can prevent the backup OS user from reading the key file. I think it would be better to have some explanation with examples in the above section.

Regards
Takayuki Tsunakawa