Redshift RLS. How to make RLS only for a specific user without affecting others?

Question

Hi there!
It was necessary for one user to restrict access to data in a table at the row level. I created an RLS policy and attached it to a role that is defined for only one user. 
`CREATE RLS POLICY rls_name
WITH (id integer) AS ev
USING (
    ev.id IN (1,2,3,4....)
);
ATTACH RLS POLICY rls_name ON schema.table_name TO ROLE role_name;
ALTER TABLE schema.table_name ROW LEVEL SECURITY ON;
`
But other users with another roles for whom I did not attached a policy no longer see the data in the table (SELECT is executed, but does not return data).
I found a workaround, adding them a role with IGNORE RLS, but perhaps there is another way. Is it possible to make RLS only for a specific user without affecting others?

Accepted Answer

Once a RLS policy is attached to a table then all access to the table goes through the policy. So you will need to have a restrictive policy for the user you want to restrict, and another permissive policy for everyone else so other can access the table.

`IGNORE RLS` is one way to do this and it will work as long as you do not need to actually apply any other RLS policy to those users.

Better might be to create a separate permissive policy as shown in [RLS blog](https://aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/) example policy `all_can_see` and use that instead.

Answer

To achieve this, you can utilize a combination of roles and RLS policies. Here's how you can do it:

Create a Role for the User:
 First, create a role specifically for the user who needs restricted access to the data.

sql

CREATE ROLE restricted_user_role;

Create RLS Policy for the User: 
Define an RLS policy that restricts data access based on the specific criteria for the user.

sql

CREATE ROW LEVEL SECURITY POLICY rls_name 
ON schema.table_name 
FOR SELECT 
USING (id IN (1, 2, 3, 4));

Attach the RLS Policy to the User's Role: 
Attach the RLS policy to the role created for the user.

sql

ALTER TABLE schema.table_name 
ENABLE ROW LEVEL SECURITY;

ALTER TABLE schema.table_name 
FORCE ROW LEVEL SECURITY;

ALTER TABLE schema.table_name 
ENABLE ROW LEVEL SECURITY;

Grant the Role to the User:
 Grant the role containing the RLS policy to the specific user.

sql

GRANT restricted_user_role TO specific_user;

Grant Additional Access to Other Users: 
Grant access to the table for other users by assigning them roles that do not have the RLS policy attached.

sql

CREATE ROLE unrestricted_user_role;
GRANT unrestricted_user_role TO other_user;

Ensure Other Users Have Sufficient Privileges: 
Make sure that other users have sufficient privileges to access the table. You might need to grant SELECT privileges directly to them or through appropriate roles.

sql

GRANT SELECT ON schema.table_name TO unrestricted_user_role;

Redshift RLS. How to make RLS only for a specific user without affecting others?

Relevant content