Re: Postgresql Developer Privileges

On 07/27/12 12:30 PM, hartrc wrote:
> In my agency we have application developers who do most of the database
> design/development themselves (not my choice) in the development environment
> but do not administer the database. Therefore I want developers to be able
> to create & drop tables, create & drop indexes, views, triggers, procedures
> sequences etc. But I don’t want them to be able to create login roles or
> shutdown the database or drop schemas.
> Basically they need to be a “power user” but definitely not a superuser. The
> main issue I’m having in Postgresql is that I can give them permission to
> create objects in a schema but not drop objects if they are not the owner. I
> don’t want all developers to use a shared login role or have access to a
> superuser account. Also I don’t think it makes sense for the developers to
> really own the object themselves.
> Is there a way to achieve the following?
>
> Basically each developer has their own login role
> Developers can create and drop objects in schemas in which they have create
> privileges granted.
> Developers can drop objects created by other developers
I think I'd create the database owned by a group role like 'developer',
and make each developer a member of this role.

> Developers do not own the objects themselves

thats a little more tricky, as AFAIK only a 'superuser' can change the
role that owns an object, by default its owned by the rule that created
it, but a user who's a member of a group can SET ROLE group; and then
any objects they create belong to that group, or they can ALTER
TABLE/VIEW/etc name OWNER TO newowner; for any role they are a member of.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast