Managing Access Policies

After you create access policies, you can use ALTER ACCESS POLICY to manage them in several ways:

You can also view access policies by querying 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

Modifying Access Policy Expression

ALTER ACCESS POLICY can modify the expression of an existing access policy. For example, you can modify access policy in the earlier example by extending access to the admin 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

Users with superuser privileges can enable and disable access row and column acess policies for a given table.

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:

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

When you create a copy of a table, the access policies of the original table are not copied to the new table. If you copy a table, you must use ALTER ACCESS POLICY to copy the original table's access policies to the new table.

If you rename a table with ALTER TABLE…RENAME TO, the access policies that were stored under the previous name are stored under the table's new name.

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;

The copied policy retains the source policy's enabled/disabled settings.