Thread: Define permissions at database level
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.
Thanks,
Dipti
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@archonet.com> wrote:
1. Place users into appropriate groups (makes it easier to manage later). Note that groups and users are actually both just roles.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.
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