The PostgreSQL community always adds new features in its release every year. One of the prominent features is the Predefined Role which was introduced in PostgreSQL 9.6 with a single role available. Now it is having more than 10 predefined roles.
A predefined role is a built-in role that provided access to commonly needed information. This helps the database engineers and administrators to provide access to others in a very easy manner rather than having to execute numerous SQL queries to provide the same grants.
Example: Let’s say we need to provide complete read access to the app_user for database production with schemas X, Y, and Z.
Without predefined roles, we need to execute the following SQLs.
grant select on all tables in schema X to app_user;
grant select on all tables in schema Y to app_user;
grant select on all tables in schema Z to app_user;
With the predefined role, the number of SQL executed is reduced to 1.
grant pg_read_all_data to app_user;
So, in this blog post, we will have a detailed overview of available predefined roles in PostgreSQL since 9.6.
pg_signal_backend
Earlier only superusers were allowed to kill another session. If someone needs to kill any query, they should be granted with superuser role. This predefined role allows the non-superuser to signal another backend to cancel a query(pg_cancel_backend) or terminate its session(pg_terminate_backend) without being a superuser.
However, the non-superuser with this role granted cannot send signals to the backend owned by a superuser. This role is available from PostgreSQL 9.6
Session 1: Non-Superuser is trying to kill a session and access is denied
production=# set role to app_user;
SET
production=> select pg_terminate_backend(5234);
ERROR: must be a superuser to terminate superuser process
production=>
Session 2: Providing predefined role
postgres=# grant pg_signal_backend to app_user;
GRANT ROLE
postgres=#
Session 1: Now, non-superuser is able to terminate the session
production=> \c
You are now connected to database "production" as user "postgres".
production=# set role to app_user;
SET
production=# select pg_terminate_backend(5234);
pg_terminate_backend
----------------------
t
(1 row)
Trying to kill superuser connection, still could not do it.
production=> select pg_terminate_backend(5247);
ERROR: must be a superuser to terminate superuser process
production=>
pg_read_all_stats
It allows the non-superuser to read all the pg_stat_* views.
Session 1: Unable to view other sessions as it is showing insufficient privilege
postgres=> select pid,query from pg_stat_activity;
pid | query
------+-----------------------------------------
3485 |
3484 |
5283 |
5291 | select pid,query from pg_stat_activity;
5241 |
3481 |
3480 |
3483 |
(8 rows)
Session 2: Granted with read all stats access
production=# grant pg_read_all_stats to app_user ;
GRANT ROLE
production=#
Session 1: Now able to see other user stats without being a superuser
postgres=> \c
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "postgres" as user "app_user".
postgres=> select pid,query from pg_stat_activity;
pid | query
------+-----------------------------------------
3485 |
3484 |
5283 | select pg_reload_conf();
5317 | select pid,query from pg_stat_activity;
5315 | grant pg_read_all_stats to app_user ;
3481 |
3480 |
3483 |
(8 rows)
pg_read_all_settings
It allows the non-superuser to read all the configuration variables which are normally accessible only by the superuser.
pg_stat_scan_tables
It allows executing monitoring functions that may take access share lock on tables.
pg_monitor
It allows to read/execute various monitoring views and functions. This role is also a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables
production=# \du pg_monitor
List of roles
-[ RECORD 1 ]------------------------------------------------------------
Role name | pg_monitor
Attributes | Cannot login
Member of | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
production=#
All these roles pg_monitor, pg_read_all_settings, pg_stat_scan_tables, pg_read_all_stats are available from PostgreSQL 10. This allows the database engineers/administrators to configure their monitoring system just by using a non-superuser. It is just enough to create a monitoring(non-superuser) user and provide pg_monitor grant to it rather than creating a superuser which is also a serious security concern.
pg_read_server_files
It allows the user to import data into the database using commands like copy without being a superuser.
Session 1: Unable to import data
production=> copy test from '/var/lib/postgresql/load.csv' WITH (FORMAT csv);
ERROR: must be superuser or have privileges of the pg_read_server_files role to COPY from a file
HINT: Anyone can COPY to stdout or from stdin. psql copy command also works for anyone.
production=>
Session 2: Granted the required predefined role
production=# grant pg_read_server_files to app_user;
GRANT ROLE
production=#
Session 1: Able to import data without superuser access
production=> \c
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "production" as user "app_user".
production=> copy test from '/var/lib/postgresql/load.csv' WITH (FORMAT csv);
COPY 1
production=>
pg_write_server_files
It allows the user to export data from the database using commands like copy.
Session 1:
production=> copy test to '/var/lib/postgresql/export.csv' DELIMITER ',' CSV HEADER;
ERROR: must be superuser or have privileges of the pg_write_server_files role to COPY to a file
HINT: Anyone can COPY to stdout or from stdin. psql copy command also works for anyone.
production=>
production=>
Session 2:
production=# grant pg_write_server_files to app_user;
GRANT ROLE
production=#
Session 1:
production=> \c
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "production" as user "app_user".
production=> copy test to '/var/lib/postgresql/export.csv' DELIMITER ',' CSV HEADER;
COPY 2
production=>
pg_execute_server_program
Allow to execute programs on the database server.
pg_read_server_files, pg_write_server_files, and pg_execute_server_program allow the non-superuser to import, export, and execute programs on the database server. So, it is very important to be careful while providing these access roles. All these roles are available from PostgreSQL 11.
pg_read_all_data
This provides the user access to read(Select) all tables, views, sequences on all schemas in a database without providing it explicitly. It is similar to having select access on all these objects for all the schemas and usage access for all schemas in a database.
Without using Predefined Role
Session 1: Creating a new table t1 in schema test
production=# create table test.t1 (id int);
CREATE TABLE
production=#
Session 2: Trying to read the table t1 and getting access denied for the schema
production=> select * from test.t1;
ERROR: permission denied for schema test
LINE 1: select * from test.t1;
^
Session 1: Granting the access to the schema
production=# grant usage on schema test to app_user;
GRANT
production=#
Session 2: Getting the access denied for the table
production=> select * from test.t1;
ERROR: permission denied for table t1
production=>
Session 1: Granting the access to the table
production=# grant select on test.t1 to app_user;
GRANT
production=#
Session 2: Able to perform read on t1.
production=> select * from test.t1;
id
----
(0 rows)
Session 1: Creating a new table t2 on another schema test2
production=# create table test2.t2 (id int);
CREATE TABLE
production=#
Session 2: Again getting access denied for the table
production=> select * from test2.t2;
ERROR: permission denied for table t2
production=>
Using Predefined Role
Session 1: Creating a new schema new and a table t1
production=# create schema new;
CREATE SCHEMA
production=# create table new.t1 (id int);
CREATE TABLE
production=#
Session 2: Getting access denied to the schema
production=> select * from new.t1;
ERROR: permission denied for schema new
LINE 1: select * from new.t1;
^
production=>
Session 1: Providing predefined role to the user
production=# grant pg_read_all_data to app_user ;
GRANT ROLE
production=#
Session 2: Able to read from the table
production=> select * from new.t1;
id
----
(0 rows)
production=>
Session 1: Creating a new table
production=# create table new.t2 (id int);
CREATE TABLE
production=#
Session 2: Without any explicit grant, able to read from the new table as well.
production=> select * from new.t2;
id
----
(0 rows)
production=>
The number of grant SQLs is reduced to a single line and it also provide the same access to the new object being created inside the database. However, it is only suited if providing read access to all the objects in the database. If wanted to provide granular access, this cannot be suited.
pg_write_all_data
This provide the user access to write all data on tables, views and sequences without explicitly provided insert, updates, deletes on all these objects. This is similar to having insert,update,delete access and usage access on all schemas in a database.
pg_database_owner
This allows easy creation of objects that will be available only to the db owners and not to the other users without explicit permission. Mostly helpful in template1 database.
All these roles pg_read_all_data, pg_write_all_data, pg_database_owner are available from PostgreSQL 14.
pg_checkpoint
It allows the non-superuser to perform checkpoint operation by executing checkpoint command. By default, only superusers are able to perform this task.
This role is available from PostgreSQL 15.
pg_maintain
It allows the user to perform maintenance activities like VACUUM, ANALYZE, REINDEX, Refresh Materialized View, cluster and lock table. By default, superuser and table owner only able to perform these operations. This will be really helpful to perform maintenance activities using least privileged user.
pg_use_reserved_connections
This allows to reserve connections for non-superusers. To support this, a new parameter reserved_connections will be added in PostgreSQL 16. Currently only superusers can reserve connections based on the variable superuser_reserved_connections.
Both these roles pg_maintain and pg_use_reserved_connections will be available from PostgreSQL 16 and it may be expected to release by October of this year 2023.
Summary of predefined roles available:
This feature in PostgreSQL helps to do the jobs related with access part much easy and quickly than ever especially for the databases which contains numerous users and schemas. Also, it is very important to be careful while provide the access and its purpose.
.png)
.png)
.svg)
