Re: pg_rewind vs superuser

On Thu, Apr 4, 2019 at 6:11 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> 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/

Hah. I usually read your blog, but I had forgotten about that one :)

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?
>

Looks good. Maybe we should list the "role having sufficient permissions"
before superuser, "just because", but not something I feel strongly about.

The part about CHECKPOINT also looks pretty good, but that's entirely
unrelated, right? :)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>