Managing Access Policies
After you create access policies, you can use ALTER ACCESS POLICY
to manage them in several ways:
- Modify the expression of an access policy
- Enable or disable access policies
- Copy access policies to another table
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.