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

From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.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
beable 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.
 


> > 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.
> 
> 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
keyfile.  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.
 

> >> 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
complicatewhat users have to consider.  Maximal flexibility could lead to insecure misuse.  So I think it would be
betterto describe secure and practical configuration examples in the SSL section and/or the backup chapter.  The
configurationfactor 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.
 

Regards
Takayuki Tsunakawa