Обсуждение: CREATE TABLE LIKE INCLUDING POLICIES

hi.

demo:

 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
+-- coll_t1 row security is not enabled, but we still copy it's policies
+CREATE TABLE coll_t1(LIKE coll_t INCLUDING POLICIES);
+\d coll_t1
+       Table "regress_rls_schema.coll_t1"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+ c      | text |           |          |
+Policies (row security disabled):
+    POLICY "coll_p"
+      USING ((c < ('foo'::text COLLATE "C")))

by default, new table row security will be disabled, policy object comments are
copied if INCLUDING COMMENTS is specified.

ALTER TABLE SET DATA TYPE, changing a column type typically pulls the object and
reconstructs the corresponding CREATE or ALTER command string.
however CREATE TABLE LIKE retrieves the object’s catalog data and adjusts Vars
to build a Create.*Stmt node.

Here, we generate a CreatePolicyStmt from the source relation’s pg_policy
metadata and then pass it to CreatePolicy.

CreatePolicy normally performs parse analysis on CreatePolicyStmt->qual and
CreatePolicyStmt->with_check.  However, since the pg_policy entries from the
source relation already have their qual and check_qual parse analyzed, we cannot
re-analyze them.  Similar to transformStatsStmt, we therefore need a
transformPolicyStmt.


v1-0001: refactor CreatePolicy, add function transformPolicyStmt
briefly explained in [1].
v1-0002: CREATE TABLE LIKE INCLUDING-POLICIES

[1] https://postgr.es/m/CACJufxGPcBzdL9T6Qh=OFecN8zqxuU0QXfYF8F3WYV=uzwYCdA@mail.gmail.com

hi.

to avoid repeated name lookup issue, I added a RangeTblEntry field to
CreatePolicyStmt.
see v2-0001-add-RangeTblEntry-to-CreatePolicyStmt.patch commit message too.

v2-0001, v2-0002 will be used in thread [1] too.
Please also check polished v2-0003.

The main logic is for each source table POLICY, using pg_policy catalog
information, produces a CreatePolicyStmt node. The source relation is already
locked in AccessShareLock, but POLICY qual, with check qual may reference other
unrelated relations, we also need to lock them in AccessShareLock too.

[1]: https://postgr.es/m/CACJufxE42vysVEDEmaoBGmGYLZTCgUAwh_h-c9dcSLDtD5jE3g@mail.gmail.com



--
jian
https://www.enterprisedb.com

hi.

inspired by this message [1], now, v3 logic is more like other CREATE
TABLE LIKE INCLUDING commands.

Since policy queries can include SubLinks, we first acquire
AccessShareLock lock on all the referenced relations.
Only then we copy the USING and WITH CHECK clauses from the source relation.

[1] https://postgr.es/m/CA+TgmoY8v44Y8j--b7e72kuvupZdtMEZf6FVjRfeqsG4gunpzw@mail.gmail.com


--
jian
https://www.enterprisedb.com

Hello!

Generally looks good to me, the tests work, the new feature seem to
work, I only noticed a few minor things.

parse_utilcmd.c:2204
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Security policy \"%s\" contains a whole-row reference to
table \"%s\".",
+   NameStr(policy_form->polname),
+   RelationGetRelationName(parent_rel)));

The outer parentheses are missing after "ERROR, "

Same issue at line 2232.

parse_utilcmd:1634
+ policy_form = (Form_pg_policy) GETSTRUCT(tuple);
+
+ polrels = PolicyGetRelations(policy_form->oid);

Shouldn't the function free polrels after the foreach below?

policy.c:1357
+
+ relation_close(depRel, AccessShareLock);
+

Shouldn't that be table_close?

create_table.sgml:760
+          All row-level security policies are copied to the new table.
+          Note that by default row-level security is not enabled to
the new table,
+          using <command>ALTER TABLE ... ENABLE ROW LEVEL SECURITY</command>
+          in order for created policies to be applied to the new table.

Maybe "use ALTER TABLE ... ENABLE ROW LEVEL SECURITY to copy existing
policies to the new table"?

I am also wondering if this shouldn't be at least an option for CREATE
TABLE AS. This seems like something that's very easy to overlook and
accidentally forget.

