DROP ROW LEVEL SECURITY POLICY
Function
In database security management, row-level security policies are used to restrict the access scope of different users to rows in a table. As services change, some policies may no longer be applicable. For example, the old data isolation rules need to be removed after the business department is adjusted, or the policy rules need to be rebuilt to adapt to the new permission model. When a row-level security policy is no longer required or needs to be rebuilt, you can use the DROP ROW LEVEL SECURITY POLICY statement to delete the existing policy.
Note that after a policy is deleted, the row-level security policy of the table becomes invalid, and all users who have permission to access the table can access all rows in the table. Therefore, before deleting a policy, ensure that this operation will not cause sensitive data leakage.
Precautions
- Only the table owner or administrator can execute DROP ROW LEVEL SECURITY POLICY.
- Security risk warning: After a row-level security policy is deleted, the data access scope restricted by the policy is removed, and the rows filtered by the policy become accessible.
- If you need to modify an existing policy instead of deleting and rebuilding it, you can use the ALTER ROW LEVEL SECURITY POLICY statement to avoid the risk of data exposure caused by the intermediate status of the deleted policy.
Syntax
1 | DROP [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name [ CASCADE | RESTRICT ] |
Parameter Description
| Parameter | Description | Value Range |
|---|---|---|
| IF EXISTS | If the specified row-level security policy does not exist, a notice is issued instead of throwing an error. | - |
| policy_name | Specifies the name of the row-level security policy to be deleted. | Name of an existing row-level security policy. |
| table_name | Specifies the name of the data table on which the row-level security policy to be deleted resides. | Name of the data table on which an existing row-level security policy resides. |
| CASCADE/RESTRICT | This syntax is provided only for compatibility. No objects depend on the row-level security policy, so CASCADE and RESTRICT have the same effect. | - |
Examples
- Use the system catalog PG_RLSPOLICY to query existing row-level security policies.
1SELECT polname, polrelid::regclass AS table_name FROM PG_RLSPOLICY;

- Delete the row-level security policy all_data_rls from the all_data table.
1DROP ROW LEVEL SECURITY POLICY all_data_rls ON all_data;
- Check whether the policy is successfully deleted. You can query the PG_RLSPOLICY system catalog to confirm that the target policy no longer exists.
1SELECT polname, polrelid::regclass AS table_name FROM PG_RLSPOLICY WHERE polname = 'all_data_rls';

Helpful Links
ALTER ROW LEVEL SECURITY POLICY, CREATE ROW LEVEL SECURITY POLICY
FAQ
Q: Is row-level security automatically disabled for a table after a policy is deleted?
A: No. Deleting a policy only removes the specified policy rule, and the enabling status of row-level security on the table remains unchanged. If there are other policies on the table, those policies will still take effect. If the deleted policy is the only one on the table, row-level security is still enabled, but since there is no policy to match, the query will not be filtered. In this case, all users with the required permissions can access all rows of data. To completely disable row-level security, run the ALTER TABLE... DISABLE ROW LEVEL SECURITY command.
1 | ALTER TABLE all_data DISABLE ROW LEVEL SECURITY; |
Q: How do I view the current row-level security policies on a table?
A: You can query the system catalog PG_RLSPOLICY. Replace tablename with the actual table name.
1 2 3 4 5 6 7 8 9 | SELECT p.polname, c.relname AS table_name, CASE p.polcmd WHEN '*' THEN 'ALL' WHEN 'r' THEN 'SELECT' WHEN 'w' THEN 'UPDATE' WHEN 'd' THEN 'DELETE' END AS cmd, CASE p.polpermissive WHEN true THEN 'PERMISSIVE' WHEN false THEN 'RESTRICTIVE' END AS policy_type, pg_get_userbyid(p.polroles[1]) AS affected_role, p.polqual FROM pg_rlspolicy p JOIN pg_class c ON p.polrelid = c.oid WHERE c.relname = 'tablename'; |
What is your overall rating for this page?
Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot