5

PostgreSQL 14 Predefined Roles - Making Access Control Easy - Percona Database P...

 2 years ago
source link: https://www.percona.com/blog/postgresql-14-predefined-roles-making-access-control-easy/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Percona Database Performance Blog

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:

Shell
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. 

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

Transact-SQL
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:

Transact-SQL
​​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:

Shell
Pg13-database1 postgres =# grant usage on schema schema1 to appuser;
GRANT

Now try to select from the table:

Transact-SQL
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:

Transact-SQL
pg13-database1 user1 => grant select on schema1.table1 to appuser;
GRANT
pg13-database1 appuser => select * from schema1.table1 limit 5;
 id | data
----+------
(5 rows)

Now try the other table in the schema:

Transact-SQL
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:

Transact-SQL
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
----+------
(5 rows)

Worked!

What about somebody finds that the model needs a new table (table3), look at the next:

Transact-SQL
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?:

Transact-SQL
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:

Transact-SQL
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:

Transact-SQL
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
----+------
(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.

Transact-SQL
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:

Transact-SQL
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

Transact-SQL
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:

Transact-SQL
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:

Transact-SQL
pg14-database1 postgres =# grant pg_read_all_data to appuser ;
GRANT ROLE
Transact-SQL
pg14-database1 appuser => select * from schema1.table1 limit 5 ;
 id | data
----+------
(5 rows)

WORKED!

Even for the other schema:

Transact-SQL
pg14-database1 appuser => select * from schema2.table1 limit 5 ;
 id | data
----+------
(5 rows)

Now let’s add a new table:

Transact-SQL
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:

Transact-SQL
pg14-database1 appuser => select * from schema1.table3 limit 5;
 id | data
----+------
(10 rows)

WORKED!

What about the other database:

Transact-SQL
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
----+------
(5 rows)

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.

Transact-SQL
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!


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK