Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 4826759

Browse filesBrowse files
robertmhaasrobertmhaas
committed
Add a run_as_owner option to subscriptions.
This option is normally false, but can be set to true to obtain the legacy behavior where the subscription runs with the permissions of the subscription owner rather than the permissions of the table owner. The advantages of this mode are (1) it doesn't require that the subscription owner have permission to SET ROLE to each table owner and (2) since no role switching occurs, the SECURITY_RESTRICTED_OPERATION restrictions do not apply. On the downside, it allows any table owner to easily usurp the privileges of the subscription owner - basically, to take over their account. Because that's generally quite undesirable, we don't make this mode the default, but we do make it available, just in case the new behavior causes too many problems for someone. Discussion: http://postgr.es/m/CA+TgmoZ-WEeG6Z14AfH7KhmpX2eFh+tZ0z+vf0=eMDdbda269g@mail.gmail.com
1 parent 1e10d49 commit 4826759
Copy full SHA for 4826759

File tree

12 files changed

+376
-92
lines changed
Filter options

12 files changed

+376
-92
lines changed

‎doc/src/sgml/logical-replication.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/logical-replication.sgml
+17
Original file line numberDiff line numberDiff line change
@@ -1785,6 +1785,23 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
17851785
<literal>SET ROLE</literal> to each role that owns a replicated table.
17861786
</para>
17871787

1788+
<para>
1789+
If the subscription has been configured with
1790+
<literal>run_as_owner = true</literal>, then no user switching will
1791+
occur. Instead, all operations will be performed with the permissions
1792+
of the subscription owner. In this case, the subscription owner only
1793+
needs privileges to <literal>SELECT</literal>, <literal>INSERT</literal>,
1794+
<literal>UPDATE</literal>, and <literal>DELETE</literal> from the
1795+
target table, and does not need privileges to <literal>SET ROLE</literal>
1796+
to the table owner. However, this also means that any user who owns
1797+
a table into which replication is happening can execute arbitrary code with
1798+
the privileges of the subscription owner. For example, they could do this
1799+
by simply attaching a trigger to one of the tables which they own.
1800+
Because it is usually undesirable to allow one role to freely assume
1801+
the privileges of another, this option should be avoided unless user
1802+
security within the database is of no concern.
1803+
</para>
1804+
17881805
<para>
17891806
On the publisher, privileges are only checked once at the start of a
17901807
replication connection and are not re-checked as each change record is read.

‎doc/src/sgml/ref/alter_subscription.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/alter_subscription.sgml
+2-1
Original file line numberDiff line numberDiff line change
@@ -224,7 +224,8 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
224224
<link linkend="sql-createsubscription-with-binary"><literal>binary</literal></link>,
225225
<link linkend="sql-createsubscription-with-streaming"><literal>streaming</literal></link>,
226226
<link linkend="sql-createsubscription-with-disable-on-error"><literal>disable_on_error</literal></link>,
227-
<link linkend="sql-createsubscription-with-password-required"><literal>password_required</literal></link>, and
227+
<link linkend="sql-createsubscription-with-password-required"><literal>password_required</literal></link>,
228+
<link linkend="sql-createsubscription-with-run-as-owner"><literal>run_as_owner</literal></link>, and
228229
<link linkend="sql-createsubscription-with-origin"><literal>origin</literal></link>.
229230
Only a superuser can set <literal>password_required = false</literal>.
230231
</para>

‎doc/src/sgml/ref/create_subscription.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_subscription.sgml
+14
Original file line numberDiff line numberDiff line change
@@ -366,6 +366,20 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
366366
</listitem>
367367
</varlistentry>
368368

369+
<varlistentry id="sql-createsubscription-with-run-as-owner">
370+
<term><literal>run_as_owner</literal> (<type>string</type>)</term>
371+
<listitem>
372+
<para>
373+
If true, all replication actions are performed as the subscription
374+
owner. If false, replication workers will perform actions on each
375+
table as the owner of that table. The latter configuration is
376+
generally much more secure; for details, see
377+
<xref linkend="logical-replication-security" />.
378+
The default is <literal>false</literal>.
379+
</para>
380+
</listitem>
381+
</varlistentry>
382+
369383
<varlistentry id="sql-createsubscription-with-origin">
370384
<term><literal>origin</literal> (<type>string</type>)</term>
371385
<listitem>

‎src/backend/catalog/pg_subscription.c

