PG Phriday: Under Observation

Friday April 12, 2024

Have you ever wanted to use a non-superuser role in a Postgres database to perform actions that are normally restricted? Even something as simple as reading from the pg_stat_activity view requires special permissions to view the query column because it could contain sensitive information.

This is hardly useful to monitoring scripts or other forensic tools:

SELECT query FROM pg_stat_activity WHERE query != '';

          query           
--------------------------
 <insufficient privilege>
 <insufficient privilege>
 <insufficient privilege>
 <insufficient privilege>
 <insufficient privilege>
 <insufficient privilege>

In the past, the solution to this conundrum used to be creating a SECURITY DEFINER function that executes as the owner of the function. In this case, we would create a function owned by a superuser and grant access like this:

CREATE ROLE monitor;

CREATE OR REPLACE FUNCTION public.pg_stat_activity()
RETURNS SETOF pg_catalog.pg_stat_activity
AS $$
  SELECT * FROM pg_catalog.pg_stat_activity;
$$ LANGUAGE sql SECURITY DEFINER;

REVOKE ALL ON FUNCTION public.pg_stat_activity() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.pg_stat_activity() TO monitor;

GRANT monitor TO sys_monitor;

Then any user granted the monitor role—the sys_monitor user in this case—could reveal the uncensored contents of the pg_stat_activity view. We even wrote a whole forensics article in the past geared toward this technique.

Then it would look like this when queried:

SELECT query FROM pg_stat_activity();

                         query                         
-------------------------------------------------------
 SELECT query FROM pg_stat_activity WHERE query != '';
 GRANT pg_read_all_stats TO sys_monitor;

Not bad! But let’s be honest, that’s kind of a pain. Additionally, it’s an inherently insecure approach. A better definition might be something like this:

CREATE OR REPLACE FUNCTION public.pg_stat_activity()
RETURNS SETOF pg_catalog.pg_stat_activity
AS $$
  SELECT * FROM pg_catalog.pg_stat_activity;
$$ LANGUAGE sql SECURITY DEFINER
   SET search_path TO pg_catalog;

What if we created these functions before using SET search_path was common practice? How often do we audit our monitoring toolchains and the necessary privileges for them to work? That alone deserves its own conversation, but for now, let’s focus on alternatives.

Postgres acquired a really cool series of new roles back in version 14 that instantly deprecated the approach of using functions this way. One of these is the pg_monitor role. Instead of creating a function for each relevant system view and performing a specific grant, we can just use the role.

GRANT pg_monitor TO sys_monitor;

SET SESSION AUTHORIZATION sys_monitor;

SELECT query FROM pg_stat_activity WHERE query != '';

                         query                         
-------------------------------------------------------
 SELECT query FROM pg_stat_activity WHERE query != '';
 GRANT pg_monitor TO sys_monitor;

Neat, eh? Other immediately useful roles include:

pg_read_all_data - Read any data from any table. Good for low-security application stacks where viewing data is low risk.
pg_read_all_stats - A more restrictive version of pg_monitor that only allows access to the pg_stat_* tables.
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 use logical replication to subscribe to remote publications. This one is very handy if data ingress is used frequently.

There are plenty of others, but hopefully you get the idea. We can see they’ve been adding new roles here for each release, Postgres 17 is no different. Coming soon is the pg_maintain role, which allows invoking VACUUM, ANALYZE, and other important maintenance tasks without superuser or owner privileges.

Now it’s possible to expose these important capabilities to trusted users without requiring a sophisticated series of SECURITY DEFINER functions for each intended task. Though that option is always available if the predefined roles seem too broad.

Either way, Postgres continues to make life easier for DBAs everywhere!

See Also