Greetings, * Tsunakawa, Takayuki (tsunakawa.ta...@jp.fujitsu.com) wrote: > From: pgsql-hackers-ow...@postgresql.org > > [mailto:pgsql-hackers-ow...@postgresql.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.
Great! > 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? This is to allow a non-postgres user to perform a backup of the database. Perhaps this could be leveraged for other administration functions, but it's not clear how off-hand to me and the backup use-case is the reason for adding this. > 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)? I'm not quite sure what the question here is, but it is desirable to minimize the amount of access any process requires to only that which is required to perform its duties. In the case of backup, only read access to the data directory and access to connect to PG and run certain functions is required. The ability to run those functions as a non-superuser was added in 9.6, this continues the work to minimize what a backup user needs to perform a backup of the system by allowing a user to have only read-only access to the data directory. There are multiple reasons why matching the privileges a process has to only that which is required is good practice. Minimizing impact to ongoing operations from a compromise of the backup user and reducing the risk that bugs in backup software could disrupt operations are two of those. > 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? That would not be our recommended approach, but it would be possible to do. Our recommended solution is for the backup user to only be able to perform the backup. In this use-case, the restore would be run by the OS user who owns the database (eg: postgres), who would have read-only access to the backup repository. To be clear, this is not hypothetical, pgBackrest supports these configurations and has been tested with this approach. As noted, there are a few additional items that need to be addressed which weren't covered in the initial testing (on Debian-based systems, the pid file and SSL key aren't in the data directory, so they didn't pose a problem, but they should be addressed so that this can work on other distributions). > 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. If the uesr chose to configure the system with both dba1 and dba2 having write access to the data directory, performing a restore as dba2 would be possible and the backup utility could be sure to remove all existing files and restore them from the backup, ensuring that all of the files would be owned by a single user (dba2 in this case). Of course, one would then need to adjust the startup process to run as dba2 instead. With group write access to all of the files and directories, it may be possible to actually run with the dba1 user even though the files are owned by dba2, but we would not recommend it as the result would be a mix of files owned by one dba or the other. This is not the use-case this is being developed for. > 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. > 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. This is not a configuration which we would recommend (generally speaking, it's not really a good idea to drop random files into $PGDATA). That said, there was discussion on another thread about supporting this with an explicit client-side option to allow it. That would be independent from this patch, I believe. > 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 I agree that we should update the documention for this, including those. > [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. When it comes to MySQL, at least, this may be distribution dependent. In any case, it's good to see that we are not the only ones doing this. Thanks! Stephen
signature.asc
Description: Digital signature
