Re: dividing privileges for replication role.

Tomonari Katsumata <t(dot)katsumata1122(at)gmail(dot)com> writes:
>> Why is it better to do this with a privilege, rather than just using
>> pg_hba.conf?

> You are right.
> Handling with pg_hba.conf is an easy way.

> But I think many users think about switch over, so
> the pg_hba.conf is same on master and standby.
> it's not convinient that we have to rewrite pg_hba.conf
> whenever switch over occurs.

> In the other hand, using a privilege, although we have to prepare
> each roles before, we don't need to rewrite pg_hba.conf.

That sounds good, but if the behavior is controlled by a privilege
(ie, it's stored in system catalogs) then it's impossible to have
different settings on different slave servers --- or indeed to change
the settings locally on a slave at all. You can only change settings
on the master and let the change replicate to all the slaves. Quite
aside from whether you want to manage things like that, what happens if
your master has crashed and you find you need to change the settings on
the way to getting a slave to take over?

The crash-recovery worry is one of the main reasons that things like
pg_hba.conf aren't stored in system catalogs already. It's not always
convenient to need a running server before you can change the settings.

regards, tom lane