How many people/services have superuser access to your PostgreSQL cluster(s)? Did you ever ask why your software engineers might need it? Or your BI team? Why those use cases require same privileges as someone who can drop your databases?

The answer isn't because these operations are inherently dangerous - it's because PostgreSQL historically offered limited options for operational access or simply because not enough people are aware of the options. So the common practice is to either got basic permissions or handover the keys to the kingdom.

PostgreSQL's built-in predefined roles solve this problem by providing purpose-built privileges for common maintenance tasks. Instead of granting superuser access for routine operations, you can delegate specific capabilities

  • monitoring teams get comprehensive observability access, backup services get data reading capabilities, and maintenance scripts get precisely the permissions they need, nothing more.

What are Predefined Roles?

PostgreSQL's built-in administrative roles are purpose-built permission sets that solve the superuser dilemma for common maintenance tasks. Out of the box, there are 15 predefined roles that provide granular access to specific operational capabilities without requiring full superuser privileges.

While you can view their list and description in official documentation, in this article we will explore them bit more thoroughly and at the same time look into system catalogs to understand them better. The individual roles can be grouped by their functionality and most of them are quite easy to grasp, ranging from simple monitoring access to powerful filesystem operations that require careful consideration.

Data Access Role

  • pg_database_owner - Database-specific ownership (special case)
  • pg_read_all_data - Read access to all tables, views, sequences
  • pg_write_all_data - Write access to all tables, views, sequences

Monitoring & Observability

  • pg_monitor - Which is actually monitoring meta role that contains 3 roles listed below
  • pg_read_all_settings - Configuration access
  • pg_read_all_stats - Statistics views
  • pg_stat_scan_tables - Table scanning for stats

System Operations

  • pg_signal_backend - Cancel queries, terminate sessions
  • pg_checkpoint - Run CHECKPOINT command
  • pg_maintain - VACUUM, ANALYZE, REINDEX operations (PostgreSQL 17+)
  • pg_signal_autovacuum_worker - Signal autovacuum worker (PostgreSQL 18+)

File System Access

  • pg_read_server_files - Read files from server filesystem
  • pg_write_server_files - Write files to server filesystem
  • pg_execute_server_program - Execute programs on server

And specialised use cases

  • pg_create_subscription - Logical replication management
  • pg_use_reserved_connections - Connection reservation (PostgreSQL 16+)

Why Use Predefined Roles?

The primary benefit of predefined roles is expanding the pool of users who can safely manage PostgreSQL databases. Traditional PostgreSQL administration created an artificial binary choice: either users had basic access with limited capabilities, or they required full superuser privileges for operational tasks. This forced many organizations to grant excessive permissions simply to perform routine operations like monitoring, backups, or maintenance.

Predefined roles break this limitation by allowing more granular control over operational tasks without distributing superuser privileges. Instead of having a small number of highly privileged superusers handling all administrative work, organizations can now delegate specific capabilities to appropriate operational roles - monitoring teams get monitoring access, backup services get data reading capabilities, and maintenance scripts get precisely the permissions they need.

The deeper benefit is abstraction. Not only do you switch from manually managing permissions on individual system objects, you switch to managing logical capability sets. This creates a clear separation between what you want to allow and how it's actually implemented.

The advantage is clearly demonstrated in following example

-- granting select on each schema separately
GRANT USAGE ON SCHEMA finance, hr, app, audit TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO analytics_team;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA finance TO analytics_team;

-- versus one time setup
GRANT pg_read_all_data TO analytics_team;

The predefined roles approach automatically covers:

  • All current tables, views and sequences
  • All schemas
  • Future objects created after the GRANT

And the good news is the benefits don't end here. The real thing on operational level comes with the scope of those predefined roles - they apply at cluster level (instead on database level). Only notable exception is role pg_database_owner which we will cover next.

Before going there, let's briefly mention second benefit, and that's the fact any new operational features in PostgreSQL will be covered by those predefined roles automatically.

The Evolution of Predefined Roles

Understanding when and why predefined roles were introduced provides important insight into PostgreSQL's operational priorities and helps explain some of the design decisions. The journey from a single role in PostgreSQL 9.6 to today's comprehensive set reflects real-world pain points that PostgreSQL developers encountered in production environments.

PostgreSQL 9.6 (2016) introduced first predefined role pg_signal_backend to address common frustration - inability to cancel running query without giving away superuser rights.

-- This became possible in 9.6 without superuser privileges
SELECT pg_cancel_backend(12345);  -- Cancel a query
SELECT pg_terminate_backend(12345);  -- Terminate a session

PostgreSQL 10 (2017) brought biggest expansion of predefined roles with, adding four monitoring specific roles to improve database observability, and support growing importance of database monitoring in production environments.

-- Create the monitoring user
CREATE USER postgres_exporter WITH PASSWORD 'monitoring_password';

-- Grant comprehensive monitoring access
GRANT pg_monitor TO postgres_exporter;

Giving access to variety of metrics and settings. With single GRANT you can access data:

  • From pg_read_all_settings (inherited via pg_monitor) to SELECT * FROM pg_settings - Configuration parameters for alerting on misconfigurations and configuration metrics like shared_buffers, work_mem, max_connections

From pg_read_all_stats (inherited via pg_monitor):

-- Database-wide statistics (connections, transactions, blocks read/written)
SELECT * FROM pg_stat_database
-- Current query activity and connection states
SELECT * FROM pg_stat_activity
-- Replication lag and status
SELECT * FROM pg_stat_replication
-- Background writer performance
SELECT * FROM pg_stat_bgwriter
-- Lock contention monitoring
SELECT * FROM pg_locks