On Wed, Jan 21, 2026 at 11:23 PM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:
>
> Hello!
>
> Generally looks good to me, the tests work, the new feature seem to
> work, I only noticed a few minor things.
>
> parse_utilcmd.c:2204
> + ereport(ERROR,
> + errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("cannot convert whole-row table reference"),
> + errdetail("Security policy \"%s\" contains a whole-row reference to
> table \"%s\".",
> +   NameStr(policy_form->polname),
> +   RelationGetRelationName(parent_rel)));
>
> The outer parentheses are missing after "ERROR, "
>
> Same issue at line 2232.

Actually, this extra parentheses is optional.
see
https://postgr.es/m/202510100916.s2e6n3xiwvyc@alvherre.pgsql
and
https://postgr.es/m/CACJufxEheV10DpjFf+J1OabMgRe6CH+4c6d8ca3Wh1v8Twh3ZA@mail.gmail.com

>
> parse_utilcmd:1634
> + policy_form = (Form_pg_policy) GETSTRUCT(tuple);
> +
> + polrels = PolicyGetRelations(policy_form->oid);
>
> Shouldn't the function free polrels after the foreach below?
>

I found this post
https://stackoverflow.com/questions/79672060/when-should-i-use-pfree-in-a-postgres

I noticed that the above RelationGetIndexList does not list_free
parent_indexes as well.
It should be ok not not call list_free polrels too, because as the memory
allocation here is in portal context which should be reset by end of the current
statement execution.

> policy.c:1357
> +
> + relation_close(depRel, AccessShareLock);
> +
>
> Shouldn't that be table_close?
>
> create_table.sgml:760
> +          All row-level security policies are copied to the new table.
> +          Note that by default row-level security is not enabled to
> the new table,
> +          using <command>ALTER TABLE ... ENABLE ROW LEVEL SECURITY</command>
> +          in order for created policies to be applied to the new table.
>
> Maybe "use ALTER TABLE ... ENABLE ROW LEVEL SECURITY to copy existing
> policies to the new table"?
>
how about

+ All row-level security policies are copied to the new table.
+ Note, however, that row-level security policies are not enabled on the new
+ table by default,
+ using <command>ALTER TABLE ... ENABLE ROW LEVEL SECURITY</command>
+ for the copied policies to take effect.

> I am also wondering if this shouldn't be at least an option for CREATE
> TABLE AS. This seems like something that's very easy to overlook and
> accidentally forget.

do you mean
https://www.postgresql.org/docs/current/sql-createtableas.html
to also copy RLS policies.
if so, seems not doable, for example,

create table x as select from tenk1, tenk2 limit 2;

Should it copy all RLS policies from tenk1 and tenk2 to the new tables?
That does not seem to make sense to me.



--
jian
https://www.enterprisedb.com/

> Actually, this extra parentheses is optional.

Thanks for the links, I thought this is a mandatory convention in PG.

> I noticed that the above RelationGetIndexList does not list_free
> parent_indexes as well.
> It should be ok not not call list_free polrels too, because as the memory
> allocation here is in portal context which should be reset by end of the current
> statement execution.

I mainly suggested that because here PolicyGetRelations is inside a
loop, so it allocates multiple lists, not just one per statement. It
might be still a low amount of memory, so maybe its good as is.

> how about ...

that sounds good to me.


> do you mean
> https://www.postgresql.org/docs/current/sql-createtableas.html
> to also copy RLS policies.

No, sorry, I meant to write CREATE TABLE LIKE there. What I meant is
that it might be better to enable the policies if they are enabled on
the parent table, or at least to have an option to do this, as this
seems easy to overlook (to assume that if it INCLUDING POLICIES, that
includes enabling them).

Especially because in most RLS examples/introduction, you first run
ALTER TABLE ENABLE ROW LEVEL SECURITY, and then add policies with
CREATE POLICY after that.

> if so, seems not doable, for example,
>
> create table x as select from tenk1, tenk2 limit 2;
>
> Should it copy all RLS policies from tenk1 and tenk2 to the new tables?

Now that I think about it, it still would be a nice option for simple
cases where people just want to duplicate a table

create table including policies table_copy as select * from table;

But that's definitely a different topic.