Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In PostgreSQL, GRANT and row-level security (RLS) answer two different questions. GRANT decides whether a role may use a table or a column at all. RLS decides which rows that role can read, insert, update, or delete once the table is enabled for row security. A request succeeds only when both layers allow it, so a policy never replaces a grant, and a grant never replaces a policy.
What each layer controls
GRANT: object and column privileges
GRANT and REVOKE manage SQL privileges on objects such as tables, views, and sequences, and on individual columns where PostgreSQL supports column-level privileges. If a role lacks the privilege a statement needs, PostgreSQL rejects the statement with a permission denied error before any row is examined. Role membership and inheritance also shape which privileges a login role effectively holds, so the grant check depends on the role graph, not only on the grant issued to one name. See the PostgreSQL GRANT reference and the CREATE ROLE reference for the exact rules on membership and inheritance in PostgreSQL 18.
RLS: row visibility and permitted row changes
Row-level security is enabled per table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Once enabled, each normal query and data-modification command is filtered or checked against the policies that apply to the current role and command. PostgreSQL’s own description, from the Row Security Policies chapter of the PostgreSQL 18 documentation, is that RLS works “in addition to the SQL-standard privilege system available through GRANT,” restricting on a per-user basis which rows can be returned or changed.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Side-by-side comparison
| Question | GRANT privileges | RLS policies |
|---|---|---|
| Main job | Allow or deny use of an object or column. | Filter rows on read and check rows on write, for tables where RLS is enabled. |
| Granularity | Object or column. | Individual rows, expressed as SQL conditions. |
| Setup | GRANT and REVOKE. |
ALTER TABLE ... ENABLE ROW LEVEL SECURITY, then CREATE POLICY. |
| Behaviour when unconfigured | No privilege means the statement fails with a permission error. | RLS enabled with no applicable policy means default deny: no rows are visible or changeable for that role and command. |
| Which commands it governs | Each privilege type, such as SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES. | Normal SELECT, INSERT, UPDATE, and DELETE. TRUNCATE and REFERENCES are outside RLS. |
| Known bypasses | Not applicable; it is the base check. | Table owner (unless forced), superusers, and roles with BYPASSRLS. |
How the two layers work together
Think of the check as two gates in sequence. The first gate asks whether the role holds the SQL privilege for the operation. The second gate, which applies only to tables with RLS enabled and to roles that are not exempt, narrows the set of rows the operation can touch. A broad table grant does not switch RLS off for an ordinary role, and a permissive policy does not let a role write to a table it has no privilege on.
#1 Best Overall
Two details matter when designing a policy. A policy can be limited to specific commands and roles. Its USING expression controls which existing rows a role can see or target, while its WITH CHECK expression controls the rows a command creates or leaves behind. If a policy has no WITH CHECK clause, PostgreSQL uses the USING expression for those checks as well, which is the behaviour most tenant-isolation policies rely on.
Worked example: one table, one tenant per session
This example isolates rows by tenant. PostgreSQL does not know which tenant a connection belongs to. The application has to tell the database, and the approach below is one common implementation choice, not a built-in feature.
Step 1: create the table and grant the application role its privileges
Run these as a role that owns the table, such as a migration user:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
CREATE TABLE invoices (
id bigint PRIMARY KEY,
tenant_id integer NOT NULL,
amount numeric(12,2) NOT NULL
);
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;At this point app_user can use the table, and every row is visible to it because RLS has not been enabled yet.
Step 2: enable RLS and define the policy
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
FOR ALL
TO app_user
USING (tenant_id = current_setting('app.tenant_id', true)::integer)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::integer);The second argument true to current_setting makes the call return NULL instead of an error when the variable has not been set. The comparison then yields no matching rows, which keeps the default-deny behaviour.
Step 3: set the tenant inside each transaction
BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT id, amount FROM invoices;
COMMIT;Use SET LOCAL inside a transaction rather than a plain SET when connections are pooled. A session-level value survives after the request ends and can leak into the next borrower of the connection.
Rank #3
Expected results
- If
app_userhas no SELECT privilege, the query fails withpermission denied for table invoices. The policy is never consulted. - If the privilege exists but
app.tenant_idis not set in the transaction, the query returns zero rows. - If
app.tenant_idis 42, the query returns only rows wheretenant_idis 42, and an insert with any other tenant_id is rejected by theWITH CHECKexpression.
Policy combination rules
Once a table has several policies, the way they combine decides the effective access. The rules are:
- Permissive policies combine with OR. A row is allowed if any applicable permissive policy allows it.
- Restrictive policies combine with AND. Each applicable restrictive policy must also allow the row, on top of the permissive result.
- No applicable policy means default deny once RLS is enabled on the table.
When reviewing a table, list the policies that apply to each role and each command, not only the one whose name you recognise. A forgotten permissive policy can widen access, and a restrictive policy added later can narrow it in ways that surprise the application team. The pg_policies view shows each policy’s command, roles, and expressions:
SELECT policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'invoices';Exceptions that bypass RLS
RLS is not a universal filter. Several identities and operations sit outside it, and a design that ignores them can give false assurance.
Table owners
The table owner normally bypasses row security, so policies do not apply to the owner’s own queries. To make the owner subject to policies, run ALTER TABLE invoices FORCE ROW LEVEL SECURITY;. FORCE does not change the next two cases.
Superusers and BYPASSRLS roles
Superusers always bypass row policies, and so do roles that have the BYPASSRLS attribute. The attribute is controlled with CREATE ROLE and ALTER ROLE, and the normal default is NOBYPASSRLS. Because these identities see every row, review them as part of any access audit. The following query lists which roles carry the flags:
SELECT rolname, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolbypassrls;Operations outside RLS
- TRUNCATE is not subject to row policies. A role that has the TRUNCATE privilege can empty the whole table, so keep that privilege out of application roles.
- REFERENCES is not subject to row policies either.
- Referential-integrity checks, including unique and primary-key checks and foreign-key checks, bypass row security. The RLS documentation warns that policy design should consider whether these checks could reveal information about hidden rows through a covert channel, for example by reporting a duplicate key that the role cannot see.
The row_security setting for backups and dumps
The row_security setting controls how queries react when RLS would hide rows. With the default on, hidden rows are silently excluded. With off, PostgreSQL raises an error when a query would filter any rows. That behaviour matters for tools such as backups, where a silently partial result would be wrong. Setting row_security to off does not grant a bypass: it makes the filtering visible as an error, and the role still needs a bypass attribute or table ownership to read the hidden rows. The parameter is documented in the PostgreSQL 17 client connection defaults reference, and the behaviour is the same in later versions described in the PostgreSQL 18 documentation.
Operational checklist
- Verify the grants on each table and column that the application role needs, and check role membership for inherited privileges.
- Confirm which tables must enforce row isolation, and enable RLS on each one explicitly.
- Define a policy for every command the role uses, and state its USING and WITH CHECK expressions.
- List all permissive and restrictive policies that apply to each role and command, and check how they combine.
- Review the superuser and BYPASSRLS roles, and decide whether the table owner should be forced into RLS with FORCE ROW LEVEL SECURITY.
- Remove TRUNCATE and REFERENCES from roles that must not bypass isolation, and assess what the integrity checks could reveal.
- Check the behaviour against the PostgreSQL documentation for the version your server runs, since role and policy details can change between major releases.
Work through these steps once for each new table, and repeat the review whenever a new role or policy is added.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
