Grant All Privileges Postgres on Database

How to Handle Privileges in PostgreSQL

I used to downplay the importance of handling privileges for internal tools or at least misunderstand the intention of it. The use case I will talk about here is a database cluster where the users are the researchers in a research group. Since it is a small group, essentially everyone has the right to read and modify the data. The problems are exposed when I am asked to migrate the cluster to a new server and do some data cleaning before that. It turns out

• most databases are poorly documented and people have a hard time figuring it out the semantics of some old databases. This problem can be partially solved by tools like schemaspy and PostgreSQL's own COMMENT.
• some fields use types like varchar(n) , but store only numeric values — I found that by doing pattern matching queries.
• a database has multiple schemas named as xyz-[year], with duplicated data across schemas. From the first bullet point, you can see I have no idea whether such design is intentional or simply a mistake.

So you see, eve n if nobody has the intention to corrupt the data in any way, because of lack of privilege management and clear guidelines to use the databases, people can easily turn the database cluster into a mess and maintaining the data becomes extremely difficult. The issues are greatly due to the fact that researchers in academia work very independently, and their primary concern is doing their own research project. Ensuring everyone to make the right moves in the database cluster is practically impossible. We decided to constrain users' privileges in the new server, and the database admin has to be informed when creation, deletion, and update happen.

To better demonstrate our solution with code, assume for now we have only five people in the group. postgres would be the admin user. user1 and user2 are responsible for the database demo12. user3 and user4 for demo34. We want to only allow people to write on their responsible databases under permission of the database admin. Everyone can still read all the data. To sum up, we want the granularity for write access at the database level and for read access at the cluster level.

Side note: be careful of mixing up the concepts of "database" in PostgreSQL and MySQL. Roughly speaking, we have table < schema < database < cluster in PostgreSQL. The "<" should be read as "is contained in".

There is no easy built-in solution in PostgreSQL. If you read the documentation of GRANT, it sounds like the following code should solve half of our problem already

          GRANT CREATE, CONNECT, TEMPORARY ON DATABASE demo12 TO user1, user2;
GRANT CREATE, CONNECT, TEMPORARY ON DATABASE demo34 TO user3, user4;        

Unfortunately, this is not the case. This StackOverflow post illustrates the burdensome work we have to handle.

However, granting corresponding privileges to user1/2/3/4 is not a scalable solution. For one there are actually more than five people in the group and there are more than two databases in our cluster. Besides, new people may come to the group and current researchers will leave at some point. Doing bookkeeping for every user's privilege is not a very clean solution.

We are gonna make use of PostgreSQL's ROLE and USER combined to build a simple hierarchy that looks like this:

Dotted circles represent roles and solid circles represent our users. Rounded squares are our databases. Meanings of the arrows are explained by the demo below.

ROLE and USER in PostgreSQL can cause great confusion. But from the version 10 I am working with and onwards, the documentation clearly says

CREATE USER is now an alias for CREATE ROLE . The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

…The SQL standard leaves the definition of users to the implementation.

Many people use ROLE as a "group" role that contains multiple USER s. We will think in this way here as well.

By the way, if you wonder why handling the privileges for PostgreSQL is worth writing an article, here shows one of the reasons

Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

So we want the write privileges at the database level, but the roles are defined at the cluster level. Even worse, the GRANT (and REVOKE) should be invoked…I guess at every level in our case. Although these concepts are supposed to work together nicely to help manage users in the system, they seem to be designed by many people with different design ideas in mind.

Enough from my complaints. Let's sit tight for the demo.

Setup

We first create some roles and users that should exist in our system.

          $ psql -U postgres          postgres=# CREATE ROLE readonly;          postgres=# CREATE ROLE rw_demo12;          postgres=# CREATE ROLE rw_demo34;          postgres=# CREATE USER user1 ENCRYPTED PASSWORD 'user1';          postgres=# CREATE USER user2 ENCRYPTED PASSWORD 'user2';          postgres=# CREATE USER user3 ENCRYPTED PASSWORD 'user3';          postgres=# CREATE USER user4 ENCRYPTED PASSWORD 'user4';          postgres=# GRANT readonly TO user1, user2, user3, user4;          postgres=# GRANT rw_demo12 TO user1, user2;          postgres=# GRANT rw_demo34 TO user3, user4;        

I have beforehand injected some dummy data into both databases. Here shows what we have in our databases.

          demo12(demo34)=# \dn          List of schemas          Name | Owner          ----------+----------          public | postgres          (3 rows)          demo12(demo34)=# \d          List of relations          Schema |     Name     | Type  |  Owner          --------+--------------+-------+----------          public | departments  | table | postgres          public | dept_emp     | table | postgres          public | dept_manager | table | postgres          public | employees    | table | postgres          public | salaries     | table | postgres          public | titles       | table | postgres          (6 rows)        

Configure "readonly"

We will now grant readonly the privileges to read all the databases in the cluster. In the outer FOR loop, we loop through each database we want to set privileges for. In the inner FOR loop, we loop through all schemas in each database.

