PostgreSQL Row-Level Security
PostgreSQL-compatible TiDB Cloud Starter supports PostgreSQL-compatible Row-Level Security (RLS). RLS further restricts which rows a database role can view, insert, update, or delete, in addition to the role's table privileges.
RLS policies are evaluated by the database for each applicable query. This makes RLS useful for multi-tenant applications and other workloads that require row-specific access controls.
Enable row-level security
Use ALTER TABLE ... ENABLE ROW LEVEL SECURITY:
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;
When RLS is enabled and no applicable policy exists, access is denied by default for roles that do not bypass RLS.
To disable RLS:
ALTER TABLE todos DISABLE ROW LEVEL SECURITY;
Force RLS for the table owner
By default, the table owner bypasses RLS policies.
Use FORCE ROW LEVEL SECURITY to make the table owner subject to the policies:
ALTER TABLE todos FORCE ROW LEVEL SECURITY;
To restore the default owner behavior:
ALTER TABLE todos NO FORCE ROW LEVEL SECURITY;
Superusers and roles with the BYPASSRLS attribute bypass RLS even when FORCE ROW LEVEL SECURITY is enabled. FORCE ROW LEVEL SECURITY subjects a table owner to policies only when the owner is not a superuser and does not have BYPASSRLS.
Create a policy
Use CREATE POLICY to define an RLS policy.
The general syntax is:
CREATE POLICY policy_name ON table_name
[AS { PERMISSIVE | RESTRICTIVE }]
[FOR { ALL | SELECT | INSERT | UPDATE | DELETE }]
[TO role_name [, ...]]
[USING (expression)]
[WITH CHECK (expression)];
If AS is omitted, the policy is PERMISSIVE.
If FOR is omitted, the policy applies to all supported commands.
If TO is omitted, the policy applies to PUBLIC.
USING and WITH CHECK
USING and WITH CHECK have different purposes:
The clauses used for each operation are:
For an UPDATE policy, if WITH CHECK is omitted, the USING expression is also used to validate the updated row.
Example: restrict rows by user
The following example allows each role to access only rows where user_id matches current_user.
Create a table:
CREATE TABLE todos (
id BIGSERIAL PRIMARY KEY,
user_id TEXT NOT NULL,
task TEXT NOT NULL,
done BOOLEAN DEFAULT false
);
Enable RLS:
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;
Allow users to read their own rows:
CREATE POLICY user_select
ON todos
FOR SELECT
USING (user_id = current_user);
Allow users to insert rows for themselves:
CREATE POLICY user_insert
ON todos
FOR INSERT
WITH CHECK (user_id = current_user);
Allow users to update their own rows:
CREATE POLICY user_update
ON todos
FOR UPDATE
USING (user_id = current_user)
WITH CHECK (user_id = current_user);
Allow users to delete their own rows:
CREATE POLICY user_delete
ON todos
FOR DELETE
USING (user_id = current_user);
Example: public and private rows
The following example allows roles that have SELECT privilege on the table to see published posts and authors to see their own unpublished posts.
Create the table and enable RLS:
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
author TEXT,
published BOOLEAN DEFAULT false,
content TEXT
);
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
Create a policy for published posts:
CREATE POLICY see_published
ON posts
FOR SELECT
USING (published = true);
Create another policy for a user's own posts:
CREATE POLICY see_own
ON posts
FOR SELECT
USING (author = current_user);
Because both policies are permissive by default, a role with SELECT privilege can see a row if either applicable policy allows it.
Permissive and restrictive policies
Permissive policies
Multiple PERMISSIVE policies for the same command are combined with OR.
For example:
permissive_policy_1 OR permissive_policy_2 OR ...
At least one applicable permissive policy must allow a row.
Restrictive policies
Use AS RESTRICTIVE when every matching restrictive policy must pass:
CREATE POLICY must_be_published
ON posts
AS RESTRICTIVE
FOR SELECT
USING (published = true);
Restrictive policies are combined with AND and applied in addition to the permissive result:
(permissive_policy_1 OR permissive_policy_2 OR ...)
AND restrictive_policy_1
AND restrictive_policy_2
AND ...
A restrictive policy does not grant access by itself. At least one applicable permissive policy must also allow the row.
Apply policies to specific roles
Use TO to limit a policy to one or more roles.
For example:
CREATE POLICY support_read
ON tickets
FOR SELECT
TO support_agent
USING (status <> 'private');
A policy without an explicit TO clause applies to PUBLIC.
Alter a policy
Use ALTER POLICY to change the roles or policy expressions:
ALTER POLICY see_own
ON posts
USING (author = current_user OR published = true);
Drop a policy
Use DROP POLICY:
DROP POLICY see_own ON posts;
To avoid an error when the policy does not exist:
DROP POLICY IF EXISTS see_own ON posts;
Bypass RLS
BYPASSRLS role attribute
A role with BYPASSRLS bypasses RLS policies.
Create a login role with this attribute:
CREATE ROLE service_admin
LOGIN
PASSWORD 'SecurePass1'
BYPASSRLS;
Grant or revoke the attribute later:
ALTER ROLE service_admin BYPASSRLS;
ALTER ROLE service_admin NOBYPASSRLS;
Grant BYPASSRLS only to trusted administrative or service roles.
Table owners
A table owner bypasses RLS by default. Use FORCE ROW LEVEL SECURITY if the owner should also be subject to policies.
SECURITY DEFINER functions
A SECURITY DEFINER function executes with the privileges of its owner.
For example:
CREATE FUNCTION list_all_posts()
RETURNS SETOF posts
LANGUAGE SQL
SECURITY DEFINER
AS $$
SELECT * FROM posts
$$;
If the function owner bypasses RLS, queries executed inside the function can also bypass RLS.
Inspect RLS policies
Use pg_policies to list policies on a table:
SELECT
policyname,
cmd,
permissive,
roles,
qual,
with_check
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'todos'
ORDER BY policyname;
Check whether RLS is enabled or forced:
SELECT
relname,
relrowsecurity,
relforcerowsecurity
FROM pg_class
WHERE oid = 'public.todos'::regclass;
Check whether a role can bypass RLS:
SELECT
rolname,
rolbypassrls
FROM pg_roles
WHERE rolname = 'app_user';
Security considerations
When using RLS:
- Use dedicated application roles instead of administrative roles for normal application traffic.
- Enable RLS before granting application roles access to protected tables.
- Create policies for every operation the application needs.
- Review both
USINGandWITH CHECKconditions forUPDATEpolicies. - Grant
BYPASSRLSonly to trusted roles. - Review
SECURITY DEFINERfunctions carefully because they execute with the function owner's privileges. - Test RLS behavior using the same role that the application uses.