Hi!
> * What are the most important use cases here? Are we just trying to
> avoid the unnecessary use of superuser, or is there a real use case for
> subscribing to a subset of a publication?
For instance in target database we do not have permission on some table used in
publication,
but we still CREATE SUBSCRIPTION for owned tables.
> * What are all the reasons CREATE SUBSCRIPTION currently requires
> superuser?
I'm not sure, but it seems like only superuser have rights on all tables. I
can't find any restrictions.
> * Is the original idea of a special role still viable?
yes, i wrote simple patch. Role create externally, but it can be system role.
--------
Efimkin Evgeny
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 9021463a4c..31b5b9af8c 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -322,6 +322,7 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
char originname[NAMEDATALEN];
bool create_slot;
List *publications;
+ Oid role;
/*
* Parse and check options.
@@ -341,11 +342,13 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
*/
if (create_slot)
PreventInTransactionBlock(isTopLevel, "CREATE SUBSCRIPTION ... WITH (create_slot = true)");
-
- if (!superuser())
+ role = get_role_oid("pg_subsciption_users", true);
+ if (!is_member_of_role(GetUserId(), role))
+ {
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to create subscriptions"))));
+ (errmsg("must be pg_subsciption_users to create subscriptions"))));
+ }
rel = heap_open(SubscriptionRelationId, RowExclusiveLock);
@@ -1023,6 +1026,7 @@ DropSubscription(DropSubscriptionStmt *stmt, bool isTopLevel)
static void
AlterSubscriptionOwner_internal(Relation rel, HeapTuple tup, Oid newOwnerId)
{
+ Oid role;
Form_pg_subscription form;
form = (Form_pg_subscription) GETSTRUCT(tup);
@@ -1034,13 +1038,16 @@ AlterSubscriptionOwner_internal(Relation rel, HeapTuple tup, Oid newOwnerId)
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_SUBSCRIPTION,
NameStr(form->subname));
+ role = get_role_oid("pg_subsciption_users", true);
/* New owner must be a superuser */
- if (!superuser_arg(newOwnerId))
+ if (!is_member_of_role(GetUserId(), role))
+ {
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to change owner of subscription \"%s\"",
NameStr(form->subname)),
- errhint("The owner of a subscription must be a superuser.")));
+ errhint("The owner of a subscription must be a pg_subsciption_users.")));
+ }
form->subowner = newOwnerId;
CatalogTupleUpdate(rel, &tup->t_self, tup);
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index 38ae1b9ab8..fa5d343993 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -754,6 +754,8 @@ copy_table(Relation rel)
CopyState cstate;
List *attnamelist;
ParseState *pstate;
+ AclResult aclresult;
+ AclMode aclmask;
/* Get the publisher relation info. */
fetch_remote_table_info(get_namespace_name(RelationGetNamespace(rel)),
@@ -770,6 +772,13 @@ copy_table(Relation rel)
initStringInfo(&cmd);
appendStringInfo(&cmd, "COPY %s TO STDOUT",
quote_qualified_identifier(lrel.nspname, lrel.relname));
+ aclmask = ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE;
+ aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(),
+ aclmask);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, get_relkind_objtype(rel->rd_rel->relkind),
+ RelationGetRelationName(rel));
+
res = walrcv_exec(wrconn, cmd.data, 0, NULL);
pfree(cmd.data);
if (res->status != WALRCV_OK_COPY_OUT)
