3.7. Policies

Policies are objects that handle the row-level security system introduced by PostgreSQL 10. The row-level security must be enabled in the table which will receive a policy so this object can be applied to the first. A policy grants the permission to select, insert, update, or delete rows that match the relevant policy expression. Existing table rows are checked against the expression specified in USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified in WITH CHECK. Policies can be applied for specific SQL commands or for specific roles. The following image and table show the policy object attributes.

Attribute Description
Command The command to which the policy applies. Possible values are SELECT, INSERT, DELETE, UPDATE, and ALL.
Permissive This option creates a permissive policy when checked. A permissive policy has its expressions combined with other policies' expressions using OR operator when validating a certain query over the table. When not checked this option creates a restrictive policy that uses the AND operator during the validation of a query against a table.
Roles The roles to which the policy is applied. Leaving this field empty creates a policy that is applied to all roles (PUBLIC).
Expressions This tab stores the expressions that constrain operations on the table. There are two expression types: USING and CHECK. The USING expression (returning a boolean result) will be added to queries that refer to the table making rows that match that expression visible.

The CHECK expression (also returning a boolean result) will be in INSERT and UPDATE queries executed against the table and only rows for which the expression evaluates to true will be allowed. Different from USING expression the CHECK is evaluated against the new contents of the row, not the current one.

Jun 29, 2023 at 15:08