Re: PATCH: Configurable file mode mask

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of David Steele
> PostgreSQL currently requires the file mode mask (umask) to be 0077.
> However, this precludes the possibility of a user in the postgres group
> performing a backup (or whatever). Now that
> pg_start_backup()/pg_stop_backup() privileges can be delegated to an
> unprivileged user, it makes sense to also allow a (relatively) unprivileged
> user to perform the backup at the file system level as well.

I'd like to help review this. First, let me give some questions and comments.

1.What's the concrete use case of this feature? Do you intend to extend the concept of multiple DBAs to the full range of administration of a single database instance, or just multiple OS users for database backup?
If you think that multiple OS user support is desirable to reduce the administration burdon on a single person, then isn't the automated backup sufficient (such as with cron)?

2.Backup should always be considered with recovery. If you allow another OS user to back up the database, can you allow him to recover the database as well?
For example, assume the PostgreSQL user account (the OS user who does initdb and pg_ctl start/stop) is dba1, and dba2 backs up the database using tar or cpio.
When dba2 restores the backup, the owner of the database cluster becomes dba2. If the file permission only allows one user to write the file, then dba1 can't start the instance.

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.

4.I've seen a few users to place .pgpass file in $PGDATA and set the environment variable PGPASSFILE to point to it. They expect it to be back up with other database files. So I'm afraid the permission of .pgpass file also becomes 0640 some time. However, the current code requires it to be 0600. See src/interface/libpq/fe-connect.c.

5.I think some explanation about the concept of multiple OS users is necessary, such as here:

16.1. Short Version
https://www.postgresql.org/docs/devel/static/install-short.html

18.2. Creating a Database Cluster
https://www.postgresql.org/docs/devel/static/creating-cluster.html

[FYI]
Oracle instructs the user, during the software installation, to put "umask 022" in ~/.bashrc or so.
MySQL's files in the data directory appears to be 0640.

Regards
Takayuki Tsunakawa