Re: [HACKERS] PATCH: Configurable file mode mask - Mailing list pgsql-hackers

On 3/14/17 4:23 AM, Tsunakawa, Takayuki wrote:
> From: David Steele [mailto:david@pgmasters.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
anywayregardless of whether the key file is there or not.  That's because the key is not for encrypting data at rest.
 

Sure, but having the private key may allow them to get new data from the
server as well as the data from the backup.

To be clear, the default for this patch is to leave permissions exactly
as they are now.  It also provides alternatives that may or not be
useful in all cases.

> 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
commandchmod 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
thosecertificate 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
misunderstoodthat the PostgreSQL user account and the backup OS user needs to belong to the same OS group.  But that's
notthe 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
readingthe key file.  I think it would be better to have some explanation with examples in the above section.
 

If the backup user is in the same group as the postgres user and in the
ssl_cert group then backups of the certs would be possible using group
reads.  Restores will be a little tricky, though, because of the need to
set ownership to root.  The restore would need to be run as root or the
permissions fixed up after the restore completes.

-- 
-David
david@pgmasters.net