Re: Proposal: Change of pg_trigger.tg_enabled and adding

On Jan 26, 2007, at 5:09 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>> On 1/26/2007 4:40 PM, Jim Nasby wrote:
>>> It would be nice if we had a separate role for replication services
>>> so that we weren't exposing superuser so much.
>
>> So you think about another flag in pg_shadow? Would work for me.

Not really sure if that's necessary or not... there might be better
ways to do it.

> How exactly would such a role differ from a "regular" superuser? It
> would still need an awful lot of privilege bypassing ability. I'm
> pretty dubious that you could lock it down enough to make it worth the
> trouble of supporting an additional concept.

There's two cases...

First is the role that actually sets up replication. It's going to
need a decent amount of privileges... on the origin, it will need to
add triggers to tables. Possibly create a schema as well (though, I'd
argue that that should happen when you install replication, which is
different than just adding a new table to a replication set, or
adding a new node).

On the replica, it's going to need to be able to alter tables to
disable triggers. If we want to be fancy and replicate DDL, it'd need
to be able to do that as well.

But it's important to note that we could require the user to grant
those abilities specifically to the replication admin role. Maybe not
what we actually want, but it's something to consider.

The second case is the role that's actually replicating data. It will
need to INSERT/UPDATE/DELETE on replica tables. Presumably it will
need some rights on objects that actually implement the replication
(think objects in the _cluster_name schema in slony), but when the
node is added the replication admin role should be able to handle that.

Both of those are much more limited than a superuser is... they can't
create databases, they can't run admin functions such as
pg_cancel_backend, etc, etc.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)