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 c3afe8c

Browse filesBrowse files
robertmhaasrobertmhaas
committed
Add new predefined role pg_create_subscription.
This role can be granted to non-superusers to allow them to issue CREATE SUBSCRIPTION. The non-superuser must additionally have CREATE permissions on the database in which the subscription is to be created. Most forms of ALTER SUBSCRIPTION, including ALTER SUBSCRIPTION .. SKIP, now require only that the role performing the operation own the subscription, or inherit the privileges of the owner. However, to use ALTER SUBSCRIPTION ... RENAME or ALTER SUBSCRIPTION ... OWNER TO, you also need CREATE permission on the database. This is similar to what we do for schemas. To change the owner of a schema, you must also have permission to SET ROLE to the new owner, similar to what we do for other object types. Non-superusers are required to specify a password for authentication and the remote side must use the password, similar to what is required for postgres_fdw and dblink. A superuser who wants a non-superuser to own a subscription that does not rely on password authentication may set the new password_required=false property on that subscription. A non-superuser may not set password_required=false and may not modify a subscription that already has password_required=false. This new password_required subscription property works much like the eponymous postgres_fdw property. In both cases, the actual semantics are that a password is not required if either (1) the property is set to false or (2) the relevant user is the superuser. Patch by me, reviewed by Andres Freund, Jeff Davis, Mark Dilger, and Stephen Frost (but some of those people did not fully endorse all of the decisions that the patch makes). Discussion: http://postgr.es/m/CA+TgmoaDH=0Xj7OBiQnsHTKcF2c4L+=gzPBUKSJLh8zed2_+Dg@mail.gmail.com
1 parent b0e9e4d commit c3afe8c
Copy full SHA for c3afe8c

21 files changed

+384
-60
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/alter_subscription.sgml
+9-7
Original file line numberDiff line numberDiff line change
@@ -46,10 +46,11 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
4646

4747
<para>
4848
You must own the subscription to use <command>ALTER SUBSCRIPTION</command>.
49-
To alter the owner, you must be able to <literal>SET ROLE</literal> to the
50-
new owning role. The new owner has to be a superuser.
51-
(Currently, all subscription owners must be superusers, so the owner checks
52-
will be bypassed in practice. But this might change in the future.)
49+
To rename a subscription or alter the owner, you must have
50+
<literal>CREATE</literal> permission on the database. In addition,
51+
to alter the owner, you must be able to <literal>SET ROLE</literal> to the
52+
new owning role. If the subscription has
53+
<literal>password_required=false</literal>, only superusers can modify it.
5354
</para>
5455

5556
<para>
@@ -223,7 +224,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
223224
<link linkend="sql-createsubscription-with-binary"><literal>binary</literal></link>,
224225
<link linkend="sql-createsubscription-with-streaming"><literal>streaming</literal></link>,
225226
<link linkend="sql-createsubscription-with-disable-on-error"><literal>disable_on_error</literal></link>,
226-
and <link linkend="sql-createsubscription-with-origin"><literal>origin</literal></link>.
227+
<link linkend="sql-createsubscription-with-password-required"><literal>password_required</literal></link>, and
228+
<link linkend="sql-createsubscription-with-origin"><literal>origin</literal></link>.
229+
Only a superuser can set <literal>password_required = false</literal>.
227230
</para>
228231
</listitem>
229232
</varlistentry>
@@ -244,8 +247,7 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
244247
finishes a transaction, the LSN (stored in
245248
<structname>pg_subscription</structname>.<structfield>subskiplsn</structfield>)
246249
is cleared. See <xref linkend="logical-replication-conflicts"/> for
247-
the details of logical replication conflicts. Using this command requires
248-
superuser privilege.
250+
the details of logical replication conflicts.
249251
</para>
250252

251253
<para>

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_subscription.sgml
+21-1
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
3333

3434
<para>
3535
<command>CREATE SUBSCRIPTION</command> adds a new logical-replication
36-
subscription. The subscription name must be distinct from the name of
36+
subscription. The user that creates a subscription becomes the owner
37+
of the subscription. The subscription name must be distinct from the name of
3738
any existing subscription in the current database.
3839
</para>
3940

@@ -49,6 +50,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
4950
unless the subscription is initially disabled.
5051
</para>
5152

