Just a clear cut post on how to do postgres permissions

https://www.waitingforcode.com/postgresql/alter-default-privileges-postgresql/read

My take away is towards the end

In the above example you can see that user_a explicitly grants SELECT privileges on all the tables and views that will be created to the user_b. That’s why user_b is able to issue SELECT query without any extra GRANT execution. However, the code from previous snippet has a small trap. To see it, let’s create new tables as user_c and try to query them as user_b:

test_alter_default=> SET SESSION AUTHORIZATION 'user_c';
SET
test_alter_default=> CREATE TABLE test_permissions.user_c_table_1 (id INT);
CREATE TABLE
test_alter_default=> SET SESSION AUTHORIZATION 'user_b';
SET
test_alter_default=> SELECT * FROM test_permissions.user_c_table_1;
ERROR:  permission denied for table user_c_table_1

Wait! You didn’t say that user_b can access everything in the schema? Indeed but I didn’t mention that ALTER DEFAULT PRIVILEGES applies only to the objects created by the user executing this query! So when user_a executed it, it allowed user_b to see all tables and views he’ll create in the future. To solve that issue, we can add FOR USER in our alter command to say that this applies for all objects of a particular user:

test_alter_default=> SET SESSION AUTHORIZATION 'root';
SET
test_alter_default=# ALTER DEFAULT PRIVILEGES FOR USER user_c IN SCHEMA test_permissions GRANT SELECT ON TABLES TO user_b;
ALTER DEFAULT PRIVILEGES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s