A convenience feature committed for PostgreSQL 14
PostgreSQL has long offered no built-in way to grant a user read (or write) access to every table at once. The pg_read_all_data role, now committed to the repository, removes that gap. The need for it is not theoretical: where a data model cannot be fully anticipated and access requirements change faster than the privilege design keeps up, operators have often solved the problem by handing out superusers.
|
1 2 3 4 5 6 7 8 9 10 |
commit 6c3ffd697e2242f5497ea4b40fffc8f6f922ff60 Author: Stephen Frost <[email protected]> Date: Mon Apr 5 13:42:52 2021 -0400 Add pg_read_all_data and pg_write_all_data roles …. Reviewed-by: Georgios Kokolatos Discussion: https://postgr.es/m/[email protected] |
Why avoiding superusers matters
Superuser access is not merely a data-exposure question. A superuser can execute commands at the OS level, so the impact of a single overly privileged account can reach the database server, and from there the wider company network. Backups or low data sensitivity do not change that risk profile.
Granting it
Usage is a single GRANT to a trustworthy user of your choice, with the parallel pg_write_all_data covering the write case. The issuing session must itself still be a superuser.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
## NB! Assuming logged in as superuser on a devel build # Let’s first create a test table CREATE TABLE data (data jsonb); # And a plain mortal login user called “bob” CREATE USER bob; # Let’s verify that Bob has no privileges to the “data” table. # NB! This is a “psql” command, not SQL... dp+ data Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+-------------------+-------------------+---------- public | data | table | | | (1 row) # Let’s a test row INSERT INTO data SELECT '{'hello': 'world'}'; # And try to look at it as Bob SET ROLE TO bob; /* acting as Bob from now on … */ SELECT * FROM data; ERROR: permission denied for table data # Time to check out that new functionality… RESET ROLE; /* back to superuser again */ GRANT pg_read_all_data TO bob; # Let’s try again as Bob SET ROLE TO bob; # Voila... SELECT * FROM data; data -------------------- {'hello': 'world'} (1 row) |
Options until v14 ships
The commit exists, but release is still many months out. Until then, the requirement can be met with existing means.
- The cleanest approach is a role hierarchy plus
ALTER DEFAULT PRIVILEGES. A meta-role such asappdb_readeris automatically grantedSELECTon every table created afterwards, and that role is then assigned to the login user that needs it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE ROLE appdb_reader; CREATE ROLE data_analyst_role IN ROLE appdb_reader; CREATE USER alice; GRANT data_analyst_role TO alice; -- NB! You need to do this for all schemas! ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO appdb_reader; CREATE TABLE data(id INT); SET ROLE TO alice; /* Should work automagically... */ TABLE data; |
- A quick fix suits static schemas where a hierarchy seems too much work, with the caveat that tables created later still need their own grant.
|
1 |
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appdb_reader; |
- For the more daring: confine superuser access to a replica. A separate replication machine is set up, and access to the real HA nodes is blocked at the
pg_hba.conflevel, so a compromised user cannot damage operations. Heavy read queries may require configuration tuning to avoid replication conflicts.
Limits and design considerations
Because the grant is coarse, it can encourage a less granular database design. Understanding what it implies matters before adopting it, and for more complex projects the established role system remains the better fit. In an enterprise setting, fine-grained access can be the difference between a simple GRANT and an expensive redesign of the whole schema.
