Re: Define permissions at database level

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.

Thanks,
Dipti.

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

> 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
>