Copy file name to clipboardExpand all lines: src/backend/catalog/pg_subscription.c
+1
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,7 @@ GetSubscription(Oid subid, bool missing_ok)
7272
sub->twophasestate = subform->subtwophasestate;
7373
sub->disableonerr = subform->subdisableonerr;
7474
sub->passwordrequired = subform->subpasswordrequired;
75+
sub->runasowner = subform->subrunasowner;
7576

7677
/* Get conninfo */
7778
datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,

‎src/backend/catalog/system_views.sql

Copy file name to clipboardExpand all lines: src/backend/catalog/system_views.sql
+1-1
Original file line numberDiff line numberDiff line change
@@ -1319,7 +1319,7 @@ REVOKE ALL ON pg_replication_origin_status FROM public;
13191319
REVOKE ALL ON pg_subscription FROM public;
13201320
GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled,
13211321
subbinary, substream, subtwophasestate, subdisableonerr,
1322-
subpasswordrequired,
1322+
subpasswordrequired, subrunasowner,
13231323
subslotname, subsynccommit, subpublications, suborigin)
13241324
ON pg_subscription TO public;
13251325

‎src/backend/commands/subscriptioncmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/subscriptioncmds.c
+19-4
Original file line numberDiff line numberDiff line change
@@ -68,8 +68,9 @@
6868
#define SUBOPT_TWOPHASE_COMMIT 0x00000200
6969
#define SUBOPT_DISABLE_ON_ERR 0x00000400
7070
#define SUBOPT_PASSWORD_REQUIRED 0x00000800
71-
#define SUBOPT_LSN 0x00001000
72-
#define SUBOPT_ORIGIN 0x00002000
71+
#define SUBOPT_RUN_AS_OWNER 0x00001000
72+
#define SUBOPT_LSN 0x00002000
73+
#define SUBOPT_ORIGIN 0x00004000
7374

7475
/* check if the 'val' has 'bits' set */
7576
#define IsSet(val, bits) (((val) & (bits)) == (bits))
@@ -93,6 +94,7 @@ typedef struct SubOpts
9394
bool twophase;
9495
bool disableonerr;
9596
bool passwordrequired;
97+
bool runasowner;
9698
char *origin;
9799
XLogRecPtr lsn;
98100
} SubOpts;
@@ -151,6 +153,8 @@ parse_subscription_options(ParseState *pstate, List *stmt_options,
151153
opts->disableonerr = false;
152154
if (IsSet(supported_opts, SUBOPT_PASSWORD_REQUIRED))
153155
opts->passwordrequired = true;
156+
if (IsSet(supported_opts, SUBOPT_RUN_AS_OWNER))
157+
opts->runasowner = false;
154158
if (IsSet(supported_opts, SUBOPT_ORIGIN))
155159
opts->origin = pstrdup(LOGICALREP_ORIGIN_ANY);
156160

@@ -290,6 +294,15 @@ parse_subscription_options(ParseState *pstate, List *stmt_options,
290294
opts->specified_opts |= SUBOPT_PASSWORD_REQUIRED;
291295
opts->passwordrequired = defGetBoolean(defel);
292296
}
297+
else if (IsSet(supported_opts, SUBOPT_RUN_AS_OWNER) &&
298+
strcmp(defel->defname, "run_as_owner") == 0)
299+
{
300+
if (IsSet(opts->specified_opts, SUBOPT_RUN_AS_OWNER))
301+
errorConflictingDefElem(defel, pstate);
302+
303+
opts->specified_opts |= SUBOPT_RUN_AS_OWNER;
304+
opts->runasowner = defGetBoolean(defel);
305+
}
293306
else if (IsSet(supported_opts, SUBOPT_ORIGIN) &&
294307
strcmp(defel->defname, "origin") == 0)
295308
{
@@ -578,7 +591,7 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
578591
SUBOPT_SYNCHRONOUS_COMMIT | SUBOPT_BINARY |
579592
SUBOPT_STREAMING | SUBOPT_TWOPHASE_COMMIT |
580593
SUBOPT_DISABLE_ON_ERR | SUBOPT_PASSWORD_REQUIRED |
581-
SUBOPT_ORIGIN);
594+
SUBOPT_RUN_AS_OWNER | SUBOPT_ORIGIN);
582595
parse_subscription_options(pstate, stmt->options, supported_opts, &opts);
583596

