The PostgreSQL RDBMS has shown rapid growth in terms of adoption and usability for a wide range of industries and projects. The same is true as for the new methodologies for application development and deployment, which currently focuses on making it easier for developers to get the infrastructure and the DB model they need with not too much intervention from other IT teams, so they can jump into their development process as quick as possible.
The PostgreSQL community is very active and always has included new features in every release that cover or improve some necessities for this changing and always evolving IT world.
In this blog post, I’m going to go through a “small” feature just added in the latest PostgreSQL version 14 release. This for sure comes in handy when the time for granting privileges for users in the DB model comes. This is the inclusion of two new predefined roles, which can simplify the privilege management:
- pg_read_all_data
- pg_write_all_data
This new facility to grant access in a wide scope requires to be used with the proper control and awareness, especially when we work in a production environment. With that said, let’s check what is this about.
Users, Roles, and Privileges
When working with a PostgreSQL system, the concepts of Users and Roles may be used indistinguishably from each other, they refer to a cluster-level object that usually represents a database user (User) and/or can act as a logical container for privileges (Role), the syntax is often interchangeable, ie:
1 2 | ALTER ROLE... ALERT USER... |
NOTE: In this blog, we are going to grant the privileges directly to the users for sake of simplicity, however, as a best practice is advisable to set all permissions to a role rather than a user so we can get better control over them.
The privileges are all those permissions an administrator, or object owner, can grant to a role (and thus to a user, or a set of users) and apply to specific objects within a database context. Depending on the object type there are different privileges that can be granted, their usage and effect are not exactly in the scope of this blog post but you can review the official documentation as always.
We are going to review a specific use case to get how the new predefined roles can be used to simplify some of the actions related to the access control.
The Use Case
Let’s imagine we are working in a new application or service that will use our loved PostgreSQL database as backend, this application can be an ETL, a data extraction tool, or even an on-demand microservice, the thing is it requires access practically to all the data in our PG cluster, that means all the user tables (and views) within all the schemas and in all the databases present in the cluster. Think in the next model, two databases, one has two schemas the other only one schema, each schema is owned by a different user, each schema has one or more tables. Our application user is going to be named appuser.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | database database1: schema schema1: owner: user1 table table1: columns: - id: integer - data: text table table2: columns: - id: integer - data: text schema schema2: owner: user2 table table1: columns: - id: integer - data: text database database2: schema schema1: owner: user1 table table1: columns: - id: integer - data: text |
Getting Hands-On
Before trying this with the new predefined roles let’s check how it would be handled in a PostgreSQL 13.
First, create the user
1 2 3 4 | pg13-database1 postgres =# create user appuser; CREATE ROLE pg13-database1 postgres =# alter user appuser password 'secretsuperpasswd'; ALTER ROLE |
If we connect with our new user and try to access the tables on schema1:
1 2 3 4 5 6 7 8 9 | pg13-database1 user1 => c database1 appuser Password for user appuser: psql (14.1, server 13.5 (Debian 13.5-1.pgdg110+1)) You are now connected to database "database1" as user "appuser". pg13-database1 user1 => select * from schema1.table1 limit 5; ERROR: permission denied for schema schema1 LINE 1: select * from schema1.table1 limit 5; ^ |
Ok, grant USAGE on the schema to the user:
1 2 | Pg13-database1 postgres =# grant usage on schema schema1 to appuser; GRANT |
Now try to select from the table:
1 2 | pg13-database1 appuser => select * from schema1.table1 limit 5; ERROR: permission denied for table table1 |
Ok, ok, we need to grant SELECT to the table, got it:
1 2 3 4 5 6 7 8 9 10 11 12 | pg13-database1 user1 => grant select on schema1.table1 to appuser; GRANT pg13-database1 appuser => select * from schema1.table1 limit 5; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) |
Now try the other table in the schema:
1 2 | pg13-database1 appuser => select * from schema1.table2 limit 5; ERROR: permission denied for table table2 |
Mmmh, ok, let’s grant SELECT over all the tables in the schema:
1 2 3 4 5 6 7 8 9 10 11 12 | pg13-database1 user1 => grant select on all tables in schema schema1 to appuser; GRANT pg13-database1 appuser => select * from schema1.table2 limit 5 ; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) |
Worked!
What about somebody finds that the model needs a new table (table3), look at the next:
1 2 3 4 5 6 | pg13-database1 user1 => create table schema1.table3 (like schema1.table1) ; CREATE TABLE pg13-database1 user1 => insert into schema1.table3(id, data) select i,i::text from generate_series(1,1000) i; INSERT 0 1000 |
Now our appuser tries to read from it, we already granted SELECT over all the tables, right?:
1 2 | pg13-database1 appuser => select * from schema1.table3 limit 5; ERROR: permission denied for table table3 |
We have to repeat the previous grants in the new table, just as we did for the table2 table.
To let our user keep the read access even in new tables we have to alter the default privileges at the schema:
1 2 | pg13-database1 user1 => alter default privileges in schema schema1 grant select on tables to appuser; ALTER DEFAULT PRIVILEGES |
Now if a new table is created our user will have access to it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | pg13-database1 user1 => create table schema1.table4 (like schema1.table1) ; CREATE TABLE pg13-database1 user1 => insert into schema1.table4(id, data) select i,i::text from generate_series(1,1000) i; INSERT 0 1000 pg13-database1 appuser => select * from schema1.table4 limit 5 ; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) |
Nice!
But do not forget we have another schema, and the appuser should be able to get the data from there as well.
1 2 | pg13-database1 appuser => select * from schema2.table1 limit 5; ERROR: permission denied for schema schema2 |
OK, we have to apply all the above… again.
And also remember we have a different database in the cluster, so:
1 2 3 4 5 6 | pg13-database1 appuser => c database2 appuser psql (14.1, server 13.5 (Debian 13.5-1.pgdg110+1)) You are now connected to database "database2" as user "appuser". pg13-database2 appuser => select * from schema1.table1 limit 5; ERROR: permission denied for schema schema1 |
We have to do the same for this database’s schemas and tables as well.
Is true we can follow the well-known “code once, execute many” and turn all these steps into a single automation piece, but we still need to take them into consideration since they are needed.
What about the “little” feature in PostgreSQL 14 we want to check around, the new predefined roles are described as follow:
Role | Allowed Access |
pg_read_all_data | Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. |
pg_write_all_data | Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. |
https://www.postgresql.org/docs/14/predefined-roles.html
So, how our previous scenario would work in PostgreSQL 14? let’s check it out.
As before, we start creating our user
1 2 3 4 | pg14-database1 postgres =# create user appuser; CREATE ROLE pg14-database1 postgres =# alter user appuser password 'supersecretpasswd'; ALTER ROLE |
If we try accessing the table right now with our new user, we know what is going to happen:
1 2 3 4 5 6 7 | pg14-database1 user1 => c database1 appuser Password for user appuser: You are now connected to database "database1" as user "appuser". pg14-database1 appuser => select * from schema1.table1 limit 5; ERROR: permission denied for schema schema1 LINE 1: select * from schema1.table1 limit 5 ; |
The magic starts here, we can grant the new predefined role:
1 2 | pg14-database1 postgres =# grant pg_read_all_data to appuser ; GRANT ROLE |
And…
1 2 3 4 5 6 7 8 9 | pg14-database1 appuser => select * from schema1.table1 limit 5 ; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) |
WORKED!
Even for the other schema:
1 2 3 4 5 6 7 8 9 | pg14-database1 appuser => select * from schema2.table1 limit 5 ; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) |
Now let’s add a new table:
1 2 3 4 5 6 | pg14-database1 user1 => create table schema1.table3 (like schema1.table1) ; CREATE TABLE pg14-database1 user1 => insert into schema1.table3(id, data) database1-> select i,i::text from generate_series(1,1000000) i; INSERT 0 1000000 |
And try the SELECT from it:
1 2 3 4 5 6 7 8 9 | pg14-database1 appuser => select * from schema1.table3 limit 5; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (10 rows) |
WORKED!
What about the other database:
1 2 3 4 5 6 7 8 9 10 11 12 | pg14-database1 appuser => c database2 appuser You are now connected to database "database2" as user "appuser". pg14-database2 appuser => select * from schema1.table1 limit 5; id | data ----+------ 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) |
WOW!
As we can see, a single command did the work for the previous multiple commands we required in PG13, cool!.
For the write privileges we should do almost the same:
- For PG13 grant the UPDATE, INSERT, and (acting with caution) the DELETE instead of SELECT.
- In PG14 grant the pg_write_all_data role.
NOTE: Granting only the privileges for DML directly or using the new PG14 predefined role, without the read part the user won’t be able to perform UPDATES or DELETES over subsets, due to it requires the filtering, and that only is viable if the read access is in place.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | pg14-database1 write_appuser => dg write_appuser List of roles Role name | Attributes | Member of ----------------+------------+--------------------- write_appuser | | {pg_write_all_data} pg14-database1 write_appuser => insert into schema1.table1(id, data) values(2000000, 'database1.schema1.table1-2000000'); INSERT 0 1 pg14-database1 write_appuser => update schema1.table1 set data = 'database1.schema1.table1-2000000-upd' where id = 2000000 ; ERROR: permission denied for table table1 pg14-database1 write_appuser => delete from schema1.table1 where id = 2000000 ; ERROR: permission denied for table table1 pg14-database1 write_appuser => update schema1.table1 set data = ''; UPDATE 1000001 pg14-database1 write_appuser => delete from schema1.table1; DELETE 1000001 |
This feature might help us to get things done quickly, especially if we are working in large environments. However, we always need to be sure to who we are giving access to and how it will be used.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
