Skip site navigation (1) Skip section navigation (2)

Re: Define permissions at database level

From: dipti shah <shahdipti1980(at)gmail(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Define permissions at database level
Date: 2010-02-18 10:23:54
Message-ID: d5b05a951002180223m2ccc12b7p19150bfe571b8803@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Richard HuxtonDate: 2010-02-18 10:31:37
Subject: Re: Define permissions at database level
Previous:From: Jayadevan MDate: 2010-02-18 10:13:28
Subject: Re: How to select all columns and insert into other table

pgsql-general by date

Next:From: Antonio Gom├ęz SotoDate: 2010-02-18 10:28:21
Subject: Re: define transaction within pg/psql. Necessary?
Previous:From: Richard HuxtonDate: 2010-02-18 10:07:26
Subject: Re: define transaction within pg/psql. Necessary?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group