584597
/*
@@ -681,6 +694,7 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
681694
LOGICALREP_TWOPHASE_STATE_DISABLED);
682695
values[Anum_pg_subscription_subdisableonerr - 1] = BoolGetDatum(opts.disableonerr);
683696
values[Anum_pg_subscription_subpasswordrequired - 1] = BoolGetDatum(opts.passwordrequired);
697+
values[Anum_pg_subscription_subrunasowner - 1] = BoolGetDatum(opts.runasowner);
684698
values[Anum_pg_subscription_subconninfo - 1] =
685699
CStringGetTextDatum(conninfo);
686700
if (opts.slot_name)
@@ -1115,7 +1129,8 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
11151129
supported_opts = (SUBOPT_SLOT_NAME |
11161130
SUBOPT_SYNCHRONOUS_COMMIT | SUBOPT_BINARY |
11171131
SUBOPT_STREAMING | SUBOPT_DISABLE_ON_ERR |
1118-
SUBOPT_PASSWORD_REQUIRED | SUBOPT_ORIGIN);
1132+
SUBOPT_PASSWORD_REQUIRED |
1133+
SUBOPT_RUN_AS_OWNER | SUBOPT_ORIGIN);
11191134

11201135
parse_subscription_options(pstate, stmt->options,
11211136
supported_opts, &opts);

‎src/backend/replication/logical/worker.c

Copy file name to clipboardExpand all lines: src/backend/replication/logical/worker.c
+36-10
Original file line numberDiff line numberDiff line change
@@ -2401,6 +2401,7 @@ apply_handle_insert(StringInfo s)
24012401
EState *estate;
24022402
TupleTableSlot *remoteslot;
24032403
MemoryContext oldctx;
2404+
bool run_as_owner;
24042405

24052406
/*
24062407
* Quick return if we are skipping data modification changes or handling
@@ -2425,8 +2426,13 @@ apply_handle_insert(StringInfo s)
24252426
return;
24262427
}
24272428

2428-
/* Make sure that any user-supplied code runs as the table owner. */
2429-
SwitchToUntrustedUser(rel->localrel->rd_rel->relowner, &ucxt);
2429+
/*
2430+
* Make sure that any user-supplied code runs as the table owner, unless
2431+
* the user has opted out of that behavior.
2432+
*/
2433+
run_as_owner = MySubscription->runasowner;
2434+
if (!run_as_owner)
2435+
SwitchToUntrustedUser(rel->localrel->rd_rel->relowner, &ucxt);
24302436

24312437
/* Set relation for error callback */
24322438
apply_error_callback_arg.rel = rel;
@@ -2457,7 +2463,8 @@ apply_handle_insert(StringInfo s)
24572463
/* Reset relation for error callback */
24582464
apply_error_callback_arg.rel = NULL;
24592465

2460-
RestoreUserContext(&ucxt);
2466+
if (!run_as_owner)
2467+
RestoreUserContext(&ucxt);
24612468

24622469
logicalrep_rel_close(rel, NoLock);
24632470

@@ -2546,6 +2553,7 @@ apply_handle_update(StringInfo s)
25462553
TupleTableSlot *remoteslot;
25472554
RTEPermissionInfo *target_perminfo;
25482555
MemoryContext oldctx;
2556+
bool run_as_owner;
25492557

25502558
/*
25512559
* Quick return if we are skipping data modification changes or handling
@@ -2577,8 +2585,13 @@ apply_handle_update(StringInfo s)
25772585
/* Check if we can do the update. */
25782586
check_relation_updatable(rel);
25792587

2580-
/* Make sure that any user-supplied code runs as the table owner. */
2581-
SwitchToUntrustedUser(rel->localrel->rd_rel->relowner, &ucxt);
2588+
/*
2589+
* Make sure that any user-supplied code runs as the table owner, unless
2590+
* the user has opted out of that behavior.
2591+
*/
2592+
run_as_owner = MySubscription->runasowner;
2593+
if (!run_as_owner)
2594+
SwitchToUntrustedUser(rel->localrel->rd_rel->relowner, &ucxt);
25822595

25832596
/* Initialize the executor state. */
25842597
edata = create_edata_for_relation(rel);
@@ -2630,7 +2643,8 @@ apply_handle_update(StringInfo s)
26302643
/* Reset relation for error callback */
26312644
apply_error_callback_arg.rel = NULL;
26322645

2633-
RestoreUserContext(&ucxt);
2646+
if (!run_as_owner)
2647+
RestoreUserContext(&ucxt);
26342648

26352649
logicalrep_rel_close(rel, NoLock);
26362650

@@ -2720,6 +2734,7 @@ apply_handle_delete(StringInfo s)
27202734
EState *estate;
27212735
TupleTableSlot *remoteslot;
27222736
MemoryContext oldctx;
2737+
bool run_as_owner;
27232738

