Re: Define permissions at database level

Okay then I think below works:

1. Revoke permission ALL permissions from PUBLIC on schema.

REVOKE ALL ON ALL TABLES IN SCHEMA mySchema FROM PUBLIC;

2. Give store procedure for creating table with SECURITY DEFINER marked
so that all tables owner will be "postgres" user.
3. Grant SELECT permission to required group on created table.
4. Give store procedure for droping the table with SECURITY DEFINER
marked so that droping will happen in the context of "postgres" user.

I think above will not allow anyone to create and/or drop tables directly
without using store procedures.

Please let me know if I am missing anything.

Thanks for being there.
Dipti

On Thu, Feb 18, 2010 at 4:09 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:

> On 18/02/10 10:34, dipti shah wrote:
>
>> Actually, I don't want table owners to drop the table using DROP command
>> directly. They have to use stored procedure to drop the table.
>>
>
> Then don't let them own the table. Or rather, the role they log in to the
> database as shouldn't.
>
> --
> Richard Huxton
> Archonet Ltd
>