Re: PATCH: Configurable file mode mask

On 3/15/17 1:56 AM, Tsunakawa, Takayuki wrote:
> From: pgsql-hackers-owner(at)postgresql(dot)org
>> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of David Steele
>> Sure, but having the private key may allow them to get new data from the
>> server as well as the data from the backup.
>
> You are right. My rough intent was that the data is stolen anyway. So, I thought it might not be so bad to expect to be able to back up the SSL key file in $PGDATA together with the database. If it's bad, then the default value of ssl_key_file (=$PGDATA/ssl.key) should be disallowed.

I think it really depends on the situation and the user needs to make an
evaluation of the security risks for themselves.

>> 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.
>
> Yes, but I thought, from the following message, that you do not recommend that the backup user be able to read the SSL key file. So, I proposed to describe the example configuration to achieve that -- postgres user in dba and ssl_cert group, and a separate backup user in only dba group.

That would work as long as the key was not in $PGDATA. Most database
backup software does not have a --ignore option because of the obvious
dangers.

>>>> 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.
>
>> 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.
>
> So you think there are configurations that may be useful or not, don't you? Adding a new parameter could possibly complicate what users have to consider. Maximal flexibility could lead to insecure misuse. So I think it would be better to describe secure and practical configuration examples in the SSL section and/or the backup chapter. The configuration factor includes whether the backup user is different from the postgres user, where the SSL key file is placed, the owner of the SSL key file, whether the backup user can read the SSL key file.

I think we are more or less on the same page, and you'd like to see
documentation that shows examples of secure configurations when group
access is allow. I agree and I'm working on documentation for all the
sections you recommended.

Thanks,
--
-David
david(at)pgmasters(dot)net