Managing access policies

By default, you can only manage access policies on tables that you own. You can optionally restrict access policy management to superusers with the AccessPolicyManagementSuperuserOnly parameter (false by default):

=> ALTER DATABASE DEFAULT SET PARAMETER AccessPolicyManagementSuperuserOnly = 1;
ALTER DATABASE

You can view and manage access policies for tables in several ways:

• View access policies

• Modify the expression of an access policy

• Enable or disable access policies

• Copy access policies to another table

Viewing access policies

You can view access policies in two ways:

• Query system table ACCESS_POLICY. For example, the following query returns all access policies on table public.customer_dimension:

  => \x
  => SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy WHERE table_name = 'public.customer_dimension';
  -[ RECORD 1 ]-----+----------------------------------------------------------------------------------------
  policy_type       | Column Policy
  is_policy_enabled | Enabled
  table_name        | public.customer_dimension
  column_name       | customer_address
  expression        | CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END
  

• Export table DDL from the database catalog with EXPORT_TABLES, EXPORT_OBJECTS, or EXPORT_CATALOG. For example:

  => SELECT export_tables('','customer_dimension');
                                  export_tables
  -----------------------------------------------------------------------------
  CREATE TABLE public.customer_dimension
  (
      customer_key int NOT NULL,
      customer_type varchar(16),
      customer_name varchar(256),
      customer_gender varchar(8),
      ...
      CONSTRAINT C_PRIMARY PRIMARY KEY (customer_key) DISABLED
  );
  
  CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END ENABLE;
  

Modifying access policy expression

ALTER ACCESS POLICY can modify the expression of an existing access policy. For example, you can modify the access policy in the earlier example by extending access to the dbadmin role:

=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address
    CASE WHEN enabled_role('dbadmin') THEN customer_address
         WHEN enabled_role('administrator') THEN customer_address
         ELSE '**************' END ENABLE;
ALTER ACCESS POLICY

Querying system table ACCESS_POLICY confirms this change:

=> SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy
  WHERE table_name = 'public.customer_dimension' AND column_name='customer_address';
-[ RECORD 1 ]-----+-------------------------------------------------------------------------------------------------------------------------------------------
policy_type       | Column Policy
is_policy_enabled | Enabled
table_name        | public.customer_dimension
column_name       | customer_address
expression        | CASE WHEN enabled_role('dbadmin') THEN customer_address WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END

Enabling and disabling access policies

Owners of a table can enable and disable its row and column access policies.

Row access policies

You enable and disable row access policies on a table:

ALTER ACCESS POLICY ON [schema.]table FOR ROWS { ENABLE | DISABLE }

The following examples disable and then re-enable the row access policy on table customer_dimension:

=> ALTER ACCESS POLICY ON customer_dimension FOR ROWS DISABLE;
ALTER ACCESS POLICY
=> ALTER ACCESS POLICY ON customer_dimension FOR ROWS ENABLE;
ALTER ACCESS POLICY

Column access policies

You enable and disable access policies on a table column as follows:

ALTER ACCESS POLICY ON [schema.]table FOR COLUMN column { ENABLE | DISABLE }

The following examples disable and then re-enable the same column access policy on customer_dimension.customer_address:

=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address DISABLE;
ALTER ACCESS POLICY
=> ALTER ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address ENABLE;
ALTER ACCESS POLICY

Copying access polices

You copy access policies from one table to another as follows. Non-superusers must have ownership of both the source and destination tables:

ALTER ACCESS POLICY ON [schema.]table { FOR COLUMN column | FOR ROWS } COPY TO TABLE table

When you create a copy of a table or move its contents with the following functions (but not CREATE TABLE AS SELECT or CREATE TABLE LIKE), the access policies of the original table are copied to the new/destination table:

• COPY_TABLE
• COPY_PARTITIONS_TO_TABLE
• MOVE_PARTITIONS_TO_TABLE
• SWAP_PARTITIONS_BETWEEN_TABLES

To copy access policies to another table, use ALTER ACCESS POLICY.

For example, you can copy a row access policy as follows:

=> ALTER ACCESS POLICY ON public.emp_dimension FOR ROWS COPY TO TABLE public.regional_managers_dimension;

The following statement copies the access policy on column employee_key from table public.emp_dimension to store.store_sales_fact:

=> ALTER ACCESS POLICY ON public.emp_dimension FOR COLUMN employee_key COPY TO TABLE store.store_sales_fact;