Hi,
Inspired by this message [1], introduce CREATE TABLE LIKE INCLUDING PRIVILEGES.
With this, the source table ACL stored in pg_class.relacl and
pg_attribute.attacl will now be copied to the target table.
This functionality only works if the current user is also the owner of
the source table.
Demo:
CREATE ROLE alice;
CREATE ROLE bob;
SET ROLE alice;
CREATE TABLE src (a int, b int);
GRANT INSERT ON src TO bob WITH GRANT OPTION;
CREATE TABLE dst (LIKE src INCLUDING PRIVILEGES);
SELECT relacl FROM pg_class WHERE relname = 'dst';
relacl
-------------------------------------
{alice=arwdDxtm/alice,bob=a*/alice}
[1]: https://www.postgresql.org/message-id/546590.1771005826%40sss.pgh.pa.us
--
jian
https://www.enterprisedb.com/
From 85229eab58f21cab13c4b982e2f8a7091ff8abe2 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 3 Jun 2026 14:12:33 +0800
Subject: [PATCH v1 1/1] CREATE TABLE LIKE INCLUDING PRIVILEGES
Adds INCLUDING PRIVILEGES option to CREATE TABLE LIKE, which copies
access privileges of the source table and its columns to the new table.
Demo:
CREATE ROLE alice;
CREATE ROLE bob;
SET ROLE alice;
CREATE TABLE src (a int, b int);
GRANT INSERT ON src TO bob WITH GRANT OPTION;
CREATE TABLE dst (LIKE src INCLUDING PRIVILEGES);
SELECT relacl FROM pg_class WHERE relname = 'dst';
relacl
-------------------------------------
{alice=arwdDxtm/alice,bob=a*/alice}
discussion: https://postgr.es/m
commitfest entry: https://commitfest.postgresql.org/patch
---
doc/src/sgml/ref/create_table.sgml | 17 +-
src/backend/catalog/aclchk.c | 75 +++++++--
src/backend/parser/gram.y | 7 +
src/backend/parser/parse_utilcmd.c | 155 +++++++++++++++++-
src/include/nodes/parsenodes.h | 7 +
src/include/utils/acl.h | 1 +
.../regress/expected/create_table_like.out | 92 +++++++++++
src/test/regress/expected/privileges.out | 16 ++
src/test/regress/sql/create_table_like.sql | 56 +++++++
src/test/regress/sql/privileges.sql | 11 ++
10 files changed, 419 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e342585c7f0..6a41cb729f6 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -100,7 +100,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | PRIVILEGES | STATISTICS | STORAGE | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -774,6 +774,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-privileges">
+ <term><literal>INCLUDING PRIVILEGES</literal></term>
+ <listitem>
+ <para>
+ Copies the access privileges of the source table and its columns
+ to the new table. Note that when the original grants were made by
+ a role other than the source table owner, those grants will be
+ re-issued by the current user instead, since
+ <command>CREATE TABLE LIKE INCLUDING PRIVILEGES</command> requires
+ the current user to own the source table.
+ The default behavior is to exclude privileges.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-statistics">
<term><literal>INCLUDING STATISTICS</literal></term>
<listitem>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 67424fe3b0c..0ed1047f2b6 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -434,21 +434,31 @@ ExecuteGrantStmt(GrantStmt *stmt)
* insert an ACL_ID_PUBLIC into the list if appropriate, so downstream
* there shouldn't be any additional work needed to support this case.
*/
- foreach(cell, stmt->grantees)
+ if (list_length(stmt->grantees) > 0)
{
- RoleSpec *grantee = (RoleSpec *) lfirst(cell);
- Oid grantee_uid;
-
- switch (grantee->roletype)
+ foreach(cell, stmt->grantees)
{
- case ROLESPEC_PUBLIC:
- grantee_uid = ACL_ID_PUBLIC;
- break;
- default:
- grantee_uid = get_rolespec_oid(grantee, false);
- break;
+ RoleSpec *grantee = (RoleSpec *) lfirst(cell);
+ Oid grantee_uid;
+
+ switch (grantee->roletype)
+ {
+ case ROLESPEC_PUBLIC:
+ grantee_uid = ACL_ID_PUBLIC;
+ break;
+ default:
+ grantee_uid = get_rolespec_oid(grantee, false);
+ break;
+ }
+ istmt.grantees = lappend_oid(istmt.grantees, grantee_uid);
}
- istmt.grantees = lappend_oid(istmt.grantees, grantee_uid);
+ }
+ else
+ {
+ Assert(stmt->granteeOids != NIL);
+
+ foreach_oid(grantee_uid, stmt->granteeOids)
+ istmt.grantees = lappend_oid(istmt.grantees, grantee_uid);
}
/*
@@ -5035,3 +5045,44 @@ RemoveRoleFromInitPriv(Oid roleid, Oid classid, Oid objid, int32 objsubid)
table_close(rel, RowExclusiveLock);
}
+
+const char *
+convert_aclchar_to_string(char acl)
+{
+ switch (acl)
+ {
+ case ACL_INSERT_CHR:
+ return "insert";
+ case ACL_SELECT_CHR:
+ return "select";
+ case ACL_UPDATE_CHR:
+ return "update";
+ case ACL_DELETE_CHR:
+ return "delete";
+ case ACL_TRUNCATE_CHR:
+ return "truncate";
+ case ACL_REFERENCES_CHR:
+ return "references";
+ case ACL_TRIGGER_CHR:
+ return "trigger";
+ case ACL_EXECUTE_CHR:
+ return "execute";
+ case ACL_USAGE_CHR:
+ return "usage";
+ case ACL_CREATE_CHR:
+ return "create";
+ case ACL_CREATE_TEMP_CHR:
+ return "temporary";
+ case ACL_CONNECT_CHR:
+ return "connect";
+ case ACL_SET_CHR:
+ return "set";
+ case ACL_ALTER_SYSTEM_CHR:
+ return "alter system";
+ case ACL_MAINTAIN_CHR:
+ return "maintain";
+ default:
+ elog(ERROR, "unexpected privilege type %c", acl);
+ pg_unreachable();
+ }
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..6ccc379d53f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4353,6 +4353,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | PRIVILEGES { $$ = CREATE_TABLE_LIKE_PRIVILEGES; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -7877,6 +7878,7 @@ GrantStmt: GRANT privileges ON privilege_target TO grantee_list
n->objtype = ($4)->objtype;
n->objects = ($4)->objs;
n->grantees = $6;
+ n->granteeOids = NIL;
n->grant_option = $7;
n->grantor = $8;
$$ = (Node *) n;
@@ -7896,6 +7898,7 @@ RevokeStmt:
n->objtype = ($4)->objtype;
n->objects = ($4)->objs;
n->grantees = $6;
+ n->granteeOids = NIL;
n->grantor = $7;
n->behavior = $8;
$$ = (Node *) n;
@@ -7912,6 +7915,7 @@ RevokeStmt:
n->objtype = ($7)->objtype;
n->objects = ($7)->objs;
n->grantees = $9;
+ n->granteeOids = NIL;
n->grantor = $10;
n->behavior = $11;
$$ = (Node *) n;
@@ -8376,6 +8380,7 @@ DefACLAction:
n->objtype = $4;
n->objects = NIL;
n->grantees = $6;
+ n->granteeOids = NIL;
n->grant_option = $7;
$$ = (Node *) n;
}
@@ -8391,6 +8396,7 @@ DefACLAction:
n->objtype = $4;
n->objects = NIL;
n->grantees = $6;
+ n->granteeOids = NIL;
n->behavior = $7;
$$ = (Node *) n;
}
@@ -8406,6 +8412,7 @@ DefACLAction:
n->objtype = $7;
n->objects = NIL;
n->grantees = $9;
+ n->granteeOids = NIL;
n->behavior = $10;
$$ = (Node *) n;
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 37071502a9f..d3b4db3c300 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -140,6 +140,8 @@ static Const *transformPartitionBoundValue(ParseState *pstate, Node *val,
const char *colName, Oid colType, int32 colTypmod,
Oid partCollation);
+static void generateClonedGrantStmt(Relation relation, RangeVar *heapRel,
+ AttrNumber attnum, List **result);
/*
* transformCreateStmt -
@@ -1163,11 +1165,40 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
else
{
- aclresult = pg_class_aclcheck(RelationGetRelid(relation), GetUserId(),
- ACL_SELECT);
- if (aclresult != ACLCHECK_OK)
- aclcheck_error(aclresult, get_relkind_objtype(relation->rd_rel->relkind),
- RelationGetRelationName(relation));
+ /*
+ * CREATE TABLE LIKE INCLUDING PRIVILEGES requires the current user to
+ * own the source table. Otherwise, the reconstructed GRANTED BY
+ * clause of GrantStmt produced later may yield inconsistent results
+ * depending on the relationship between the current user and the
+ * grantor.
+ */
+ if (table_like_clause->options & CREATE_TABLE_LIKE_PRIVILEGES)
+ {
+ if (!object_ownercheck(RelationRelationId,
+ RelationGetRelid(relation),
+ GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER,
+ get_relkind_objtype(relation->rd_rel->relkind),
+ RelationGetRelationName(relation));
+ }
+ else
+ {
+ aclresult = pg_class_aclcheck(RelationGetRelid(relation), GetUserId(),
+ ACL_SELECT);
+
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, get_relkind_objtype(relation->rd_rel->relkind),
+ RelationGetRelationName(relation));
+ }
+ }
+
+ /* Process table/column ACL if required. */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_PRIVILEGES) &&
+ (relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
+ relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE))
+ {
+ generateClonedGrantStmt(relation, cxt->relation,
+ 0, &cxt->alist);
}
tupleDesc = RelationGetDescr(relation);
@@ -1264,6 +1295,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
cxt->alist = lappend(cxt->alist, stmt);
}
+
+ /* Now copy parent table column ACL */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_PRIVILEGES) &&
+ (relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
+ relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE))
+ {
+ generateClonedGrantStmt(relation, cxt->relation,
+ attribute->attnum,
+ &cxt->alist);
+ }
}
/*
@@ -1330,6 +1371,110 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
table_close(relation, NoLock);
}
+/*
+ * Reconstructs a single GrantStmt and append it to the result list.
+ */
+static void
+generateClonedGrantStmt(Relation relation, RangeVar *heapRel,
+ AttrNumber attnum, List **result)
+{
+ Acl *acl;
+ AclItem *acldat;
+ GrantStmt *stmt;
+ HeapTuple tuple;
+ Datum aclDatum;
+ bool isNull;
+
+ /*
+ * For table-level privilege grants, we get the source ACL from pg_class;
+ * for column-level grants, we use pg_attribute.
+ */
+ if (attnum > 0)
+ {
+ tuple = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(RelationGetRelid(relation)),
+ Int16GetDatum(attnum));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for attribute %d of relation %u",
+ attnum, RelationGetRelid(relation));
+
+ aclDatum = SysCacheGetAttr(ATTNUM, tuple, Anum_pg_attribute_attacl,
+ &isNull);
+ }
+ else
+ {
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(RelationGetRelid(relation)));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", RelationGetRelid(relation));
+
+ aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
+ &isNull);
+ }
+
+ if (isNull)
+ {
+ /* If there's no ACL, no need to copy it */
+ ReleaseSysCache(tuple);
+ return;
+ }
+
+ acl = DatumGetAclPCopy(aclDatum);
+ acldat = ACL_DAT(acl);
+ /* ACL datum already copied, now release the tuple */
+ ReleaseSysCache(tuple);
+
+ for (int j = 0; j < ACL_NUM(acl); j++)
+ {
+ AclItem *aclitem = &acldat[j];
+
+ /* Skip this if grantee is the same as grantor */
+ if (aclitem->ai_grantee == aclitem->ai_grantor)
+ continue;
+
+ stmt = makeNode(GrantStmt);
+ stmt->is_grant = true;
+ stmt->targtype = ACL_TARGET_OBJECT;
+ stmt->objtype = OBJECT_TABLE;
+ stmt->objects = list_make1(copyObject(heapRel));
+ stmt->privileges = NIL;
+ stmt->grantees = NIL;
+
+ for (int i = 0; i < N_ACL_RIGHTS; ++i)
+ {
+ if (ACLITEM_GET_PRIVS(*aclitem) & (UINT64CONST(1) << i))
+ {
+ AccessPriv *n = makeNode(AccessPriv);
+
+ n->priv_name = pstrdup(convert_aclchar_to_string(ACL_ALL_RIGHTS_STR[i]));
+
+ n->cols = NIL;
+
+ if (attnum > 0)
+ {
+ TupleDesc tupleDesc = RelationGetDescr(relation);
+
+ Form_pg_attribute attribute = TupleDescAttr(tupleDesc,
+ attnum - 1);
+
+ n->cols = list_make1(makeString(pstrdup(NameStr(attribute->attname))));
+ }
+
+ stmt->privileges = lappend(stmt->privileges, n);
+ }
+ }
+
+ stmt->granteeOids = lappend_oid(stmt->granteeOids, aclitem->ai_grantee);
+ stmt->grant_option = (ACLITEM_GET_GOPTIONS(*aclitem) != ACL_NO_RIGHTS);
+
+ /*
+ * Because CREATE TABLE LIKE INCLUDING PRIVILEGES requires the current
+ * user to own the source table, the current user is always the
+ * grantor, so leaving GrantStmt->grantor as NULL is ok.
+ */
+ *result = lappend(*result, stmt);
+ }
+}
+
/*
* expandTableLikeClause
*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 91377a6cde3..6efecb4f8f3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -811,6 +811,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_PRIVILEGES = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -2692,6 +2693,12 @@ typedef struct GrantStmt
List *privileges; /* list of AccessPriv nodes */
/* privileges == NIL denotes ALL PRIVILEGES */
List *grantees; /* list of RoleSpec nodes */
+
+ /*
+ * list of RoleSpec nodes; exactly one of 'grantees' or 'granteeOids' must
+ * be NIL
+ */
+ List *granteeOids;
bool grant_option; /* grant or revoke grant option */
RoleSpec *grantor; /* GRANTED BY clause, or NULL if none */
DropBehavior behavior; /* drop behavior (for REVOKE) */
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 0b9b04e78ee..392ec3df05f 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -287,5 +287,6 @@ extern void RemoveRoleFromInitPriv(Oid roleid,
extern bool object_ownercheck(Oid classid, Oid objectid, Oid roleid);
extern bool has_createrole_privilege(Oid roleid);
extern bool has_bypassrls_privilege(Oid roleid);
+extern const char *convert_aclchar_to_string(char acl);
#endif /* ACL_H */
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 5720d160f05..6a91a2dac53 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -698,6 +698,98 @@ SELECT attname, attcompression FROM pg_attribute
e |
(5 rows)
+CREATE ROLE regress_priv_1;
+CREATE ROLE regress_priv_2;
+CREATE ROLE regress_priv_3;
+SET SESSION AUTHORIZATION regress_priv_1;
+CREATE TABLE ctl_like_priv (a int, b int, c int, d int);
+CREATE VIEW ctl_like_priv_v1 AS SELECT * FROM ctl_like_priv;
+CREATE MATERIALIZED VIEW ctl_like_priv_mv AS SELECT * FROM ctl_like_priv;
+SET SESSION AUTHORIZATION regress_priv_2;
+-- error, current user should also own the source table
+CREATE TABLE ctl_like_aclcopy3(LIKE ctl_like_priv INCLUDING PRIVILEGES);
+ERROR: must be owner of table ctl_like_priv
+SET SESSION AUTHORIZATION regress_priv_1;
+GRANT SELECT (a) ON ctl_like_priv TO regress_priv_2;
+GRANT UPDATE (b, c) ON ctl_like_priv TO regress_priv_3;
+GRANT INSERT (a, c) ON ctl_like_priv TO regress_priv_3;
+GRANT DELETE ON ctl_like_priv TO PUBLIC;
+GRANT REFERENCES (d) ON ctl_like_priv TO regress_priv_2;
+GRANT TRIGGER, MAINTAIN ON ctl_like_priv TO regress_priv_2 WITH GRANT OPTION;
+CREATE TABLE ctl_like_privcopy1(LIKE ctl_like_priv INCLUDING ALL EXCLUDING PRIVILEGES);
+CREATE TABLE ctl_like_privcopy2(LIKE ctl_like_priv INCLUDING ALL);
+CREATE TABLE ctl_like_privcopy3(LIKE ctl_like_priv INCLUDING PRIVILEGES);
+SELECT relname, relacl
+FROM pg_class pc
+WHERE relname IN ('ctl_like_priv', 'ctl_like_privcopy1', 'ctl_like_privcopy2', 'ctl_like_privcopy3')
+ORDER BY relname;
+ relname | relacl
+--------------------+-----------------------------------------------------------------------------------------------
+ ctl_like_priv | {regress_priv_1=arwdDxtm/regress_priv_1,=d/regress_priv_1,regress_priv_2=t*m*/regress_priv_1}
+ ctl_like_privcopy1 |
+ ctl_like_privcopy2 | {regress_priv_1=arwdDxtm/regress_priv_1,=d/regress_priv_1,regress_priv_2=t*m*/regress_priv_1}
+ ctl_like_privcopy3 | {regress_priv_1=arwdDxtm/regress_priv_1,=d/regress_priv_1,regress_priv_2=t*m*/regress_priv_1}
+(4 rows)
+
+SELECT relname, pa.attname, pa.attacl
+FROM pg_class pc JOIN pg_attribute pa ON pa.attrelid = pc.oid
+WHERE relname IN ('ctl_like_priv', 'ctl_like_privcopy1', 'ctl_like_privcopy2', 'ctl_like_privcopy3')
+AND attnum > 0 AND pa.attacl IS NOT NULL
+ORDER BY pa.attname, relname;
+ relname | attname | attacl
+--------------------+---------+-------------------------------------------------------------------
+ ctl_like_priv | a | {regress_priv_2=r/regress_priv_1,regress_priv_3=a/regress_priv_1}
+ ctl_like_privcopy2 | a | {regress_priv_2=r/regress_priv_1,regress_priv_3=a/regress_priv_1}
+ ctl_like_privcopy3 | a | {regress_priv_2=r/regress_priv_1,regress_priv_3=a/regress_priv_1}
+ ctl_like_priv | b | {regress_priv_3=w/regress_priv_1}
+ ctl_like_privcopy2 | b | {regress_priv_3=w/regress_priv_1}
+ ctl_like_privcopy3 | b | {regress_priv_3=w/regress_priv_1}
+ ctl_like_priv | c | {regress_priv_3=aw/regress_priv_1}
+ ctl_like_privcopy2 | c | {regress_priv_3=aw/regress_priv_1}
+ ctl_like_privcopy3 | c | {regress_priv_3=aw/regress_priv_1}
+ ctl_like_priv | d | {regress_priv_2=x/regress_priv_1}
+ ctl_like_privcopy2 | d | {regress_priv_2=x/regress_priv_1}
+ ctl_like_privcopy3 | d | {regress_priv_2=x/regress_priv_1}
+(12 rows)
+
+GRANT ALL ON ctl_like_priv_v1 TO regress_priv_2;
+GRANT SELECT(a,c, d) ON ctl_like_priv_mv TO regress_priv_3;
+CREATE TABLE ctl_like_privcopy4(LIKE ctl_like_priv_v1 INCLUDING ALL);
+CREATE TABLE ctl_like_privcopy5(LIKE ctl_like_priv_mv INCLUDING PRIVILEGES);
+SELECT relname, relacl
+FROM pg_class pc
+WHERE relname IN ('ctl_like_priv_v1', 'ctl_like_priv_mv', 'ctl_like_privcopy4', 'ctl_like_privcopy5')
+ORDER BY relname;
+ relname | relacl
+--------------------+---------------------------------------------------------------------------------
+ ctl_like_priv_mv |
+ ctl_like_priv_v1 | {regress_priv_1=arwdDxtm/regress_priv_1,regress_priv_2=arwdDxtm/regress_priv_1}
+ ctl_like_privcopy4 | {regress_priv_1=arwdDxtm/regress_priv_1,regress_priv_2=arwdDxtm/regress_priv_1}
+ ctl_like_privcopy5 |
+(4 rows)
+
+SELECT relname, pa.attname, pa.attacl
+FROM pg_class pc JOIN pg_attribute pa ON pa.attrelid = pc.oid
+WHERE relname IN ('ctl_like_priv_v1', 'ctl_like_privcopy4', 'ctl_like_privcopy5', 'ctl_like_priv_mv')
+AND attnum > 0 AND pa.attacl IS NOT NULL
+ORDER BY pa.attname, relname;
+ relname | attname | attacl
+--------------------+---------+-----------------------------------
+ ctl_like_priv_mv | a | {regress_priv_3=r/regress_priv_1}
+ ctl_like_privcopy5 | a | {regress_priv_3=r/regress_priv_1}
+ ctl_like_priv_mv | c | {regress_priv_3=r/regress_priv_1}
+ ctl_like_privcopy5 | c | {regress_priv_3=r/regress_priv_1}
+ ctl_like_priv_mv | d | {regress_priv_3=r/regress_priv_1}
+ ctl_like_privcopy5 | d | {regress_priv_3=r/regress_priv_1}
+(6 rows)
+
+RESET SESSION AUTHORIZATION;
+DROP TABLE ctl_like_priv, ctl_like_privcopy1, ctl_like_privcopy2,
+ ctl_like_privcopy3, ctl_like_privcopy4, ctl_like_privcopy5 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to view ctl_like_priv_v1
+drop cascades to materialized view ctl_like_priv_mv
+DROP ROLE regress_priv_1, regress_priv_2, regress_priv_3;
DROP TABLE ctl_table;
DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 0de13612818..44033c5fac4 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -3682,6 +3682,22 @@ SELECT * FROM information_schema.table_privileges t
regress_grantor3 | regress_grantor5 | regression | public | grantor_test | UPDATE | NO | NO
(2 rows)
+BEGIN;
+RESET ROLE;
+CREATE ROLE regress_6;
+ALTER TABLE grantor_test OWNER TO regress_6;
+SET ROLE regress_6;
+CREATE TABLE grantor_test_copy(LIKE grantor_test INCLUDING PRIVILEGES);
+SELECT relname, relacl
+FROM pg_class pc
+WHERE relname IN ('grantor_test_copy', 'grantor_test') ORDER BY relname;
+ relname | relacl
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ grantor_test | {regress_6=arwdDxtm/regress_6,regress_grantor2=r*/regress_6,regress_grantor3=w*/regress_6,regress_grantor4=r*w*/regress_6,regress_grantor5=r/regress_grantor2,regress_grantor5=w/regress_grantor3}
+ grantor_test_copy | {regress_6=arwdDxtm/regress_6,regress_grantor2=r*/regress_6,regress_grantor3=w*/regress_6,regress_grantor4=r*w*/regress_6,regress_grantor5=rw/regress_6}
+(2 rows)
+
+ROLLBACK;
REVOKE SELECT, UPDATE ON grantor_test FROM regress_grantor5 GRANTED BY regress_grantor2;
WARNING: not all privileges could be revoked for "grantor_test"
WARNING: not all privileges could be revoked for column "tableoid" of relation "grantor_test"
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..d3400f44b50 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -276,6 +276,62 @@ CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl
SELECT attname, attcompression FROM pg_attribute
WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum;
+CREATE ROLE regress_priv_1;
+CREATE ROLE regress_priv_2;
+CREATE ROLE regress_priv_3;
+
+SET SESSION AUTHORIZATION regress_priv_1;
+CREATE TABLE ctl_like_priv (a int, b int, c int, d int);
+CREATE VIEW ctl_like_priv_v1 AS SELECT * FROM ctl_like_priv;
+CREATE MATERIALIZED VIEW ctl_like_priv_mv AS SELECT * FROM ctl_like_priv;
+
+SET SESSION AUTHORIZATION regress_priv_2;
+-- error, current user should also own the source table
+CREATE TABLE ctl_like_aclcopy3(LIKE ctl_like_priv INCLUDING PRIVILEGES);
+SET SESSION AUTHORIZATION regress_priv_1;
+
+GRANT SELECT (a) ON ctl_like_priv TO regress_priv_2;
+GRANT UPDATE (b, c) ON ctl_like_priv TO regress_priv_3;
+GRANT INSERT (a, c) ON ctl_like_priv TO regress_priv_3;
+GRANT DELETE ON ctl_like_priv TO PUBLIC;
+GRANT REFERENCES (d) ON ctl_like_priv TO regress_priv_2;
+GRANT TRIGGER, MAINTAIN ON ctl_like_priv TO regress_priv_2 WITH GRANT OPTION;
+
+CREATE TABLE ctl_like_privcopy1(LIKE ctl_like_priv INCLUDING ALL EXCLUDING PRIVILEGES);
+CREATE TABLE ctl_like_privcopy2(LIKE ctl_like_priv INCLUDING ALL);
+CREATE TABLE ctl_like_privcopy3(LIKE ctl_like_priv INCLUDING PRIVILEGES);
+
+SELECT relname, relacl
+FROM pg_class pc
+WHERE relname IN ('ctl_like_priv', 'ctl_like_privcopy1', 'ctl_like_privcopy2', 'ctl_like_privcopy3')
+ORDER BY relname;
+
+SELECT relname, pa.attname, pa.attacl
+FROM pg_class pc JOIN pg_attribute pa ON pa.attrelid = pc.oid
+WHERE relname IN ('ctl_like_priv', 'ctl_like_privcopy1', 'ctl_like_privcopy2', 'ctl_like_privcopy3')
+AND attnum > 0 AND pa.attacl IS NOT NULL
+ORDER BY pa.attname, relname;
+
+GRANT ALL ON ctl_like_priv_v1 TO regress_priv_2;
+GRANT SELECT(a,c, d) ON ctl_like_priv_mv TO regress_priv_3;
+CREATE TABLE ctl_like_privcopy4(LIKE ctl_like_priv_v1 INCLUDING ALL);
+CREATE TABLE ctl_like_privcopy5(LIKE ctl_like_priv_mv INCLUDING PRIVILEGES);
+
+SELECT relname, relacl
+FROM pg_class pc
+WHERE relname IN ('ctl_like_priv_v1', 'ctl_like_priv_mv', 'ctl_like_privcopy4', 'ctl_like_privcopy5')
+ORDER BY relname;
+
+SELECT relname, pa.attname, pa.attacl
+FROM pg_class pc JOIN pg_attribute pa ON pa.attrelid = pc.oid
+WHERE relname IN ('ctl_like_priv_v1', 'ctl_like_privcopy4', 'ctl_like_privcopy5', 'ctl_like_priv_mv')
+AND attnum > 0 AND pa.attacl IS NOT NULL
+ORDER BY pa.attname, relname;
+RESET SESSION AUTHORIZATION;
+
+DROP TABLE ctl_like_priv, ctl_like_privcopy1, ctl_like_privcopy2,
+ ctl_like_privcopy3, ctl_like_privcopy4, ctl_like_privcopy5 CASCADE;
+DROP ROLE regress_priv_1, regress_priv_2, regress_priv_3;
DROP TABLE ctl_table;
DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 95a46854b37..91324223b7b 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -2263,6 +2263,17 @@ GRANT SELECT, UPDATE ON grantor_test TO regress_grantor5 GRANTED BY regress_gran
SELECT * FROM information_schema.table_privileges t
WHERE grantor LIKE 'regress_grantor%' ORDER BY ROW(t.*);
+BEGIN;
+RESET ROLE;
+CREATE ROLE regress_6;
+ALTER TABLE grantor_test OWNER TO regress_6;
+SET ROLE regress_6;
+CREATE TABLE grantor_test_copy(LIKE grantor_test INCLUDING PRIVILEGES);
+SELECT relname, relacl
+FROM pg_class pc
+WHERE relname IN ('grantor_test_copy', 'grantor_test') ORDER BY relname;
+ROLLBACK;
+
REVOKE SELECT, UPDATE ON grantor_test FROM regress_grantor5 GRANTED BY regress_grantor2;
REVOKE SELECT, UPDATE ON grantor_test FROM regress_grantor5 GRANTED BY regress_grantor3;
--
2.34.1
