Redshift Row level access control security (RLS)

0

Hello, I'm running RLS to see how it works (aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/) I am successfully able to run Example 1 in the documentation but when I run example 2; it won't return any results. Example 2 uses session context variables. I'm able to create a user (external_user), grant role external to the user. Create and attach policy (see_only_own_customer_rows) to customer table and role external. I also login with the external_user and set the set_config variable with value as below- select set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE); After running the select * query below I don't get any results. select * from report.customer

Any ideas what could be the issue? Looks like everything is running fine but don't get the results on select * Any help would be really appreciated

7 Answers
0

Hi,

Before executing the select * from report.consumer, does the select current_user () returns external_user?

If you run the select * query using the admin user, does it return any rows?

Thanks.

Ziad
answered 2 months ago
  • Yes to both questions - Select * current_user returns returns external_user when I login as external_user. When I login as admin for query - select * from report.consumer I get all rows as result

0

This could happen if a table has RLS ON and the RLS policy is not attached to the user's role.

Was the policy attached to the user's role on the customer table through the following command?

ATTACH RLS POLICY see_only_own_customer_rows ON report.customer TO ROLE EXTERNAL;

Thanks,

Ziad
answered 2 months ago
EXPERT
reviewed 2 months ago
  • Yes I have attached the policy to user's role on customer table. I exactly followed the example 2 from blog - aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/ Below are the steps I took as admin -

    1. Create the external user and grant the external role: (External role created in example 1)
    2. Grant SELECT on the customer table to role external
    3. Turn on row-level security for the report.customer table:
    4. Create a row-level security policy using the session context variable app.customer_id to enforce the policy to filter records for c_customer_id Then I login as user and run the commands below: select set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE); select * from report.customer limit 10;

    select * does not return any rows.

0

Yes I have attached the policy to user's role on customer table. I exactly followed the example 2 from blog - aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/ Below are the steps I took as admin -

  1. Create the external user and grant the external role: (External role created in example 1)
  2. Grant SELECT on the customer table to role external
  3. Turn on row-level security for the report.customer table:
  4. Create a row-level security policy using the session context variable app.customer_id to enforce the policy to filter records for c_customer_id Then I login as user and run the commands below: select set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE); select * from report.customer limit 10;

select * does not return any rows.

answered 2 months ago
  • Now it works. Here's how it works - I was using Query editor on AWS console to test RLS example 2 and everything was running fine. Only problem was ---- Query below would run without returning any rows. Select * from report.customer limit 10

    Today I setup DBeaver connection and dropped all users and roles and run all the commands again and finally see the results to the query above So to summarize it does not work using Query editor but works by setting JDBC connection on DBeaver

    Thanks!

0

Hi,

Thank you for the update. I really appreciate it. Honestly, I have always used Query Editor V2 to test RLS and it worked. So, it should work for you. If you're still facing the same issue with Query Editor V2, I would suggest that you create a ticket for the AWS support to identify the root cause.

Many thanks,

Ziad
answered 2 months ago
  • Thanks for the answer. I use Query Editor. I cannot user Query editor V2 because it's not enabled for us in the test VPC, provisioned Redshift cluster I will create a ticket with AWS support

0

Yes I have attached the policy to user's role on customer table. I exactly followed the example 2 from blog - aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/ Below are the steps I took as admin -

  1. Create the external user and grant the external role: (External role created in example 1)
  2. Grant SELECT on the customer table to role external
  3. Turn on row-level security for the report.customer table:
  4. Create a row-level security policy using the session context variable app.customer_id to enforce the policy to filter records for c_customer_id Then I login as user and run the commands below: select set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE); select * from report.customer limit 10;

select * does not return any rows.

answered 2 months ago
0

Hi,

That's strange. Could you try to create/load a new table report.customer_2 (same ddl as customer) and execute the following in order? Does it return any results?

CREATE USER external_user_2 PASSWORD 'Testemp1';
CREATE ROLE external_2;
grant role external_2 to external_user_2;

grant usage on schema report to role external_2;
GRANT select ON TABLE report.customer_2 TO ROLE external_2;

ALTER TABLE report.customer_2 row level security on;

CREATE RLS POLICY see_only_own_customer_rows_2
WITH ( c_customer_id char(16) )
USING ( c_customer_id = current_setting('app.customer_id', FALSE));

ATTACH RLS POLICY see_only_own_customer_rows_2 ON report.customer_2 TO ROLE external_2;

SET SESSION AUTHORIZATION 'external_user_2';

select set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE);
select * from report.customer_2 limit 10;

Thanks,

Ziad
answered 2 months ago
0

Now it works. Here's how it works - I was using Query editor on AWS console to test RLS example 2 and everything was running fine. Only problem was ---- Query below would run without returning any rows Select * from report.customer limit 10 Today I setup DBeaver connection and dropped all users and roles and run all the commands again and finally see the results to the query above So to summarize it does not work using Query editor but works by setting JDBC connection on Dbeaver

Thanks!

answered 2 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions