Re: pg_rewind vs superuser

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?