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