27242739
/*
27252740
* Quick return if we are skipping data modification changes or handling
@@ -2750,8 +2765,13 @@ apply_handle_delete(StringInfo s)
27502765
/* Check if we can do the delete. */
27512766
check_relation_updatable(rel);
27522767

2753-
/* Make sure that any user-supplied code runs as the table owner. */
2754-
SwitchToUntrustedUser(rel->localrel->rd_rel->relowner, &ucxt);
2768+
/*
2769+
* Make sure that any user-supplied code runs as the table owner, unless
2770+
* the user has opted out of that behavior.
2771+
*/
2772+
run_as_owner = MySubscription->runasowner;
2773+
if (!run_as_owner)
2774+
SwitchToUntrustedUser(rel->localrel->rd_rel->relowner, &ucxt);
27552775

27562776
/* Initialize the executor state. */
27572777
edata = create_edata_for_relation(rel);
@@ -2778,7 +2798,8 @@ apply_handle_delete(StringInfo s)
27782798
/* Reset relation for error callback */
27792799
apply_error_callback_arg.rel = NULL;
27802800

2781-
RestoreUserContext(&ucxt);
2801+
if (!run_as_owner)
2802+
RestoreUserContext(&ucxt);
27822803

27832804
logicalrep_rel_close(rel, NoLock);
27842805

@@ -3225,13 +3246,18 @@ apply_handle_truncate(StringInfo s)
32253246
* Even if we used CASCADE on the upstream primary we explicitly default
32263247
* to replaying changes without further cascading. This might be later
32273248
* changeable with a user specified option.
3249+
*
3250+
* MySubscription->runasowner tells us whether we want to execute
3251+
* replication actions as the subscription owner; the last argument to
3252+
* TruncateGuts tells it whether we want to switch to the table owner.
3253+
* Those are exactly opposite conditions.
32283254
*/
32293255
ExecuteTruncateGuts(rels,
32303256
relids,
32313257
relids_logged,
32323258
DROP_RESTRICT,
32333259
restart_seqs,
3234-
true);
3260+
!MySubscription->runasowner);
32353261
foreach(lc, remote_rels)
32363262
{
32373263
LogicalRepRelMapEntry *rel = lfirst(lc);

‎src/bin/psql/describe.c

Copy file name to clipboardExpand all lines: src/bin/psql/describe.c
+5-3
Original file line numberDiff line numberDiff line change
@@ -6493,7 +6493,7 @@ describeSubscriptions(const char *pattern, bool verbose)
64936493
PGresult *res;
64946494
printQueryOpt myopt = pset.popt;
64956495
static const bool translate_columns[] = {false, false, false, false,
6496-
false, false, false, false, false, false, false, false};
6496+
false, false, false, false, false, false, false, false, false};
64976497

64986498
if (pset.sversion < 100000)
64996499
{
@@ -6550,8 +6550,10 @@ describeSubscriptions(const char *pattern, bool verbose)
65506550

65516551
if (pset.sversion >= 160000)
65526552
appendPQExpBuffer(&buf,
6553-
", suborigin AS \"%s\"\n",
6554-
gettext_noop("Origin"));
6553+
", suborigin AS \"%s\"\n"
6554+
", subrunasowner AS \"%s\"\n",
6555+
gettext_noop("Origin"),
6556+
gettext_noop("Run as Owner?"));
65556557

65566558
appendPQExpBuffer(&buf,
65576559
", subsynccommit AS \"%s\"\n"

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202304041
60+
#define CATALOG_VERSION_NO 202304042
6161

6262
#endif

‎src/include/catalog/pg_subscription.h

Copy file name to clipboardExpand all lines: src/include/catalog/pg_subscription.h
+4
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,9 @@ CATALOG(pg_subscription,6100,SubscriptionRelationId) BKI_SHARED_RELATION BKI_ROW
9090

9191
bool subpasswordrequired; /* Must connection use a password? */
9292

93+
bool subrunasowner; /* True if replication should execute as
94+
* the subscription owner */
95+
9396
#ifdef CATALOG_VARLEN /* variable-length fields start here */
9497
/* Connection string to the publisher */
9598
text subconninfo BKI_FORCE_NOT_NULL;
@@ -134,6 +137,7 @@ typedef struct Subscription
134137
* automatically disabled if a worker error
135138
* occurs */
136139
bool passwordrequired; /* Must connection use a password? */
140+
bool runasowner; /* Run replication as subscription owner */
137141
char *conninfo; /* Connection string to the publisher */
138142
char *slotname; /* Name of the replication slot */
139143
char *synccommit; /* Synchronous commit setting for worker */

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.