PostgreSQL Row Level Security (RLS): Basics and Examples
What is Postgres Row Level Security?
Row level security (RLS for short) is a PostgreSQL security feature provided by the open source PostgreSQL database. It allows database administrators to define policies to control how specific rows of data display and operate for one or more roles.
RLS is, in essence, an additional filter you can apply to a PostgreSQL database table. When a user tries to perform an action on a table, this filter is applied before the query criteria or other filtering, and the data is narrowed or rejected according to your security policy.
You can create row level security policies for specific commands like SELECT, INSERT, UPDATE, and DELETE, specify it for ALL commands.
In this article, you will learn:
- Why is Row Level Security Important?
- Row Level Security Policies
- Postgres Row Level Security Example
- Postgres Security with Satori
Why is Row Level Security Important?
Here are several aspects to know when implementing row-level security policies:Use ALTER TABLE … ENABLE ROW LEVEL SECURITY—to enable row-level security on the table. If this option is not enabled, your policy cannot be applied to the table. Use the CREATE POLICY command—to define new row-level security policies for each table. Each policy grants permission to specific database operations, such as DELETE, UPDATE, or SELECT. The policy is selective, only applying to rows that match a predefined SQL expression. USING statements are used to check existing table rows for the policy expression. WITH CHECK statements are used to check new rows Policy names can be repeated across tables—you can create a policy with the same name and reuse it across multiple tables.Here are several parameters you should know:Name—this is the name of the policy. table_name—lets you create a name for the table the policy is applied to. PERMISSIVE—lets you create a permissive policy. This is the default option. Permissive policies can be applied together to a single query. RESTRICTIVE—lets you create a restrictive policy. Restrictive policies can be applied together to a single query. You can use this parameter to restrict access to tables. command—the default option is DELETE. ALL. You can also use, SELECT, ALL, INSERT, UPDATE. role_name—the default is PUBLIC, which means the policy applies to all database users. using_expression—a SQL expression that returns boolean. Each row is checked against this expression—if it returns false, it is silently suppressed and cannot be viewed or modified by the user. No error is returned. check_expression—a SQL expression returning boolean, which is used when INSERT or UPDATE operations are performed on the table. Rows are allowed if the policy expression is true, and if it returns false, an error is returned.Row Level Security Policies
Here are several aspects to know when implementing row-level security policies:
- Use ALTER TABLE … ENABLE ROW LEVEL SECURITY—to enable row-level security on the table. If this option is not enabled, your policy cannot be applied to the table.
- Use the CREATE POLICY command—to define new row-level security policies for each table.
- Each policy grants permission to specific database operations, such as DELETE, UPDATE, or SELECT.
- The policy is selective, only applying to rows that match a predefined SQL expression.
- USING statements are used to check existing table rows for the policy expression.
- WITH CHECK statements are used to check new rows
- Policy names can be repeated across tables—you can create a policy with the same name and reuse it across multiple tables.
Here are several parameters you should know:
- Name—this is the name of the policy.
- table_name—lets you create a name for the table the policy is applied to.
- PERMISSIVE—lets you create a permissive policy. This is the default option. Permissive policies can be applied together to a single query.
- RESTRICTIVE—lets you create a restrictive policy. Restrictive policies can be applied together to a single query. You can use this parameter to restrict access to tables.
- command—the default option is DELETE. ALL. You can also use, SELECT, ALL, INSERT, UPDATE.
- role_name—the default is PUBLIC, which means the policy applies to all database users.
- using_expression—a SQL expression that returns boolean. Each row is checked against this expression—if it returns false, it is silently suppressed and cannot be viewed or modified by the user. No error is returned.
- check_expression—a SQL expression returning boolean, which is used when INSERT or UPDATE operations are performed on the table. Rows are allowed if the policy expression is true, and if it returns false, an error is returned.
Postgres Row Level Security Examples
Here are examples showing how to define RLS policies for your database.
Allowing managers to edit their own rows
Here is an example showing how to create a policy that ensures only members of the “manager” role can access only the rows for a specific account. The code in this and the following example was shared in the PostgreSQL documentation.
The USING clause implicitly adds a WITH CHECK clause, ensuring that members of the manager role cannot perform SELECT, DELETE, or UPDATE operations on rows that belong to other managers, and cannot INSERT new rows belonging to another manager.
Alternatively, instead of providing a specific user role, you can use PUBLIC, which has the same effect, but applies the restrictions to all users of the database.
Applying different access permissions for new rows
The following example lets you apply another policy for new rows added by the user. This can be done by combining two policies—one enables all rows to be viewed by all roles, and the other only allows each user to modify her own rows.
Note that this policy has a different effect on different comments:
- When users perform a SELECT, the two policies are combined using an OR operator, allowing users to select all rows
- For other commands, only the second policy is applied, allowing each user to perform actions on their own rows.
Disabling row level security
You can turn off RLS, without deleting the policy. When needed, the policy can be re-enabled. To do this, use this line:
ALTER TABLE {table-name} ENABLE ROW LEVEL SECURITY;