From pg_stat_scan_tables (inherited via pg_monitor):

  • Enhanced table statistics collection with sequential scan information
  • More detailed I/O statistics for table access patterns

The pg_monitor role is particularly clever in its design. Rather than being a single monolithic permission set, it's actually a composite of the other three roles. This allows for granular access when needed - you can grant pg_read_all_stats for basic monitoring without including configuration access, or grant the full pg_monitor bundle for comprehensive monitoring capabilities.

PostgreSQL 11 (2018) laid the foundation for secure file system operations with the introduction of three powerful roles that fundamentally changed how PostgreSQL handles server-side file access:

  • pg_read_server_files
  • pg_write_server_files
  • pg_execute_server_program

Addressing many long-standing issues with providing file system access, like ETL processes that needs to read CSV files and data processing pipelines often involving external programs.

The file system roles represented a significant security advancement. Before PostgreSQL 11, operations like server-side COPY FROM file were superuser-only, forcing administrators to either grant excessive privileges or find workarounds.

PostgreSQL 14 (2021) introduced three significant roles that addressed different data access patterns:

  • pg_read_all_data
  • pg_write_all_data
  • pg_database_owner

The first two solved a common backup and analytics challenge. The special case is pg_database_owner which we will cover later.

-- create ETL extraction user
CREATE USER etl_extractor WITH PASSWORD 'extract_password';
GRANT pg_read_all_data TO etl_extractor;

-- create ETL loader with write access
CREATE USER etl_loader WITH PASSWORD 'loader_password';
GRANT pg_write_all_data TO etl_loader;

One big caveat that comes with those ALL data roles is that they still (despite what the name might suggest) respect Row Level Security policies (RLS). This is design choice of PostgreSQL which prioritises security by default - even for users with broad data access. The only way to avoid RLS is to explicitly grant BYPASSRLS role attribute.

PostgreSQL 15 (2022) expanded set with pg_checkpoint, reflecting the reality that checkpoint operations are sometimes needed for maintenance but don't require full superuser privileges.

PostgreSQL 16 (2023) expanded the list by introduction of

  • pg_use_reserved_connections - resolving a critical problem with high-traffic databases, where previously only superusers could access reserved connections. Introduction of this roles expanded the pool of potential users who might be able to resolve incidents or high-load situations. For example with pg_signal_backend grant.
  • pg_create_subscription - with ability to create Logical Replication subscriptions and confirming the place of PostgreSQL in the distributed database scenarios.

PostgreSQL 17 (2024) finally saw pg_maintain, role that had a turbulent development history (initially committed for PostgreSQL 16), allowing non-superusers to perform crucial maintenance tasks like VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE.

The pg_maintain role is particularly valuable for:

  • Automated maintenance scripts that can now run with minimal privileges, reducing the security footprint of scheduled maintenance operations.
  • Operational teams managing multiple databases who need consistent maintenance capabilities without requiring superuser access to each database.
  • Cloud and managed environments where maintenance operations need to be delegated to operations staff without granting broader system access.

PostgreSQL 18 (2025) continues the trend with pg_signal_autovacuum_worker expanding the ability for non-superusers to signal autovacuum workers specifically, enabling them to cancel vacuum operations on specific tables or terminate autovacuum sessions that may be causing performance issues during critical periods.

The Magic of pg_database_owner

If you paid attention, there's one role that stands apart from all others - pg_database_owner. While it does not provide any specific privileges that wouldn't be possible without superuser access, it serves as a marker for the owner of the database. This role is crucial for maintaining database ownership and ensuring that the database is managed by the correct user.

Before PostgreSQL 15 you would have public schema owned by postgres user.

demo=> \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres

It came with the baggage of relaxed default permission, allowing every user (PUBLIC) to create objects. Making this a maintenance nightmare.

Starting with PostgreSQL 15 and above, the public schema is owned by pg_database_owner role.

demo=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner

Unlike any other roles, pg_database_owner has a unique behaviour - it membership changes with the current database. As the code snippet above shows the "shape-shifting nature" of this role fixed the public schema and complexity of the permissions associated with it.

Another special behaviour is that the role itself does not come with any permissions. Let that sink in - zero permissions. Its power only comes from what you grant it or inherit. It opens up system for elaborate template inheritance.

-- define functionality in template1
\c template1 postgres
CREATE OR REPLACE FUNCTION db_owner_stats() RETURN ...  SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION db_owner_stats() TO pg_database_owner;

-- and let it automatically apply to all new databases
CREATE DATABASE app_prod OWNER first_app;
CREATE DATABASE demo_prod OWNER second_app;

-- both first_app and second_app automatically inherit db_owner_stats function on their databases

And when we are talking about magic, the special status of the role prevents this role to be granted to any other role.

demo=# GRANT pg_database_owner TO labs_app;
ERROR:  role "pg_database_owner" cannot have explicit members

Conclusion

Predefined roles transform PostgreSQL administration from ad-hoc permission management into systematic capability delegation. They solve the fundamental problem of needing operational access without operational trust.

The evolution continues - each PostgreSQL release adds new predefined roles addressing real-world operational challenges. The pattern is established: identify common pain points, create focused roles, eliminate the need for superuser privileges in routine operations.

The next time you reach for superuser access to solve an operational problem, check if PostgreSQL has already provided a predefined role for exactly that purpose. You'll likely find it already has one.