Directory Permissions
Overview
The purpose of this feature is to allow non-superuser roles read and write capability on system directories through PostgreSQL. Ultimately, this feature is motivated by the principle of least privilege and would contribute to the goal of minimizing the need for superuser privileges for routine tasks that are outside typical system administration. This feature is accomplished by creating a superuser controlled configuration that grants a user/role read and/or write permissions to a specific path that is local to the servers file system (including NFS mounts).
Use Cases
- Allow non-superuser roles access to log files without needing access to the server (pg_read_file, etc). Also, it provide non-superuser roles the ability to select and load a specific range of log files.
- Allow non-superuser roles to load data files utilizing pg_background, thus eliminating the need for DBA to maintain a backend connection from remote system. As well, facilitate general ETL by eliminating the need to pass all data through an intermediary layer or application.
- Allow non-superuser roles ability to CREATE TABLESPACE.
- Allow non-superuser roles to utilize COPY (server side import/export).
- Allow non-superuser roles to utilize Large Objects.
- Allow non-superuser roles to utilize file_fdw.
- Allow DBA's to keep distinct data sets separate and available only to authorized roles.
Design Proposals
- PGC_SUSET GUC - A set of GUC's that can be set per role.
- Keep a "whitelist" of directories stored in a GUC.
- Benefits are that this is fairly light weight/simple.
- Potential issues are that multiple GUCs would be required to provide read/write capability. As well, might not provide the full capability required to provide full feature support.
- Directory Alias
- A catalog that maintains role, path and permission association.
- Entry are maintained through corresponding CREATE, ALTER and DROP.
- Permissions are granted via GRANT or ALTER command.
- Can utilize an ACL scheme to facilitate more complex permissions (beyond READ/WRITE) if necessary.
- Utility functions provide permission checks on path and role.
- A catalog that maintains role, path and permission association.
Similar Features In Other RDBMSs
The following are not meant to serve as 1-to-1 models for this proposed feature. The features of these other systems range in similarity to the proposed functionality. Some are more restrictive than others, while some present some interesting. The purpose of listing these features is to help demonstrate a general need and utility for such a feature.
Considerations
- Should such a configuration be "dump-able"? Perhaps not since it is very likely that paths will not be the same across systems?
