How to enable row level security in Amazon Redshift

Where can we use row-level security?

Suppose you have a report.orders table in Redshift.

Your organization has different teams of data analysts who are responsible for different countries. From a data security perspective, it is wise to grant access to country-specific data to a team. But all orders are stored in the same table, and creating new tables or materialized views would require significant effort.

Row Level Security is able to assist you here. By enabling row-level security on tables/schema, you can provide granular access to your records. You can define a row-level policy with a condition and assign it to a user or role. After that, users will only be permitted to view records that meet the policy condition.

How to enable row-level security on a Redshift table?

Create Table

For this example, We will create a orders table in Redshift.

CREATE TABLE report.orders (
  order_id integer not null,
  customer_id integer not null,
  country varchar(256) not null
) DISTKEY(country) COMPOUND SORTKEY(country, customer_id);

Insert some records

INSERT INTO
  report.orders
VALUES
  (1, 400, 'India'),
  (2, 555, 'US'),
  (3, 555, 'US'),
  (4, 555, 'US'),
  (5, 300, 'Canada'),
  (6, 300, 'Canada'),
  (7, 300, 'Canada'),
  (8, 300, 'India'),
  (9, 300, 'India');

Create new role

This step is optional. If you already have an existing role you can use it.

CREATE ROLE team_india;

Grant SELECT permission on table for our role

Let’s grant SELECT permission to the table for the role. We won’t grant full access to records, as we will only allow reading the records based on custom conditions in the next steps.

GRANT SELECT ON report.orders TO ROLE team_india;

Create row-level security policy

Use CREATE RLS POLICY command to create new policy. Make sure the data type for column in WITH clause is same as the table’s column. The record access is determined by the condition specified in USING clause.

CREATE RLS POLICY team_india_rls_policy
WITH (country VARCHAR(256))
USING (country = 'India');

Attach row-level security (RLS) policy to our role and the table

ATTACH RLS POLICY team_india_rls_policy ON report.orders TO ROLE team_india;

Enable row-level policy on our table

Here, we are enabling row-level security for our table. The CONJUNCTION TYPE can take two values: OR or AND. The conjuction type helps you combine multiple row-level security policies.

ALTER TABLE report.orders ROW LEVEL SECURITY ON CONJUNCTION TYPE OR;

Grant the role to the user

Grant the newly created role to the login user.

GRANT ROLE team_india TO indian_dev;

Test

Login using indian_dev, and try querying on the table.

SELECT * FROM report.orders;

>
| order_id | customer_id | country |
|----------|-------------|---------|
| 1        | 400         | India   |
| 8        | 300         | India   |
| 9        | 300         | India   |

It will return only the records with the country set to ‘India’.