Thread: Define permissions at database level

On 18/02/10 08:53, dipti shah wrote:
> Hi,
>
> Is it possible to define the permissions at database level such that no
> users(except postgres) can execute DROP, ALTER, TRUNCATE commands directily?
> Users have to use the given stored procedures.

1. Place users into appropriate groups (makes it easier to manage
later). Note that groups and users are actually both just roles.

2. Use GRANT/REVOKE to restrict what those users can do.

3. Write your "alter table" function owned by user "postgres" and make
sure it's marked "SECURITY DEFINER".

http://www.postgresql.org/docs/8.4/static/user-manag.html
http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

--
   Richard Huxton
   Archonet Ltd

On 18/02/10 10:23, dipti shah wrote:
> Thanks Richard. That makes sense. If I want to restrict DROP for any table
> then do I need to REVOKE permissions individually on tables.
>
>      Revoke DROP ON MyTable from PUBLIC;
>
> I want to avoid doing it so I am wondering if I can define/grant the
> permission at database level so that nousers can directly use any commands
> like CREATE, UPDATE, ALTER or DROP. They have to use stored procedure. They
> can only use SELECT. Nothing else.

Only table owners can drop them.

You can deny permission to a whole schema, but not set permissions on
everything in it in one go. However there are various shell-scripts and
plpgsql functions that let you set permissions on groups of tables in
one go. They should show up easily enough through googling.

--
   Richard Huxton
   Archonet Ltd

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

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