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@xxxxxxxxxxxx> wrote:
On 18/02/10 10:34, dipti shah wrote:Then don't let them own the table. Or rather, the role they log in to the database as shouldn't.
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.
--
Richard Huxton
Archonet Ltd
