On Thu, Apr 4, 2019 at 6:11 AM Michael Paquier <mich...@paquier.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/>
