What is the difference between Enable/Disable RLS and Grant/Revoke priviliges

0
votes

I have a table created by role A

Firstname|Lastname| Age
------------------------
 Jill    |  Smith |  50 
 Eve     |   Dar  |  94

Say I want to enable Row Level security,

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

this by default denies select/update/delete operations to all roles except the table owner(roleA and superuser)

Inorder to allow roleB to view/modify rows I would need to create a policy

CREATE POLICY view_users ON users TO roleB
    USING (manager = current_user);

That seems to me like GRANT/REVOKE privileges to a table, How is RLS different

1
postgresqlgrantrow-level-security

1 Answers

0
votes

GRANT can only give permission on a complete table. Once you have GRANTed the SELECT privilege to a user (or role) that user can see all rows in the table, and if that user has the UPDATE privilege for the table, he/she can update all rows.

With RLS (as the name implies) you can control access for each row individually.

Given your example, granting SELECT privilege on the table will allow every user to see all rows. With your rule in place a user can only see rows of those employees where he/she is the manager (the rule should probably be extended to include the current user's row as well, not only those where the current user is the manager).

Take the following table:

Firstname | Lastname | manager 
------------------------------
Jill      | Smith    | paul
Eve       | Dar      | paul
Arthur    | Dent     | mary
Ford      | Prefect  | mary

If you grant select and update to the roles mary and paul they can see and change all rows in that table.

But with the RLS policy in your question, the role mary can only see and change Arthur Dent and Ford Prefect. And paul would only be able so see and change Jill Smith and Eve Dar.

Neither of those users would even know that there are more rows in the table.