Re: Define permissions at database level

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