Re: Define permissions at database level - Mailing list pgsql-general

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