CREATE_POLICY(7pg18)

💅 CREATE POLICY(7) — PostgreSQL 18.6 Documentation ✨

okay bestie, sit down, hydrate, because today we're learning about CREATE POLICY and honestly? it's giving gatekeeper energy but we're gonna unlock it together 🫶

🌸 NAME

CREATE_POLICY — define a new row-level security policy for a table 💖

✨ SYNOPSIS

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

look at her. so structured. so much power in so few brackets 🎀

💫 DESCRIPTION

So the tea is: CREATE POLICY makes a brand-new row-level security policy for a table. But — and this is the part everyone skips and then cries about — row-level security has to actually be turned ON for the table (that's your ALTER TABLE ... ENABLE ROW LEVEL SECURITY) or all these cute little policies you're writing? they do literally nothing. dead. 💀

A policy is basically the bouncer that grants permission to select, insert, update, or delete rows that match the relevant policy expression. Here's the split:

rows that already exist get checked against the expression in USING
rows that would be created via INSERT or UPDATE get checked against the expression in WITH CHECK

When a USING expression returns true for a row, that row is visible to the user 👀. When it returns false or null, the row is just... not visible. And typically? no error. it just quietly disappears. (but see Table 300 for the exceptions, because of course there are exceptions 🙈)

When a WITH CHECK expression returns true for a row, that row gets inserted or updated. When it returns false or null? error. thrown. no silent treatment here, she's loud about it 😭

For INSERT, UPDATE, and MERGE statements, WITH CHECK expressions are enforced after BEFORE triggers are fired, and before any actual data modifications are made. so a BEFORE ROW trigger can modify the data to be inserted, which then affects the result of the security policy check. (yes fr fr, that's real.) and WITH CHECK expressions are enforced before any other constraints. she's first in line, always 💅

Policy names are per-table. so you can absolutely use the same policy name on a bunch of different tables, and each table gets its own definition that's appropriate for it. slay, honestly.

Policies can be applied for specific commands or for specific roles. default for newly created policies? they apply to all commands and all roles, unless you say otherwise. multiple policies can apply to a single command — more on that below. and Table 300 summarizes how the different types of policy apply to specific commands 📋

Now for the ✨plot twist✨ — for policies that can have both USING and WITH CHECK expressions (that's ALL and UPDATE), if you don't define a WITH CHECK expression, then the USING expression does double duty: it determines which rows are visible (the normal USING case) and which new rows are allowed to be added (the WITH CHECK case). one expression, two jobs, main character energy 👑

And if row-level security is enabled for a table but there are no applicable policies at all? a "default deny" policy is assumed, so no rows will be visible or updatable. nothing. zero. the void. 🕳️

🎀 PARAMETERS

name — the name of the policy you're creating. it has to be distinct from the name of any other policy for that table. no twins allowed 💖
table_name — the name (optionally schema-qualified) of the table the policy applies to.
PERMISSIVE — says this policy is created as a permissive policy. all permissive policies applicable to a given query get combined together using the Boolean "OR" operator. so by creating permissive policies, administrators can add to the set of records that can be accessed. and psst — policies are permissive by default 🌸
RESTRICTIVE — says this policy is created as a restrictive policy. all restrictive policies applicable to a given query get combined together using the Boolean "AND" operator. so by creating restrictive policies, administrators can reduce the set of records that can be accessed, because all restrictive policies must be passed for each record.

heads up: there needs to be at least one permissive policy granting access to records before restrictive policies can be usefully used to reduce that access. if only restrictive policies exist, then no records will be accessible. when you've got a mix of permissive and restrictive policies, a record is only accessible if at least one of the permissive policies passes, in addition to all the restrictive policies. both. always both. 💅

command — the command the policy applies to. valid options are ALL, SELECT, INSERT, UPDATE, and DELETE. ALL is the default. see below for the specifics on how these get applied.
role_name — the role(s) the policy is applied to. default is PUBLIC, which applies the policy to all roles. everyone's invited 🫶
using_expression — any SQL conditional expression (returning boolean). and no, the conditional expression cannot contain any aggregate or window functions, so don't even try it 🙈. this expression gets added to queries that refer to the table if row-level security is enabled. rows for which the expression returns true will be visible. any rows for which it returns false or null will not be visible to the user (in a SELECT), and will not be available for modification (in an UPDATE or DELETE). typically, such rows are silently suppressed — no error is reported (but see Table 300 for exceptions, because there's always a Table 300).
check_expression — any SQL conditional expression (returning boolean). again, the conditional expression cannot contain any aggregate or window functions. this expression gets used in INSERT and UPDATE queries against the table if row-level security is enabled. only rows for which the expression evaluates to true will be allowed. an error will be thrown if the expression evaluates to false or null for any of the records inserted, or any of the records that result from the update. also note: check_expression is evaluated against the proposed new contents of the row, not the original contents. she's looking forward, not back ✨

🌈 Per-Command Policies

⭐ ALL

Using ALL for a policy means it applies to all commands, regardless of the type of command. if an ALL policy exists and more specific policies exist, then both the ALL policy and the more specific policy (or policies) get applied. additionally, ALL policies get applied to both the selection side of a query and the modification side, using the USING expression for both cases if only a USING expression has been defined.

as an example: if an UPDATE is issued, then the ALL policy is applicable both to what the UPDATE will be able to select as rows to be updated (applying the USING expression), and to the resulting updated rows, to check if they're permitted to be added to the table (applying the WITH CHECK expression if defined, and the USING expression otherwise). and if an INSERT or UPDATE command tries to add rows to the table that don't pass the ALL policy's WITH CHECK expression (or its USING expression, if it doesn't have a WITH CHECK expression), the entire command will be aborted. all of it. gone. 😭

💜 SELECT

Using SELECT for a policy means it applies to SELECT queries and whenever SELECT permissions are required on the relation the policy is defined for. the result? only those records from the relation that pass the SELECT policy get returned during a SELECT query, and queries that require SELECT permissions — like UPDATE, DELETE, and MERGE — will also only see those records that are allowed by the SELECT policy.

a SELECT policy cannot have a WITH CHECK expression, since it only applies in cases where records are being retrieved from the relation, except as described below. (there's always an "except as described below," iykyk)

if a data-modifying query has a RETURNING clause, SELECT permissions are required on the relation, and any newly inserted or updated rows from the relation must satisfy the relation's SELECT policies in order to be available to the RETURNING clause. if a newly inserted or updated row doesn't satisfy the relation's SELECT policies, an error will be thrown — inserted or updated rows to be returned are never silently ignored. she said what she said 💅

if an INSERT has an ON CONFLICT DO UPDATE clause, or an ON CONFLICT DO NOTHING clause with an arbiter index or constraint specification, then SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies. if a row proposed for insertion doesn't satisfy the relation's SELECT policies, an error is thrown (the INSERT is never silently avoided). in addition, if the UPDATE path is taken, the row to be updated and the new updated row are checked against the relation's SELECT policies, and an error is thrown if they're not satisfied (an auxiliary UPDATE is never silently avoided). no sneaky skips around here 🔥

a MERGE command requires SELECT permissions on both the source and target relations, so each relation's SELECT policies are applied before they're joined, and the MERGE actions will only see those records that are allowed by those policies. in addition, if an UPDATE action is executed, the target relation's SELECT policies are applied to the updated row, just like for a standalone UPDATE — except that an error is thrown if they're not satisfied. dramatic but fair ✨

🦋 INSERT

Using INSERT for a policy means it applies to INSERT commands and MERGE commands that contain INSERT actions. rows being inserted that don't pass this policy will result in a policy violation error, and the entire INSERT command will be aborted.

an INSERT policy cannot have a USING expression, because it only applies in cases where records are being added to the relation. adding, not looking. periodt 👑

and note: an INSERT with an ON CONFLICT DO NOTHING/UPDATE clause will check the INSERT policies' WITH CHECK expressions for all rows proposed for insertion, regardless of whether or not they end up actually being inserted. she checks everybody at the door, even the ones who leave 🎀

👑 UPDATE

Using UPDATE for a policy means it applies to UPDATE, SELECT FOR UPDATE, and SELECT FOR SHARE commands, plus auxiliary ON CONFLICT DO UPDATE clauses of INSERT commands, and MERGE commands containing UPDATE actions. she gets around, honestly 💅

since an UPDATE command involves pulling an existing record and replacing it with a new modified record, UPDATE policies accept both a USING expression and a WITH CHECK expression. the USING expression determines which records the UPDATE command will see to operate against, while the WITH CHECK expression defines which modified rows are allowed to be stored back into the relation.

any rows whose updated values don't pass the WITH CHECK expression will cause an error, and the entire command will be aborted. and if only a USING clause is specified, then that clause gets used for both the USING and WITH CHECK cases. one clause, double duty, we love a multitasker 🌟

Table 300. Policies Applied by Command Type ┌──────────────┬───────────────┬────────────┬─────────────────────────┬────────────┐ │ │ SELECT/ALL │ INSERT/ALL │ UPDATE/ALL policy │ DELETE/ALL │ │ │ policy │ policy │ │ policy │ │ Command ├───────────────┼────────────┼────────────┬────────────┼────────────┤ │ │ USING │ WITH CHECK │ USING │ WITH CHECK │ USING │ │ │ expression │ expression │ expression │ expression │ expression │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ SELECT / │ Filter │ — │ — │ — │ — │ │ COPY ... TO │ existing │ │ │ │ │ │ │ row │ │ │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ SELECT FOR │ Filter │ — │ Filter │ — │ — │ │ UPDATE/SHARE │ existing │ │ existing │ │ │ │ │ row │ │ row │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ INSERT │ Check new │ Check new │ — │ — │ — │ │ │ row [a] │ row │ │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ UPDATE │ Filter │ — │ Filter │ Check new │ — │ │ │ existing │ │ existing │ row │ │ │ │ row [a] & │ │ row │ │ │ │ │ check │ │ │ │ │ │ │ new row [a] │ │ │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ DELETE │ Filter │ — │ — │ — │ Filter │ │ │ existing │ │ │ │ existing │ │ │ row [a] │ │ │ │ row │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ INSERT ... │ Check new │ Check new │ — │ — │ — │ │ ON CONFLICT │ row [b][c] │ row [c] │ │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ ON CONFLICT │ Check │ — │ Check │ Check new │ — │ │ DO UPDATE │ existing & │ │ existing │ row [d] │ │ │ │ new rows [d] │ │ row │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ MERGE │ Filter source │ — │ — │ — │ — │ │ │ & target rows │ │ │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ MERGE ... │ Check new │ Check new │ — │ — │ — │ │ THEN INSERT │ row [a] │ row │ │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ MERGE ... │ Check new row │ — │ Check │ Check new │ — │ │ THEN UPDATE │ │ │ existing │ row │ │ │ │ │ │ row │ │ │ ├──────────────┼───────────────┼────────────┼────────────┼────────────┼────────────┤ │ MERGE ... │ — │ — │ — │ — │ Check │ │ THEN DELETE │ │ │ │ │ existing │ │ │ │ │ │ │ row │ ├──────────────┴───────────────┴────────────┴────────────┴────────────┴────────────┤ │ ---- │ │ [a] If read access is required to either the existing or new row │ │ (for example, a WHERE or RETURNING clause that refers to columns │ │ from the relation). ---- │ │ [b] If an arbiter index or constraint is specified. ---- │ │ [c] Row proposed for insertion is checked regardless of whether or │ │ not a conflict occurs. ---- │ │ [d] New row of the auxiliary UPDATE command, which might be │ │ different from the new row of the original INSERT command. │ └──────────────────────────────────────────────────────────────────────────────────┘


Wait, I see a potential issue: In the original, the line for "UPDATE" has an extra space before "check" in "         check". That is, it's "│              │         check │". I have that. Good.

Also, the line for "MERGE ... THEN UPDATE" has "│ Check new row │ —          │ Check      │ Check new  │ —          │". I have that.

Now, the bottom border line: `└──────────────────────────────────────────────────────────────────────────────────┘`. I need to count the dashes. In the original, it's exactly as I copied. I'll trust it.

Now, I'll put this in a code block. I'll use ```text to start and ``` to end.

I need to ensure that the code block is not indented, because if I indent it, it might be rendered as a code block but with extra spaces. The instruction says "fenced code blocks (```) for synopsis lines, examples and multi-line code. Code is shown verbatim, so keep it exact." So I'll just put the fence at the beginning of a line, no indentation.

Now, let's assemble the final output. I'll write the prose, then the code block.

I should also add a little transition after the table? But the user said more parts follow, so no sign-off. I can just end with the code block. That's fine.

Now, check for any missed details. The original had a sentence before the table: "Table 300 summarizes how the different types of policy apply to specific commands. In the table, “check” means that the policy expression is checked and an error is thrown if it returns false or null, whereas “filter” means that the row is silently ignored if the policy expression returns false or null." I included that. The original also had the "DELETE" heading. I included it. The original had the UPDATE paragraphs. I included them. All good.

Now, about the color tags: I used <pink>, <red>, <rainbow>. I need to close them. I did. I also used emoji.

One more thing: The instruction says "Don't put them inside backticks." I didn't. I put color tags around plain text, not inside backticks. For example, <red>**error will be thrown**</red> — that's fine because the backticks are around `error will be thrown`? Wait, I wrote: an <red>**error will be thrown**</red>. There are no backticks there. So it's fine. I used **bold** inside the color tag. That's allowed? The instruction says color tags may wrap any inline text. Bold is inline text. So it's fine.

I used <rainbow>Table 300</rainbow> — that's fine.

Now, final output.

I'll write it out.
</think>So like, an `UPDATE` command usually also needs to read data from the columns in the relation it's updating—like in a `WHERE` clause, or a `RETURNING` clause, or in an expression on the right-hand side of the `SET` clause. When that happens, you also need <pink>SELECT</pink> rights on the relation being updated, and the appropriate <pink>SELECT</pink> or <pink>ALL</pink> policies get applied **in addition to** the `UPDATE` policies. So basically the user has to have access to the row(s) being updated through a <pink>SELECT</pink> or <pink>ALL</pink> policy, AND be granted permission to update the row(s) via an `UPDATE` or <pink>ALL</pink> policy. Both. No cap. 💅

Now, when an `INSERT` command has an auxiliary `ON CONFLICT DO UPDATE` clause, and the `UPDATE` path is taken, the row to be updated is first checked against the `USING` expressions of any `UPDATE` policies. Then the new updated row is checked against the `WITH CHECK` expressions. BUT—and this is the tea—unlike a standalone `UPDATE` command, if the existing row does not pass the `USING` expressions, an <red>**error will be thrown**</red>. The `UPDATE` path will never be silently avoided. Same energy for an `UPDATE` action of a `MERGE` command. 🫖

## DELETE 🗑️

Using `DELETE` for a policy means it applies to `DELETE` commands and `MERGE` commands containing `DELETE` actions. For a `DELETE` command, only rows that pass this policy will be seen by the `DELETE` command. There can be rows that are visible through a `SELECT` policy that are **not** available for deletion, if they do not pass the `USING` expression for the `DELETE` policy. But heads up: a `DELETE` action in a `MERGE` command will see rows that are visible through `SELECT` policies, and if the `DELETE` policy does not pass for such a row, an <red>**error will be thrown**</red>. 😭

In most cases, a `DELETE` command also needs to read data from columns in the relation that it is deleting from (e.g., in a `WHERE` clause or a `RETURNING` clause). In this case, `SELECT` rights are also required on the relation, and the appropriate `SELECT` or `ALL` policies will be applied **in addition to** the `DELETE` policies. So the user must have access to the row(s) being deleted through a `SELECT` or `ALL` policy, AND be granted permission to delete the row(s) via a `DELETE` or `ALL` policy. Both, bestie. 🫶

A `DELETE` policy **cannot** have a `WITH CHECK` expression, because it only applies in cases where records are being deleted from the relation—so there is no new row to check. Periodt. 💅

<rainbow>Table 300</rainbow> summarizes how the different types of policy apply to specific commands. In the table, “check” means that the policy expression is checked and an error is thrown if it returns false or null, whereas “filter” means that the row is silently ignored if the policy expression returns false or null. No drama, just skipped. ✨


## 🦋 Application of Multiple Policies 🦋

Okay so this is where it gets <hotpink>spicy</hotpink> and you actually need to lock in, bestie.

When you've got multiple policies of **different command types** all applying to the *same* command — like a `SELECT` policy and an `UPDATE` policy both showing up for an `UPDATE` command — then the user has to have **both** types of permissions. Like, yes you can select those rows *and* yes you can update them. So the expressions for one type get smooshed together with the expressions for the other type using the `AND` operator. <purple>Both. Always. No skipping.</purple>

Now when it's multiple policies of the **same command type** hitting the same command, the rules change up:

- there must be **at least one `PERMISSIVE` policy** granting access to the relation, and
- **all** the `RESTRICTIVE` policies must pass.

So: all the `PERMISSIVE` policy expressions get combined with `OR`, all the `RESTRICTIVE` policy expressions get combined with `AND`, and then those two results get combined with `AND`. 💅

> And if there are **no** `PERMISSIVE` policies at all? Access is **denied**. Full stop. Periodt.

Also super important: for the purposes of combining multiple policies, <cyan>**`ALL` policies are treated as having the same type as whichever other type of policy is being applied**</cyan>. iykyk.

So for example, in an `UPDATE` command requiring both `SELECT` and `UPDATE` permissions, if there are multiple applicable policies of each type, they get combined like this:

expression from RESTRICTIVE SELECT/ALL policy 1 AND expression from RESTRICTIVE SELECT/ALL policy 2 AND ... AND ( expression from PERMISSIVE SELECT/ALL policy 1 OR expression from PERMISSIVE SELECT/ALL policy 2 OR ... ) AND expression from RESTRICTIVE UPDATE/ALL policy 1 AND expression from RESTRICTIVE UPDATE/ALL policy 2 AND ... AND ( expression from PERMISSIVE UPDATE/ALL policy 1 OR expression from PERMISSIVE UPDATE/ALL policy 2 OR ... )


Look at all those parentheses. <yellow>She's structured. She's organized. She ate.</yellow> ✨

---

# 📝 NOTES 📝

First things first, bestie: <hotpink>you must be the **owner of a table** to create or change policies for it.</hotpink> Not a vibe, just a fact.

So here's a thing that lowkey matters a lot. Policies get applied for explicit queries against tables in the database, **but they are not applied** when the system is doing internal referential integrity checks or validating constraints. 😭 Which means there are *indirect* ways to figure out that a given value exists. Sneaky!!

An example of this: you try to insert a duplicate value into a column that's a primary key or has a unique constraint. If the insert **fails**, then the user can infer that the value already exists. (This example assumes the user is permitted by policy to insert records which they are **not** allowed to see. Yeah. That.) 🙈

Another example: a user is allowed to insert into a table which references another, otherwise hidden table. Existence can be determined by the user inserting values into the referencing table, where **success** would indicate that the value exists in the referenced table. <red>Leakage. Through the side door.</red>

These issues can be addressed by carefully crafting policies to prevent users from being able to insert, delete, or update records at all which might possibly indicate a value they are not otherwise able to see — or by using generated values (e.g., surrogate keys) instead of keys with external meanings. 🧚

Generally, the system will enforce filter conditions imposed using security policies **prior to** qualifications that appear in user queries, so that protected data doesn't get inadvertently exposed to user-defined functions which might not be trustworthy. <lavender>Safety first, always.</lavender>

However! Functions and operators marked by the system (or the system administrator) as <cyan>`LEAKPROOF`</cyan> may be evaluated **before** policy expressions, because they're assumed to be trustworthy. Trust the process, but only that process. 💅

Since policy expressions are added to the user's query directly, they'll be run with the **rights of the user running the overall query**. So if you're using a given policy, you must be able to access any tables or functions referenced in the expression — otherwise you'll just get a permission denied error when you try to query the table that has row-level security enabled. 😭

This does **not** change how views work, though. As with normal queries and views, permission checks and policies for the tables which are referenced by a view will use the **view owner's rights** and any policies which apply to the view owner — except if the view is defined using the <cyan>`security_invoker`</cyan> option (see `CREATE VIEW`). 👑

No separate policy exists for `MERGE`. Instead, the policies defined for `SELECT`, `INSERT`, `UPDATE`, and `DELETE` are applied while executing `MERGE`, depending on the actions that are performed. Main character energy: `MERGE` doesn't need its own policy, it borrows everyone else's. ✨

Additional discussion and practical examples can be found in **Section 5.9**. Go read it, fr fr. 📚

---

# 🌈 COMPATIBILITY 🌈

`CREATE POLICY` is a <purple>PostgreSQL extension</purple>. That's it, that's the whole section. 🫶

---

# 👀 SEE ALSO 👀

- `ALTER POLICY` (`ALTER_POLICY(7)`)
- `DROP POLICY` (`DROP_POLICY(7)`)
- `ALTER TABLE` (`ALTER_TABLE(7)`)

---

<pink>And that's the whole page, bestie — you made it to the end!! 🎀 This one came from **PostgreSQL 18.6**, dated **2026**, and it's the `CREATE POLICY(7)` page. Go forth and secure your rows. 💖✨</pink>

📦 where this page comes from

opensuse-leap 16.0: postgresql18 18.6-160000.1.1 · PostgreSQL
opensuse-leap 16.0: postgresql18 18.6-160000.1.1 · PostgreSQL
opensuse-leap 16.0: postgresql18 18.6-160000.1.1 · PostgreSQL
opensuse-leap 16.0: postgresql18 18.6-160000.1.1 · PostgreSQL
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-18: a language model's rewrite of the original, under its terms