Thread: Postgresql Developer Privileges

Postgresql v9.1.4
SUSE Linux Enterprise Server SP2

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
Developers do not own the objects themselves

Thank You
Rob




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-Developer-Privileges-tp5718244.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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