If we have more than databases demo12 and demo34 , and we want to configure the readonly role for all databases, we can use

          DATABASE_NAMES=$(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname <> 'postgres';")        

to get a list of all databases in our cluster. Then simply replace for DBNAME in "demo12" "demo34" with for DBNAME in $DATBASE_NAMES .

You might be wondering the difference between GRANT and ALTER DEFAULT PRIVILEGES and why the former needs to be placed in a loop while the latter doesn't. Briefly speaking, GRANT changes the privileges for CURRENT objects in a particular schema in a database, while ALTER DEFAULT PRIVILEGES changes the privileges for FUTURE objects in a database. Yes, when we do not specify any particular schema in ALTER DEFAULT PRIVILEGES, the changes apply to the whole database. So one more command out of sync: ALTER DEFAULT PRIVILEGES can be invoked for the database level, but GRANT cannot.

Did you notice the FOR ROLE postgres part in ALTER DEFAULT PRIVILEGES commands? Keep an eye out and see how it can trick us in a minute.

Configure "rw_demo12" and "rw_demo12"

Let's see what would happen with the following code.

          $ psql -d demo12 -U user1 -W          demo12=> create schema foo;          CREATE SCHEMA          demo12=> create table foo.bar(col1 int);          CREATE TABLE          demo12=> insert into foo.bar values(1);          INSERT 0 1          demo12=> \c demo12 user3          You are now connected to database "demo12" as user "user3".          demo12=> select * from foo.bar;          ERROR:  permission denied for schema foo          LINE 1: select * from foo.bar;          demo12=> \dn          List of schemas          Name  |  Owner          --------+----------          foo    | user1          public | postgres          (2 rows)        

We log in as user1 , create a schema called foo in demo12 and a table in it. Now when user3 connects to the demo12 and tries to read the data, it shows that user3 has no permission. How can that be? Isn't user3 a member of readonly that has access to read all databases?

The reason is shown in the Owner column. The owner of foo is user1 . When we do ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO readonly , we only grant the privileges for the future objects owned by postgres. Therefore, readonly and its members cannot select on the tables owned by user1 .

I know I just said we want the responsible people to be able to write to the database, but the above example shows it can result in blocking other users reading the data. Hence, a simple (yet very strict) fix is to enforce postgres to own all objects. This is not really bad for our case, because create and delete happen far less often than updating the data.

The configuration for rw_demo12 and rw_demo34 would be

Unfortunately, this does not stop users with connection permission to create new tables in the schema public (and hence own them). To avoid this, we need to additionally execute REVOKE ALL ON SCHEMA public FROM public for all databases. Just put this in the outer loop, and we will have the complete scripts for the figuration.

Test

If we want to remove user1 from the rw_demo12 group, we only need to do

          REVOKE rw_demo FROM user1;        

Similarly, if we want to add a newuser for database rw_demo12 , just grant it to the group

          GRANT rw_demo TO newuser;        

What if we want to reject all updates to demo12 , and the members in the role rw_demo12 are far more than user1 and user2 ? I didn't find any existing queries that can retrieve such information. I think it's because PostgreSQL maps each user to all the group roles they belong to, but not the other way around. So we need to revoke the privileges we previously granted to rw_demo12, and the script will be just as burdensome as before. But hey, we have some consistency this time😃

Alternative setup

If you want a more flexible solution, i.e. allowing users other than the admin to own objects, we can hack it with NOINHERIT . The reason why we landed this strict implementation is we don't want to list all users in a command like ALTER DEFAULT PRIVILEGES FOR ROLE postgres, user1,user2,user3,... GRANT SELECT ON TABLES TO readonly . But if we can ensure that owners for demo1 objects would be only postgres and rw_demo12 , it would be easier to manage. We use a different setup for users with NOINHERIT.

          $ psql -U postgres          postgres=# DROP USER user1, user2, user3, user4;          postgres=# CREATE USER user1 NOINHERIT ENCRYPTED PASSWORD 'user1';          postgres=# CREATE USER user2 NOINHERIT ENCRYPTED PASSWORD 'user2';          postgres=# CREATE USER user3 NOINHERIT ENCRYPTED PASSWORD 'user3';          postgres=# CREATE USER user4 NOINHERIT ENCRYPTED PASSWORD 'user4';          postgres=# GRANT readonly TO user1, user2, user3, user4;          postgres=# GRANT rw_demo12 TO user1, user2;          postgres=# GRANT rw_demo34 TO user3, user4;        

Now user1 can only connect to the cluster, because it does not inherit the privileges of rw_demo12 automatically. In order to view the data, user1 has to explicitly do SET ROLE TO readonly . Likewise, if user1 wants to insert values into certain tables, or CREATE NEW TABLES (which is banned in our proposed solution), user1 needs to SET ROLE TO rw_demo12 . In this way, all new future objects created by user1 or user2 will be owned by rw_demo12 . So you see, this alternative solution is more flexible, but with the sacrifice of user experience.

Grant All Privileges Postgres on Database

Source: https://towardsdatascience.com/how-to-handle-privileges-in-postgresql-with-specific-use-case-and-code-458fbdb67a73

Share this post