On Wed, Apr 03, 2019 at 11:28:50AM +0200, Magnus Hagander wrote: > As pointed out by Michael Banck as a comment on my blogpost, the pg_rewind > documentation says it requires superuser permissions on the remote server. > > Is that really so, though? I haven't tested it, but from a quick look at > the code it looks like it needs pg_ls_dir(), pg_stat_file() and > pg_read_binary_file(), all, of which are independently grantable. > > Or am I missing something?
Somebody I heard of has mentioned that stuff on his blog some time ago: https://paquier.xyz/postgresql-2/postgres-11-superuser-rewind/ And what you need to do is just that: CREATE USER rewind_user LOGIN; GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewind_user; GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewind_user; GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewind_user; GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewind_user; I think that we should document that and back-patch, as now the docs only say that a superuser should be used, but that is wrong. At the same time, let's also document that we need to use a checkpoint on the promoted standby so as the control file gets a refresh and pg_rewind is able to work properly. I promised that some time ago and got reminded of that issue after seeing this thread... What do you think about the attached? -- Michael
diff --git a/doc/src/sgml/ref/pg_rewind.sgml b/doc/src/sgml/ref/pg_rewind.sgml
index d98406c420..45425141fb 100644
--- a/doc/src/sgml/ref/pg_rewind.sgml
+++ b/doc/src/sgml/ref/pg_rewind.sgml
@@ -156,8 +156,10 @@ PostgreSQL documentation
<para>
Specifies a libpq connection string to connect to the source
<productname>PostgreSQL</productname> server to synchronize the target with.
- The connection must be a normal (non-replication) connection
- with superuser access. This option requires the source
+ The connection must be a normal (non-replication) connection with a
+ superuser role or a role having sufficient permissions to execute the
+ functions used by <application>pg_rewind</application> on the source
+ server (see Notes section for details). This option requires the source
server to be running and not in recovery mode.
</para>
</listitem>
@@ -246,6 +248,30 @@ PostgreSQL documentation
<refsect1>
<title>Notes</title>
+ <para>
+ When executing <application>pg_rewind</application> using an online
+ cluster as source, a role having sufficient permissions to execute the
+ functions used by <application>pg_rewind</application> on the source
+ cluster can be used instead of a superuser. Here is how to create such
+ a role, named <literal>rewind_user</literal> here:
+<programlisting>
+CREATE USER rewind_user LOGIN;
+GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewind_user;
+GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewind_user;
+GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewind_user;
+GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewind_user;
+</programlisting>
+ </para>
+
+ <para>
+ When executing <application>pg_rewind</application> using an online
+ cluster as source which has been recently promoted, it is necessary
+ to execute a <command>CHECKPOINT</command> after promotion so as its
+ control file reflects up-to-date timeline information, which is used by
+ <application>pg_rewind</application> to check if the target cluster
+ can be rewound using the designated source cluster.
+ </para>
+
<refsect2>
<title>How it works</title>
signature.asc
Description: PGP signature
