PG Phriday: Taking Postgres for GRANTed

Database
Page content

Not every database-backed application needs to be locked down like Fort Knox. Sometimes there are even roles that leverage blanket access to large swathes of available data, if not every table, simply for auditing or monitoring purposes. Normally this would require quite a bit of preparation or ongoing privilege management, but Postgres came up with a unique solution starting with version 14: predefined roles.

This topic comes up relatively frequently in Postgres chats like Discord, Slack, and IRC. Usually it’s along the lines of: “We have a low security application but have separated read and write access from the table owner to avoid accidents. That user should still be able to read or write any table in the database. What do I do?”

Boy do we have a story for you!

The Old Ways

Users familiar with a database like MySQL may expect some kind of blanket statement like this:

GRANT ALL ON *.* TO 'some_user'@'localhost';

And then this statement effectively acts as a blanket expression covering all existing and future databases and tables. The closest Postgres equivalent is probably something like this:

-- Grant for existing tables
GRANT ALL ON ALL TABLES IN SCHEMA public TO some_user;

-- Grant for all future tables
ALTER DEFAULT PRIVILEGES FOR USER postgres
GRANT ALL ON TABLES TO some_user;

Why so complicated? Postgres grants are far more granular, having separate elements for databases, schemas, tables, views, sequences, types, functions, and every other object type in the database. Postgres also applies grants as a one-time event by modifying the system catalog for each individual table. Future tables won’t be accessible unless there’s an existing default for that specific situation. Heck, the ALTER DEFAULT PRIVILEGES statement alone is worth its own article.

And that default is applied at object creation—if we later remove the default, the previously granted access remains. This is because the default applies actual grants to objects as they’re being created, as grants are tangibly associated with their targets. We can check the relacl column in the pg_class catalog and see every grant associated with tables, views, and sequences referenced there. The same applies to functions (pg_proc), types (pg_type), etc.

It’s a very powerful, but ultimately complicated system that many users simply aren’t ready to handle. What usually happens is a schema creation and management script like this:

CREATE ROLE foo_read;
CREATE ROLE foo_write;

CREATE SCHEMA foo;
GRANT USAGE ON SCHEMA foo TO foo_read;
GRANT USAGE ON SCHEMA foo TO foo_write;

CREATE TABLE foo.bar ( id SERIAL, ... );
GRANT SELECT ON TABLE foo.bar TO foo_read;
GRANT ALL ON TABLE foo.bar TO foo_write;
GRANT ALL ON SEQUENCE foo.bar_id_seq TO foo_write;

GRANT foo_write TO some_user;
GRANT foo_read TO other_user;

This is actually what we’d prefer all developers did. It’s very explicit about which roles are intended to access certain aspects of our data, invoke specific functions, and so on. There’s no ambiguity or magic here, and we can see everything right away rather than making inferences based on following a chain of logic defined by some default rules which may not even exist in this script!

But not every application requires this kind of granularity, and users unfamiliar with the depth of the Postgres privilege system may not really care about access to that extent. The Postgres predefined roles exist for this and other similar cases.

Roles, Simplified

Let’s take a look at some of the notable predefined roles Postgres makes available to administrators starting with version 14:

  • pg_read_all_data - Read any data from any table or view, in any schema. Good for low-security application stacks where viewing data is low risk.
  • pg_write_all_data - Write data to any table or sequence, in any schema. Good for simplified stacks where there’s a single user associated with all database writes.
  • pg_read_all_stats - Can view any of the pg_stat_* tables. Great for monitoring table and index statistics without access to anything else.
  • pg_monitor - In addition to system stats, also exposes server settings and allows use of forensic tools which may physically scan tables. This should be restricted to diagnostics that make use of extensions like pgstattuple, pg_freespacemap and so on.
  • pg_signal_backend - Allows non-superusers to cancel queries and terminate sessions, and can be useful for maintenance scripts or elevated privilege users.
  • pg_checkpoint (v15+) - Checkpoints are important for starting backups quickly or prior to starting maintenance, and would normally requires a superuser.
  • pg_create_subscription (v16+) - Allows non-superusers to subscribe to remote publications. This one is very handy if data ingress is used frequently.
  • pg_maintain (v17?) - Launch VACUUM, ANALYZE and other maintenance tasks normally restricted to superusers.

If some of these look familiar, it’s because we wrote about predefined roles in regard to system monitoring a few weeks ago. This is a topic that keeps on giving, and helps end-users of all description. More importantly, note the gradual introduction of roles which facilitate delegation of specific superuser responsibilities. The less exposure of a user that can do literally anything, the better!

How does our previous foo schema creation script look now?

CREATE SCHEMA foo;
CREATE TABLE foo.bar ( id SERIAL, ... );

GRANT pg_write_all_data TO some_user;
GRANT pg_read_all_data TO other_user;

That’s fewer than half the SQL statements than before, meaning most of our initial creation script was purely managing privileges. This is perfect for simplified stacks that may consist of a few tables, or are only ever accessed by a single role other than the object owner. Note that the implicit assumption here is that the owner (often postgres or another superuser) is not used by the application itself, just in case some flaw or exploit causes a privilege escalation. We don’t want any Bobby Tables situations on our hands!

Ironically, this approach makes Postgres privileges slightly easier to manage than MySQL, despite the extremely deep rabbit-hole of the more advanced Postgres privilege system. Provided users know about these predefined roles, that is.

So go forth, young Padawan, and use Postgres in your toy or one-off project without worrying about the finer minutiae. As projects mature and start requiring finer control over access details, Postgres will be ready when you are.