Re: Default permissisons from schemas

On 1/24/07, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Sure, all the objects in a given schema should be owned by a role which
> all the admins of that schema are members of. I really see this as a
> sensible step from ACLs since ownership implies additional permissions
> (which can't otherwise be granted, otherwise it wouldn't matter so much).
>
> We do this quite a bit and it's annoying when someone forgets to change
> the ownership of something they created. Since we do this largely on a
> per-schmea basis (and different schemas have different admin groups,
> which can overlap) getting people to remember to 'set role' doesn't seem
> likely to practically improve things much. I've considered writing a
> cron job to periodically fix all the ownerships and permissions but then
> having actual exceptions becomes a pain.

In every place I've worked, table permissions/ownership has been a
problem...it's tedious and error-prone to catch permission
errors...even with regression testing. My solution has always been to
write pl/pgsql functions to do exactly that. It would be very nice
not to have to do that however and have things auto-magically be set
when you create them. Table rights almost always follow broad rules
so it only natural to integrate that with schemas somehow...but
admittedly it is awkward to put it into GRANT (and I've thought alot a
bout.

It seems like an alternate solution to this problem is to be able to
hook triggers to pg_class and pg_namepace, so you can fire grant
statements of your choosing when tables gets added/removed based on
your own logic. Or, since triggers are broadly not allowed to system
catalogs, maybe a trigger-ish sql callback could be added to the
schema so that when objects inside are added/removed, you have the
ability to inject your own sql. This gets you to the same place
without hacking up grant or adding acl.

merlin