- Más nuevo
- Más votos
- Más comentarios
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.
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,
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 -
- Create the external user and grant the external role: (External role created in example 1)
- Grant SELECT on the customer table to role external
- Turn on row-level security for the report.customer table:
- 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.
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 -
- Create the external user and grant the external role: (External role created in example 1)
- Grant SELECT on the customer table to role external
- Turn on row-level security for the report.customer table:
- 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.
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!
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,
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
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 -
- Create the external user and grant the external role: (External role created in example 1)
- Grant SELECT on the customer table to role external
- Turn on row-level security for the report.customer table:
- 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.
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,
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!
Contenido relevante
- OFICIAL DE AWSActualizada hace 2 años
- OFICIAL DE AWSActualizada hace un año
- OFICIAL DE AWSActualizada hace 2 años
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