On 9/23/23 03:57, Jeff Davis wrote:
IIUC there is really one use case here, which is for superuser to
define a subscription including the connection, and then change the
owner to a non-superuser to actually run it (without being able to
touch the connection string itself). I'd just document that in its own
section, and mention a few caveats / mistakes to avoid. For instance,
when the superuser is defining the connection, don't forget to set
password_required=false, so that when you reassign to a non-superuser
then the connection doesn't break.
Hi,
I tried adding a section in "Logical Replication > Subscription" with
the text you suggested and links in the CREATE / ALTER SUBSRIPTION commands.
Is it better ?
--
Benoit Lobréau
Consultant
http://dalibo.com
From f3f1b0ce8617971b173ea901c9735d8357955aa2 Mon Sep 17 00:00:00 2001
From: benoit <benoit.lobr...@dalibo.com>
Date: Thu, 12 Oct 2023 16:45:11 +0200
Subject: [PATCH] Doc patch for password_required
Add documentation regarding non-superuser subscriptions with
password_required=true.
---
doc/src/sgml/logical-replication.sgml | 32 +++++++++++++++++++++++
doc/src/sgml/ref/alter_subscription.sgml | 3 ++-
doc/src/sgml/ref/create_subscription.sgml | 3 ++-
3 files changed, 36 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 3b2fa1129e..c3faaf88cd 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -329,6 +329,38 @@
</para>
</sect2>
+ <sect2 id="logical-replication-subscription-password-required">
+ <title>Password required</title>
+
+ <para>
+ <literal>password_required</literal> is a subscription parameter which specifies whether
+ connections to the publisher made as a result of this subscription must
+ use password authentication. This setting is ignored when the subscription
+ is owned by a superuser and set to <literal>true</literal> by default.
+ </para>
+
+ <para>
+ If you want to have a subscription managed by a non-superuser with a connection string without
+ a password, you have to set <literal>password_required = false</literal> before transferring it's
+ ownership. In that case, only superusers can modify the subscription.
+ <programlisting>
+test_pub=# CREATE SUBSCRIPTION test_sub CONNECTION 'host=somehost port=5432 user=repli dbname=tests_pub' PUBLICATION test_pub WITH (password_required=false);
+CREATE SUBSCRIPTION
+test_pub=# ALTER SUBSCRIPTION test_sub OWNER TO new_sub_owner;
+ALTER SUBSCRIPTION
+ </programlisting>
+ </para>
+
+ <warning>
+ <para>
+ If the connection string doesn't contain a password or the publication
+ side doesn't require a password during authentication and you have set
+ <literal>password_required = true</literal>before transferring ownership,
+ the subscription will start failing.
+ </para>
+ </warning>
+ </sect2>
+
<sect2 id="logical-replication-subscription-examples">
<title>Examples: Set Up Logical Replication</title>
diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index a85e04e4d6..e061c96937 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -50,7 +50,8 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<literal>CREATE</literal> permission on the database. In addition,
to alter the owner, you must be able to <literal>SET ROLE</literal> to the
new owning role. If the subscription has
- <literal>password_required=false</literal>, only superusers can modify it.
+ <literal>password_required=false</literal>, only superusers can modify it
+ (See <xref linkend="logical-replication-subscription-password-required"/>).
</para>
<para>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index c1bafbfa06..33ad3d12c7 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -361,7 +361,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
of this subscription must use password authentication. This setting
is ignored when the subscription is owned by a superuser.
The default is <literal>true</literal>. Only superusers can set
- this value to <literal>false</literal>.
+ this value to <literal>false</literal>
+ (See <xref linkend="logical-replication-subscription-password-required"/>).
</para>
</listitem>
</varlistentry>
--
2.41.0
