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