Re: Define permissions at database level

Thanks. I will do testing.

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

> On 18/02/10 10:54, dipti shah wrote:
>
>> 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.
>>
>
> Sounds about right. Always test though.
>
> --
> Richard Huxton
> Archonet Ltd
>