53+
<para>
54+
To be able to create a subscription, you must have the privileges of the
55+
the <literal>pg_create_subscription</literal> role, as well as
56+
<literal>CREATE</literal> privileges on the current database.
57+
</para>
58+
5259
<para>
5360
Additional information about subscriptions and logical replication as a
5461
whole is available at <xref linkend="logical-replication-subscription"/> and
@@ -365,6 +372,19 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
365372
</para>
366373
</listitem>
367374
</varlistentry>
375+
376+
<varlistentry>
377+
<term><literal>password_required</literal> (<type>string</type>)</term>
378+
<listitem>
379+
<para>
380+
Specifies whether connections to the publisher made as a result
381+
of this subscription must use password authentication. This setting
382+
is ignored when the subscription is owned by a superuser.
383+
The default is <literal>true</literal>. Only superusers can set
384+
this value to <literal>false</literal>.
385+
</para>
386+
</listitem>
387+
</varlistentry>
368388
</variablelist></para>
369389

370390
</listitem>

‎doc/src/sgml/ref/drop_subscription.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/drop_subscription.sgml
+1-1
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ DROP SUBSCRIPTION [ IF EXISTS ] <replaceable class="parameter">name</replaceable
3434
</para>
3535

3636
<para>
37-
A subscription can only be dropped by a superuser.
37+
To execute this command the user must be the owner of the subscription.
3838
</para>
3939

4040
<para>

‎doc/src/sgml/user-manag.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/user-manag.sgml
+6
Original file line numberDiff line numberDiff line change
@@ -699,6 +699,12 @@ DROP ROLE doomed_role;
699699
<entry>Allow use of connection slots reserved via
700700
<xref linkend="guc-reserved-connections"/>.</entry>
701701
</row>
702+
<row>
703+
<entry>pg_create_subscription</entry>
704+
<entry>Allow users with <literal>CREATE</literal> permission on the
705+
database to issue
706+
<link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
707+
</row>
702708
</tbody>
703709
</tgroup>
704710
</table>

‎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
@@ -71,6 +71,7 @@ GetSubscription(Oid subid, bool missing_ok)
7171
sub->stream = subform->substream;
7272
sub->twophasestate = subform->subtwophasestate;
7373
sub->disableonerr = subform->subdisableonerr;
74+
sub->passwordrequired = subform->subpasswordrequired;
7475

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

‎src/backend/catalog/system_views.sql

Copy file name to clipboardExpand all lines: src/backend/catalog/system_views.sql
+1
Original file line numberDiff line numberDiff line change
@@ -1318,6 +1318,7 @@ REVOKE ALL ON pg_replication_origin_status FROM public;
13181318
REVOKE ALL ON pg_subscription FROM public;
13191319
GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled,
13201320
subbinary, substream, subtwophasestate, subdisableonerr,
1321+
subpasswordrequired,
13211322
subslotname, subsynccommit, subpublications, suborigin)
13221323
ON pg_subscription TO public;
13231324

‎src/backend/commands/alter.c

Copy file name to clipboardExpand all lines: src/backend/commands/alter.c
+24
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include "catalog/objectaccess.h"
2525
#include "catalog/pg_collation.h"
2626
#include "catalog/pg_conversion.h"
27+
#include "catalog/pg_database_d.h"
2728
#include "catalog/pg_event_trigger.h"
2829
#include "catalog/pg_foreign_data_wrapper.h"
2930
#include "catalog/pg_foreign_server.h"
@@ -235,6 +236,29 @@ AlterObjectRename_internal(Relation rel, Oid objectId, const char *new_name)
235236
aclcheck_error(aclresult, OBJECT_SCHEMA,
236237
get_namespace_name(namespaceId));
237238
}
239+
240+
if (classId == SubscriptionRelationId)
241+
{
242+
Form_pg_subscription form;
243+
244+
/* must have CREATE privilege on database */
245+
aclresult = object_aclcheck(DatabaseRelationId, MyDatabaseId,
246+
GetUserId(), ACL_CREATE);
247+
if (aclresult != ACLCHECK_OK)
248+
aclcheck_error(aclresult, OBJECT_DATABASE,
249+
get_database_name(MyDatabaseId));
250+
251+
/*
252+
* Don't allow non-superuser modification of a subscription with
253+
* password_required=false.
254+
*/
255+
form = (Form_pg_subscription) GETSTRUCT(oldtup);
256+
if (!form->subpasswordrequired && !superuser())
257+
ereport(ERROR,
258+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
259+
errmsg("password_required=false is superuser-only"),
260+
errhint("Subscriptions with the password_required option set to false may only be created or modified by the superuser.")));
261+
}
238262
}
239263

240264
/*

0 commit comments

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