PostgreSQL 16 preview - 新增pg_create_subscription 内置role, 同时精细化 subscription apply 时的角色选择 (subscription owner OR table owner)
digoal
2023-04-06
PostgreSQL , PolarDB , pg_create_subscription , apply , 权限 , owner , table owner , subscription owner , run_as_owner
PostgreSQL 16 preview - 新增pg_create_subscription 内置role, 同时精细化 subscription apply时的角色配置(subscription owner OR table owner) 提升安全性!
1、新增 pg_create_subscription 内置role
Add new predefined role pg_create_subscription.
author Robert Haas <rhaas@postgresql.org>
Thu, 30 Mar 2023 15:37:19 +0000 (11:37 -0400)
committer Robert Haas <rhaas@postgresql.org>
Thu, 30 Mar 2023 15:37:19 +0000 (11:37 -0400)
commit c3afe8cf5a1e465bd71e48e4bc717f5bfdc7a7d6
tree 66f7e14d0c74bf8c85fe6444235db1fad0eebe30 tree
parent b0e9e4d76ca212d429d9cd615ae62ac73a9a89ba commit | diff
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
2、apply时的MOD, TRUNCATE操作采用对应的table owner, 而不是 "这个 subscription 的owner".
所以创建 subscription 的role还必须有对应表的owner的set role权限. 也就是切换到table对应owner role去执行MOD, TRUNCATE操作.
Perform logical replication actions as the table owner.
author Robert Haas <rhaas@postgresql.org>
Tue, 4 Apr 2023 15:25:23 +0000 (11:25 -0400)
committer Robert Haas <rhaas@postgresql.org>
Tue, 4 Apr 2023 15:25:23 +0000 (11:25 -0400)
commit 1e10d49b65d6c26c61fee07999e4cd59eab2b765
tree ae1f0b88eda66ecbe945d92b62ffe1fbe41f661a tree
parent 3077324b03e81ab9894f1f510952273b78d98569 commit | diff
Perform logical replication actions as the table owner.
Up until now, logical replication actions have been performed as the
subscription owner, who will generally be a superuser. Commit
cec57b1a0fbcd3833086ba686897c5883e0a2afc documented hazards
associated with that situation, namely, that any user who owns a
table on the subscriber side could assume the privileges of the
subscription owner by attaching a trigger, expression index, or
some other kind of executable code to it. As a remedy, it suggested
not creating configurations where users who are not fully trusted
own tables on the subscriber.
Although that will work, it basically precludes using logical
replication in the way that people typically want to use it,
namely, to replicate a database from one node to another
without necessarily having any restrictions on which database
users can own tables. So, instead, change logical replication to
execute INSERT, UPDATE, DELETE, and TRUNCATE operations as the
table owner when they are replicated.
Since this involves switching the active user frequently within
a session that is authenticated as the subscription user, also
impose SECURITY_RESTRICTED_OPERATION restrictions on logical
replication code. As an exception, if the table owner can SET
ROLE to the subscription owner, these restrictions have no
security value, so don't impose them in that case.
Subscription owners are now required to have the ability to
SET ROLE to every role that owns a table that the subscription
is replicating. If they don't, replication will fail. Superusers,
who normally own subscriptions, satisfy this property by default.
Non-superusers users who own subscriptions will need to be
granted the roles that own relevant tables.
Patch by me, reviewed (but not necessarily in its entirety) by
Jelte Fennema, Jeff Davis, and Noah Misch.
Discussion: http://postgr.es/m/CA+TgmoaSCkg9ww9oppPqqs+9RVqCexYCE6Aq=UsYPfnOoDeFkw@mail.gmail.com
+ To create a subscription, the user must have the privileges of the
+ the <literal>pg_create_subscription</literal> role, as well as
+ <literal>CREATE</literal> privileges on the database.
+ The subscription apply process will, at a session level, run with the
+ privileges of the subscription owner. However, when performing an insert,
+ update, delete, or truncate operation on a particular table, it will switch
+ roles to the table owner and perform the operation with the table owner's
+ privileges. This means that the subscription owner needs to be able to
+ <literal>SET ROLE</literal> to each role that owns a replicated table.
3、增加run_as_owner选项, 用在create sub和alter sub语法中.
告诉这个sub是不是以table owner来回放mod和truncate.
Add a run_as_owner option to subscriptions.
author Robert Haas <rhaas@postgresql.org>
Tue, 4 Apr 2023 16:03:03 +0000 (12:03 -0400)
committer Robert Haas <rhaas@postgresql.org>
Tue, 4 Apr 2023 16:03:03 +0000 (12:03 -0400)
commit 482675987bcdffb390ae735cfd5f34b485ae97c6
tree 889d1ea562d20bf291620e8df982d09f1852aa64 tree
parent 1e10d49b65d6c26c61fee07999e4cd59eab2b765 commit | diff
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
+ <varlistentry id="sql-createsubscription-with-run-as-owner">
+ <term><literal>run_as_owner</literal> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ If true, all replication actions are performed as the subscription
+ owner. If false, replication workers will perform actions on each
+ table as the owner of that table. The latter configuration is
+ generally much more secure; for details, see
+ <xref linkend="logical-replication-security" />.
+ The default is <literal>false</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
