Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# Row-level security
<a name="t_rls"></a>

Using row-level security (RLS) in Amazon Redshift, you can have granular access control over your sensitive data. You can decide which users or roles can access specific records of data within schemas or tables, based on security policies that are defined at the database objects level. In addition to column-level security, where you can grant users permissions to a subset of columns, use RLS policies to further restrict access to particular rows of the visible columns. For more information about column-level security, see [Usage notes for column-level access control](r_GRANT-usage-notes.md#r_GRANT-usage-notes-clp).

When you enforce RLS policies on tables, you can restrict returned result sets when users run queries.

When creating RLS policies, you can specify expressions that dictate whether Amazon Redshift returns any existing rows in a table in a query. By creating RLS policies to limit access, you don't have to add or externalize additional conditions in your queries. 

When creating RLS policies, we recommend that you create simple policies and avoid complex statements in policies. When defining RLS policies, don't use excessive table joins in the policy definition that are based on policies.

When a policy refers to a lookup table, Amazon Redshift scans the additional table, in addition to the table on which the policy exists. There will be performance differences between the same query for a user with an RLS policy attached, and a user without any policy attached.

# Using RLS policies in SQL statements
<a name="t_rls_statements"></a>

When using RLS policies in SQL statements, Amazon Redshift applies the following rules:
+ Amazon Redshift applies RLS policies to the SELECT, UPDATE, and DELETE statements by default. 
+ For SELECT and UNLOAD, Amazon Redshift filters rows according to your defined policy.
+ For UPDATE, Amazon Redshift updates only the rows that are visible to you. If a policy restricts a subset of the rows in a table, then you can't update them.
+ For DELETE, you can delete only the rows that are visible to you. If a policy restricts a subset of the rows in a table, then you can't delete them. For TRUNCATE, you can still truncate the table.
+ For CREATE TABLE LIKE, tables created with the LIKE options won't inherit permission settings from the source table. Similarly, the target table won't inherit the RLS policies from source table.

# Combining multiple policies per user
<a name="t_rls_combine_policies"></a>

RLS in Amazon Redshift supports attaching multiple policies per user and object. When there are multiple policies defined for a user, Amazon Redshift applies all the policies with either AND or OR syntax depending on the RLS CONJUNCTION TYPE setting for the table. For more information about conjunction type, see [ALTER TABLE](r_ALTER_TABLE.md). 

Multiple policies on a table can be associated with you. Either multiple policies are directly attached to you, or you belong to multiple roles, and the roles have different policies attached to them. 

When the multiple policies should restrict rows access in a given relation, you can set RLS CONJUNCTION TYPE of the relation to AND. Consider the following example. Alice can only see Sports event that has a "catname" of NBA as the policy specified.

```
-- Create an analyst role and grant it to a user named Alice.
CREATE ROLE analyst;
CREATE USER alice WITH PASSWORD 'Name_is_alice_1';
GRANT ROLE analyst TO alice;

-- Create an RLS policy that only lets the user see sports.
CREATE RLS POLICY policy_sports
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Sports');

-- Create an RLS policy that only lets the user see NBA.
CREATE RLS POLICY policy_nba
WITH (catname VARCHAR(10))
USING (catname = 'NBA');

-- Attach both to the analyst role.
ATTACH RLS POLICY policy_sports ON category TO ROLE analyst;
ATTACH RLS POLICY policy_nba ON category TO ROLE analyst;

-- Activate RLS on the category table with AND CONJUNCTION TYPE. 
ALTER TABLE category ROW LEVEL SECURITY ON CONJUNCTION TYPE AND;

-- Change session to Alice.
SET SESSION AUTHORIZATION alice;

-- Select all from the category table.
SELECT catgroup, catname
FROM category;

 catgroup | catname 
---------+---------
 Sports   | NBA
(1 row)
```

When the multiple policies should permit the users to see more rows in a given relation, user can set RLS CONJUNCTION TYPE of the relation to OR. Consider the following example. Alice can only see "Concerts" and "Sports" as the policy specified.

```
-- Create an analyst role and grant it to a user named Alice.
CREATE ROLE analyst;
CREATE USER alice WITH PASSWORD 'Name_is_alice_1';
GRANT ROLE analyst TO alice;

-- Create an RLS policy that only lets the user see concerts.
CREATE RLS POLICY policy_concerts
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Concerts');

-- Create an RLS policy that only lets the user see sports.
CREATE RLS POLICY policy_sports
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Sports');

-- Attach both to the analyst role.
ATTACH RLS POLICY policy_concerts ON category TO ROLE analyst;
ATTACH RLS POLICY policy_sports ON category TO ROLE analyst;

-- Activate RLS on the category table with OR CONJUNCTION TYPE. 
ALTER TABLE category ROW LEVEL SECURITY ON CONJUNCTION TYPE OR;

-- Change session to Alice.
SET SESSION AUTHORIZATION alice;

-- Select all from the category table.
SELECT catgroup, count(*)
FROM category
GROUP BY catgroup ORDER BY catgroup;

 catgroup | count 
---------+-------
 Concerts |  3
 Sports   |  5
(2 rows)
```

# RLS policy ownership and management
<a name="t_rls_ownership"></a>

As a superuser, security administrator, or user that has the sys:secadmin role, you can create, modify, attach, and detach RLS policies. RLS policies can be attached to tables, views, late binding views (LBVs), and materialized views (MVs). At the object level, you can turn row-level security on or off without modifying the schema definition for tables.

To get started with row-level security, following are SQL statements that you can use:
+ Use the ALTER TABLE statement to turn on or off RLS on a table, view, or late binding view. For more information, see [ALTER TABLE](r_ALTER_TABLE.md).
+ Use the ALTER MATERIALIZED VIEW statement to statement to turn on or off RLS on a materialized view (MV). For more information, see [ALTER MATERIALIZED VIEW](r_ALTER_MATERIALIZED_VIEW.md).
+ Use the CREATE RLS POLICY statement to create a security policy for one or more tables, and specify one or more users or roles in the policy. 

  For more information, see [CREATE RLS POLICY](r_CREATE_RLS_POLICY.md).
+ Use the ALTER RLS POLICY statement to alter the policy, such as changing the policy definition. You can use the same policy for multiple tables or views.

  For more information, see [ALTER RLS POLICY](r_ALTER_RLS_POLICY.md).
+ Use the ATTACH RLS POLICY statement to attach a policy to one or more relations, to one or more users, or to roles.

  For more information, see [ATTACH RLS POLICY](r_ATTACH_RLS_POLICY.md).
+ Use the DETACH RLS POLICY statement to detach a policy from one or more relations, from one or more users, or from roles.

  For more information, see [DETACH RLS POLICY](r_DETACH_RLS_POLICY.md).
+ Use the DROP RLS POLICY statement to drop a policy.

  For more information, see [DROP RLS POLICY](r_DROP_RLS_POLICY.md).
+ Use the GRANT and REVOKE statements to explicitly grant and revoke SELECT permissions to RLS policies that reference lookup tables. For more information, see [GRANT](r_GRANT.md) and [REVOKE](r_REVOKE.md).

To monitor the policies created, sys:secadmin can view the [SVV\$1RLS\$1POLICY](r_SVV_RLS_POLICY.md) and [SVV\$1RLS\$1ATTACHED\$1POLICY](r_SVV_RLS_ATTACHED_POLICY.md).

To list RLS-protected relations, sys:secadmin can view [SVV\$1RLS\$1RELATION](r_SVV_RLS_RELATION.md).

To trace the application of RLS policies on queries that reference RLS-protected relations, a superuser, sys:operator, or any user with the system permission ACCESS SYSTEM TABLE can view [SVV\$1RLS\$1APPLIED\$1POLICY](r_SVV_RLS_APPLIED_POLICY.md). Note that sys:secadmin is not granted these permissions by default.

To allow users full access to an RLS-protected relation, you can grant the IGNORE RLS permission. Superusers or sys:secadmin are automatically granted IGNORE RLS. For more information, see [GRANT](r_GRANT.md).

To explain the RLS policy filters of a query in the EXPLAIN plan to troubleshoot RLS-related queries, you can grant the permission EXPLAIN RLS to any user. For more information, see [GRANT](r_GRANT.md) and [EXPLAIN](r_EXPLAIN.md). 

# Policy-dependent objects and principles
<a name="t_rls_object_dependency"></a>

To provide security for applications and to prevent policy objects from becoming stale or invalid, Amazon Redshift doesn't permit dropping or altering objects referenced by RLS policies.

Following lists schema object dependencies that Amazon Redshift tracks for RLS policies.
+ When tracking schema object dependency for the target table, Amazon Redshift follows these rules:
  + Amazon Redshift detaches the policy from a relation, user, role, or public when you drop a target table.
  + When you rename a target table name, there is no impact to the attached policies.
  + You can only drop the columns of the target table referenced inside the policy definition if you drop or detach the policy first. This also applies when the CASCADE option is specified. You can drop other columns in the target table.
  + You can't rename the referred columns of the target table. To rename referred columns, detach the policy first. This also applies when the CASCADE option is specified.
  + You can't change the type of the referred column, even when you specify the CASCADE option.
+ When tracking schema object dependency for the lookup table, Amazon Redshift follows these rules:
  + You can't drop a lookup table. To drop a lookup table, first drop the policy in which the lookup table is referred.
  + You can't rename a lookup table. To rename a lookup table, first drop the policy in which the lookup table is referred. This also applies when the CASCADE option is specified.
  + You can't drop the lookup table columns used in the policy definition. To drop the lookup table columns used in the policy definition, first drop the policy in which the lookup table is referred. This also applies when the CASCADE option is specified in the ALTER TABLE DROP COLUMN statement. You can drop other columns in the lookup table.
  + You can't rename the referred columns of the lookup table. To rename referred columns, first drop the policy in which the lookup table is referred. This also applies when the CASCADE option is specified.
  + You can't change the type of the referred column.
+ When a user or role is dropped, Amazon Redshift detaches all policies attached to the user or role automatically.
+ When you use the CASCADE option in the DROP SCHEMA statement, Amazon Redshift also drops the relations in the schema. It also drops the relations in any other schemas that are dependent on relations in the dropped schema. For a relation that is a lookup table in a policy, Amazon Redshift fails the DROP SCHEMA DDL. For any relations dropped by the DROP SCHEMA statement, Amazon Redshift detaches all policies that are attached to those relations.
+ You can only drop a lookup function (a function that is referred inside a policy definition) when you also drop the policy. This also applies when the CASCADE option is specified.
+ When a policy is attached to a table, Amazon Redshift checks if this table is a lookup table in a different policy. If this is the case, Amazon Redshift won't allow attaching a policy to this table.
+ While creating an RLS policy, Amazon Redshift checks if this table is a target table for any other RLS policy. If this is the case, Amazon Redshift won't allow creating a policy on this table.

## Example
<a name="t_rls_object_dependency-example"></a>

The following example illustrates how schema dependency is tracked.

```
-- The CREATE and ATTACH policy statements for `policy_events` references some
-- target and lookup tables.
-- Target tables are tickit_event_redshift and target_schema.target_event_table.
-- Lookup table is tickit_sales_redshift.
-- Policy `policy_events` has following dependencies:
--   table tickit_sales_redshift column eventid, qtysold
--   table tickit_event_redshift column eventid
--   table target_event_table column eventid
--   schema public and target_schema
CREATE RLS POLICY policy_events
WITH (eventid INTEGER)
USING (
    eventid IN (SELECT eventid FROM tickit_sales_redshift WHERE qtysold <3)
);

ATTACH RLS POLICY policy_events ON tickit_event_redshift TO ROLE analyst;

ATTACH RLS POLICY policy_events ON target_schema.target_event_table TO ROLE consumer;
```

# Considerations and limitations using RLS policies
<a name="t_rls_usage"></a>

## Considerations
<a name="t_rls_considerations"></a>

Following are considerations for working with RLS policies:
+ Amazon Redshift applies RLS policies to SELECT, UPDATE, or DELETE statements.
+ Amazon Redshift doesn't apply RLS policies to INSERT, COPY, ALTER TABLE APPEND statements.
+ RLS policies can be attached to tables, views, late binding views (LBVs), and materialized views (MVs).
+ Row-level security works with column-level security to protect your data.
+ When RLS is turned on for the source relation, Amazon Redshift supports the ALTER TABLE APPEND statement for superusers, users that have been explicitly granted the system permission IGNORE RLS, or the sys:secadmin role. In this case, you can run the ALTER TABLE APPEND statement to append rows to a target table by moving data from an existing source table. Amazon Redshift moves all tuples from the source relation into the target relation. The RLS status of the target relation doesn't affect the ALTER TABLE APPEND statement.
+ To facilitate migration from other data warehouse systems, you can set and retrieve customized session context variables for a connection by specifying the variable name and value.

  The following example sets session context variables for a row-level security (RLS) policy.

  ```
  -- Set a customized context variable.
  SELECT set_config(‘app.category’, ‘Concerts’, FALSE);
  
  -- Create a RLS policy using current_setting() to get the value of a customized context variable.
  CREATE RLS POLICY policy_categories
  WITH (catgroup VARCHAR(10)) 
  USING (catgroup = current_setting('app.category', FALSE));
  
  -- Set correct roles and attach the policy on the target table to one or more roles.
  ATTACH RLS POLICY policy_categories ON tickit_category_redshift TO ROLE analyst, ROLE dbadmin;
  ```

  For details on how to set and retrieve customized session context variables, go to [SET](r_SET.md), [SET\$1CONFIG](r_SET_CONFIG.md), [SHOW](r_SHOW.md), [CURRENT\$1SETTING](r_CURRENT_SETTING.md), and [RESET](r_RESET.md). For more information on modifying the server configuration in general, go to [Modifying the server configuration](cm_chap_ConfigurationRef.md#t_Modifying_the_default_settings).
**Important**  
 When using session context variables within RLS policies, the security policy is reliant on the user or role that invokes the policy. Be careful to avoid security vulnerabilities when using session context variables in RLS policies. 
+ Changing session user using SET SESSION AUTHORIZATION between DECLARE and FETCH or between subsequent FETCH statements won't refresh the already prepared plan based on the user policies at DECLARE time. Avoid changing session user when cursors are used with RLS-protected tables.
+ When the base objects inside a view object are RLS-protected, policies attached to the user running the query are applied on the respective base objects. This is different from object-level permission checks, where the view owner's permissions are checked against the view base objects. You can view the RLS-protected relations of a query in its EXPLAIN plan output.
+ When a user-defined function (UDF) is referenced in a RLS policy of a relation attached to a user, the user must have the EXECUTE permission over the UDF to query the relation.
+  Row-level security might limit query optimization. We recommend carefully evaluating query performance before deploying RLS-protected views on large datasets. 
+  Row-level security policies applied to late-binding views might be pushed into federated tables. These RLS policies might be visible in external processing engine logs. 

## Limitations
<a name="t_rls_limitations"></a>

Following are the limitations when working with RLS policies:
+ RLS policies can't be attached to external tables and several other relation types. For more information, see [ATTACH RLS POLICY](r_ATTACH_RLS_POLICY.md).
+ Amazon Redshift supports SELECT statements for certain RLS policies with lookups that have complex joins, but doesn't support UPDATE or DELETE statements. In cases with UPDATE or DELETE statements, Amazon Redshift returns the following error:

  ```
  ERROR: One of the RLS policies on target relation is not supported in UPDATE/DELETE.
  ```
+ Whenever a user-defined function (UDF) is referenced in a RLS policy of a relation attached to a user, the user must have the EXECUTE permission over the UDF to query the relation.
+ Correlated subqueries aren't supported. Amazon Redshift returns the following error:

  ```
  ERROR: RLS policy could not be rewritten.
  ```
+ Amazon Redshift doesn't support datasharing with RLS. If a relation doesn't have RLS turned off for datashares, the query fails on the consumer cluster with the following error:

  ```
  RLS-protected relation "rls_protected_table" cannot be accessed via datasharing query.
  ```

  You can turn off RLS for datashares using the ALTER TABLE command with the parameter ROW LEVEL SECURITY OFF FOR DATASHARES. For more information about using ALTER TABLE to enable or disable RLS, go to [ALTER TABLE](r_ALTER_TABLE.md).
+ In cross-database queries, Amazon Redshift blocks reads to RLS-protected relations. Users with the IGNORE RLS permission can access the protected relation using cross-database queries. When a user without the IGNORE RLS permission accesses RLS-protected relation through a cross-database query, the following error appears:

  ```
  RLS-protected relation "rls_protected_table" cannot be accessed via cross-database query.
  ```
+ ALTER RLS POLICY only supports modifying a RLS policy using the USING ( using\$1predicate\$1exp ) clause. You can't modify a RLS policy with a WITH clause when running ALTER RLS POLICY.
+ You can't query relations that have row-level security turned on if the values for any of the following configuration options don't match the default value of the session:
  +  `enable_case_sensitive_super_attribute` 
  +  `enable_case_sensitive_identifier` 
  +  `downcase_delimited_identifier` 

  Consider resetting your session’s configuration options if you attempt to query a relation with row-level security on and see the message "RLS protected relation does not support session level config on case sensitivity being different from its default value."
+  When your provisioned cluster or serverless namespace has any row-level security policies, the following commands are blocked for regular users: 

  ```
  ALTER <current_user> SET enable_case_sensitive_super_attribute/enable_case_sensitive_identifier/downcase_delimited_identifier
  ```

  When you create RLS policies, we recommend that you change the default configuration option settings for regular users to match the session’s configuration option settings at the time the policy was created. Superusers and users with the ALTER USER privilege can do this by using parameter group settings or the ALTER USER command. For information about parameter groups, see [Amazon Redshift parameter groups](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-parameter-groups.html) in the *Amazon Redshift Management Guide*. For information about the ALTER USER command, see [ALTER USER](r_ALTER_USER.md).
+  Views and late-binding views with row-level security policies can't be replaced by regular users using the [CREATE VIEW](r_CREATE_VIEW.md) command. To replace views or LBVs with RLS policies, first detach any RLS policies attached to them, replace the views or LBVs, and reattach the policies. Superusers and users with the `sys:secadmin permission` can use CREATE VIEW on views or LBVs with RLS policies without detaching the policies. 
+  Views with row-level security policies can't reference system tables and system views. 
+  A late-binding view that's referenced by a regular view can't be RLS protected. 
+  RLS-protected relations and nested data from data lakes can't be accessed in the same query. 

# Best practices for RLS performance
<a name="t_rls_performance"></a>

Following are best practices to ensure better performance from Amazon Redshift on tables protected by RLS.

## Safety of operators and functions
<a name="t_rls_safe_operators"></a>

When querying RLS-protected tables, the usage of certain operators or functions may lead to performance degradation. Amazon Redshift classifies operators and functions either as safe or unsafe for querying RLS-protected tables. A function or operator is classified as RLS-safe when it doesn't have any observable side-effects depending on the inputs. In particular, a RLS-safe function or operator can't be one of the following:
+ Outputs an input value, or any value that is dependent on the input value, with or without an error message.
+ Fails or returns errors that are dependent on the input value.

RLS-unsafe operators include:
+ Arithmetic operators — \$1, -, /, \$1, %.
+ Text operators — LIKE and SIMILAR TO.
+ Some cast operators. Note that certain casts are classified as safe, including date-to-timestamp and timestamp-to-date conversions, integer widening casts (such as INT2 to INT8 or INT4 to INT8), and integer-to-text casts.
+ UDFs.

Use the following SELECT statement to check the safety of operators and functions.

```
SELECT proname, proc_is_rls_safe(oid) FROM pg_proc;
```

Amazon Redshift imposes restrictions on the order of evaluation of user predicates containing RLS-unsafe operators and functions when planning queries on RLS-protected tables. Queries referencing RLS-unsafe operators or functions might cause performance degradation when querying RLS-protected tables. Performance can degrade significantly when Amazon Redshift can't push RLS-unsafe predicates down to base table scans to take advantage of sort keys. For better performance, avoid queries using RLS-unsafe predicates that take advantage of a sort key. To verify that Amazon Redshift is able to push down operators and functions, you can use EXPLAIN statements in combination with the system permission EXPLAIN RLS.

### Conditionally safe functions
<a name="t_rls_conditional_safety"></a>

Some functions are classified as unsafe in general but become safe when specific arguments are constant values (literal values, not column references). When the relevant arguments are constants, Amazon Redshift can push these predicates down to base table scans, improving query performance.

For example, `DATE_TRUNC('day', timestamp_col)` is conditionally safe because `'day'` is a constant literal. However, `DATE_TRUNC(datepart_col, timestamp_col)` is not conditionally safe because `datepart_col` is a column reference.

The following table lists the categories of conditionally safe functions and which arguments must be constant for the function to be considered safe.


| Function category | Functions | Argument that must be constant | 
| --- | --- | --- | 
| DATE\$1TRUNC | DATE\$1TRUNC(datepart, timestamp), DATE\$1TRUNC(datepart, timestamptz) | datepart (first argument) | 
| EXTRACT / DATE\$1PART | EXTRACT(field FROM source), DATE\$1PART(field, source) for timestamp, timestamptz, date, time, timetz, interval, and datetime types | field (first argument) | 
| DATEDIFF | DATEDIFF(datepart, start, end) for timestamp and time types | datepart (first argument) | 
| TO\$1CHAR | TO\$1CHAR(timestamp, format), TO\$1CHAR(timestamptz, format) | format (second argument) | 
| CONVERT\$1TIMEZONE | CONVERT\$1TIMEZONE(source\$1tz, target\$1tz, timestamp), CONVERT\$1TIMEZONE(target\$1tz, timestamp) | timezone arguments | 
| LEFT / RIGHT | LEFT(string, length), RIGHT(string, length) | length (second argument) | 
| SUBSTRING | SUBSTRING(string, start, length) for text, bytea, and varbyte types | length (third argument) | 
| SPLIT\$1PART | SPLIT\$1PART(string, delimiter, part) | part (third argument) | 

Use the following SELECT statement to check which functions are conditionally safe and which argument positions must be constant.

```
SELECT proname, proc_is_rls_conditionally_safe(oid) FROM pg_proc
WHERE proc_is_rls_conditionally_safe(oid) IS NOT NULL;
```

The function returns an array of 0-indexed argument positions that must be constant, or NULL if the function is not conditionally safe.

## Result caching
<a name="t_rls_result_cache"></a>

To reduce query runtime and improve system performance, Amazon Redshift caches the results of certain types of queries in the memory on the leader node.

Amazon Redshift uses cached results for a new query scanning RLS-protected tables when all the conditions for unprotected tables are true and when all of the following are true:
+ The tables or views in the policy haven't been modified.
+ The policy doesn't use a function that must be evaluated each time it's run, such as GETDATE or CURRENT\$1USER.

For better performance, avoid using policy predicates that don't satisfy the preceding conditions.

For more information about result caching in Amazon Redshift, see [Result caching](c_challenges_achieving_high_performance_queries.md#result-caching).

## Complex policies
<a name="t_rls_complex_policies"></a>

For better performance, avoid using complex policies with subqueries that join multiple tables.

## Using constant arguments for better pushdown
<a name="t_rls_constant_args"></a>

When using functions such as DATE\$1TRUNC, EXTRACT, DATEDIFF, TO\$1CHAR, CONVERT\$1TIMEZONE, LEFT, RIGHT, SUBSTRING, or SPLIT\$1PART in queries on RLS-protected tables, use constant literal arguments instead of column references for the arguments that control error behavior. This allows Amazon Redshift to classify these functions as safe and push predicates down to base table scans, which can significantly improve performance.

For example, the following query allows predicate pushdown because the datepart argument is a constant:

```
SELECT * FROM rls_protected_table
WHERE DATE_TRUNC('month', event_date) = '2024-01-01';
```

The following query prevents predicate pushdown because the datepart argument is a column reference:

```
SELECT * FROM rls_protected_table
WHERE DATE_TRUNC(datepart_col, event_date) = '2024-01-01';
```

For the full list of conditionally safe functions and which arguments must be constant, see [Conditionally safe functions](#t_rls_conditional_safety).

# Row-level security end-to-end example
<a name="t_rls-example"></a>

The following is an end-to-end example to illustrate how a superuser creates some users and roles. Then, a user with the secadmin role creates, attaches, detaches, and drops RLS policies. This example uses the tickit sample database. For more information, see [Load data from Amazon S3 to Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html) in the *Amazon Redshift Getting Started Guide*.

```
-- Create users and roles referenced in the policy statements.
CREATE ROLE analyst;
CREATE ROLE consumer;
CREATE ROLE dbadmin;
CREATE ROLE auditor;
CREATE USER bob WITH PASSWORD 'Name_is_bob_1';
CREATE USER alice WITH PASSWORD 'Name_is_alice_1';
CREATE USER joe WITH PASSWORD 'Name_is_joe_1';
CREATE USER molly WITH PASSWORD 'Name_is_molly_1';
CREATE USER bruce WITH PASSWORD 'Name_is_bruce_1';
GRANT ROLE sys:secadmin TO bob;
GRANT ROLE analyst TO alice;
GRANT ROLE consumer TO joe;
GRANT ROLE dbadmin TO molly;
GRANT ROLE auditor TO bruce;
GRANT ALL ON TABLE tickit_category_redshift TO PUBLIC;
GRANT ALL ON TABLE tickit_sales_redshift TO PUBLIC;
GRANT ALL ON TABLE tickit_event_redshift TO PUBLIC;

-- Create table and schema referenced in the policy statements.
CREATE SCHEMA target_schema;
GRANT ALL ON SCHEMA target_schema TO PUBLIC;
CREATE TABLE target_schema.target_event_table (LIKE tickit_event_redshift);
GRANT ALL ON TABLE target_schema.target_event_table TO PUBLIC;

-- Change session to analyst alice.
SET SESSION AUTHORIZATION alice;

-- Check the tuples visible to analyst alice.
-- Should contain all 3 categories.
SELECT catgroup, count(*)
FROM tickit_category_redshift
GROUP BY catgroup ORDER BY catgroup;

-- Change session to security administrator bob.
SET SESSION AUTHORIZATION bob;

CREATE RLS POLICY policy_concerts
WITH (catgroup VARCHAR(10))
USING (catgroup = 'Concerts');

SELECT poldb, polname, polalias, polatts, polqual, polenabled, polmodifiedby FROM svv_rls_policy WHERE poldb = CURRENT_DATABASE();

ATTACH RLS POLICY policy_concerts ON tickit_category_redshift TO ROLE analyst, ROLE dbadmin;

ALTER TABLE tickit_category_redshift ROW LEVEL SECURITY ON;

SELECT * FROM svv_rls_attached_policy;

-- Change session to analyst alice.
SET SESSION AUTHORIZATION alice;

-- Check that tuples with only `Concert` category will be visible to analyst alice.
SELECT catgroup, count(*)
FROM tickit_category_redshift
GROUP BY catgroup ORDER BY catgroup;

-- Change session to consumer joe.
SET SESSION AUTHORIZATION joe;

-- Although the policy is attached to a different role, no tuples will be
-- visible to consumer joe because the default deny all policy is applied.
SELECT catgroup, count(*)
FROM tickit_category_redshift
GROUP BY catgroup ORDER BY catgroup;

-- Change session to dbadmin molly.
SET SESSION AUTHORIZATION molly;

-- Check that tuples with only `Concert` category will be visible to dbadmin molly.
SELECT catgroup, count(*)
FROM tickit_category_redshift
GROUP BY catgroup ORDER BY catgroup;

-- Check that EXPLAIN output contains RLS SecureScan to prevent disclosure of
-- sensitive information such as RLS filters.
EXPLAIN SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup;

-- Change session to security administrator bob.
SET SESSION AUTHORIZATION bob;

-- Grant IGNORE RLS permission so that RLS policies do not get applicable to role dbadmin.
GRANT IGNORE RLS TO ROLE dbadmin;

-- Grant EXPLAIN RLS permission so that anyone in role auditor can view complete EXPLAIN output.
GRANT EXPLAIN RLS TO ROLE auditor;

-- Change session to dbadmin molly.
SET SESSION AUTHORIZATION molly;

-- Check that all tuples are visible to dbadmin molly because `IGNORE RLS` is granted to role dbadmin.
SELECT catgroup, count(*)
FROM tickit_category_redshift
GROUP BY catgroup ORDER BY catgroup;

-- Change session to auditor bruce.
SET SESSION AUTHORIZATION bruce;

-- Check explain plan is visible to auditor bruce because `EXPLAIN RLS` is granted to role auditor.
EXPLAIN SELECT catgroup, count(*) FROM tickit_category_redshift GROUP BY catgroup ORDER BY catgroup;

-- Change session to security administrator bob.
SET SESSION AUTHORIZATION bob;

DETACH RLS POLICY policy_concerts ON tickit_category_redshift FROM ROLE analyst, ROLE dbadmin;

-- Change session to analyst alice.
SET SESSION AUTHORIZATION alice;

-- Check that no tuples are visible to analyst alice.
-- Although the policy is detached, no tuples will be visible to analyst alice
-- because of default deny all policy is applied if the table has RLS on.
SELECT catgroup, count(*)
FROM tickit_category_redshift
GROUP BY catgroup ORDER BY catgroup;

-- Change session to security administrator bob.
SET SESSION AUTHORIZATION bob;

CREATE RLS POLICY policy_events
WITH (eventid INTEGER) AS ev
USING (
    ev.eventid IN (SELECT eventid FROM tickit_sales_redshift WHERE qtysold <3)
);

ATTACH RLS POLICY policy_events ON tickit_event_redshift TO ROLE analyst;
ATTACH RLS POLICY policy_events ON target_schema.target_event_table TO ROLE consumer;

RESET SESSION AUTHORIZATION;

-- Can not cannot alter type of dependent column.
ALTER TABLE target_schema.target_event_table ALTER COLUMN eventid TYPE float;
ALTER TABLE tickit_event_redshift ALTER COLUMN eventid TYPE float;
ALTER TABLE tickit_sales_redshift ALTER COLUMN eventid TYPE float;
ALTER TABLE tickit_sales_redshift ALTER COLUMN qtysold TYPE float;

-- Can not cannot rename dependent column.
ALTER TABLE target_schema.target_event_table RENAME COLUMN eventid TO renamed_eventid;
ALTER TABLE tickit_event_redshift RENAME COLUMN eventid TO renamed_eventid;
ALTER TABLE tickit_sales_redshift RENAME COLUMN eventid TO renamed_eventid;
ALTER TABLE tickit_sales_redshift RENAME COLUMN qtysold TO renamed_qtysold;

-- Can not drop dependent column.
ALTER TABLE target_schema.target_event_table DROP COLUMN eventid CASCADE;
ALTER TABLE tickit_event_redshift DROP COLUMN eventid CASCADE;
ALTER TABLE tickit_sales_redshift DROP COLUMN eventid CASCADE;
ALTER TABLE tickit_sales_redshift DROP COLUMN qtysold CASCADE;

-- Can not drop lookup table.
DROP TABLE tickit_sales_redshift CASCADE;

-- Change session to security administrator bob.
SET SESSION AUTHORIZATION bob;

DROP RLS POLICY policy_concerts;
DROP RLS POLICY IF EXISTS policy_events;

ALTER TABLE tickit_category_redshift ROW LEVEL SECURITY OFF;

RESET SESSION AUTHORIZATION;

-- Drop users and roles.
DROP USER bob;
DROP USER alice;
DROP USER joe;
DROP USER molly;
DROP USER bruce;
DROP ROLE analyst;
DROP ROLE consumer;
DROP ROLE auditor FORCE;
DROP ROLE dbadmin FORCE;
```