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

On 2012-07-27, John R Pierce <pierce@hogranch.com> wrote:

On 07/27/12 12:30 PM, hartrc wrote:

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.

after doing

grant developer to USERNAME;

you can do

alter user USERNAME set role to developer;

then when USERNAME logs it will be as if they had done
"set role to developer;"

--
⚂⚃ 100% natural