pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard_D_Levine@raytheon.com
To: Bruno Wolff III <bruno@wolff.to>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-admin@postgresql.org, pgsql-admin-owner@postgresql.org, Tad Marko <tmarko@metrosplash.com>
Subject: Re: Limiting user privileges
Date: Tue, 11 Jan 2005 16:51:48 -0500
Message-ID: <OFE8C52209.7771AA97-ON05256F86.0076E73C@ftw.us.ray.com> (raw)

I do this using the following:

CREATE GROUP agroup;

ALTER GROUP agroup ADD USER auser;

CREATE TABLE atable ...;

GRANT ALL ON atable TO GROUP agroup;

If you grant permissions to some set of groups on all tables at schema
creation time, then you only need to alter the groups to add and remove
users.

I generally create three groups, one that can modify the schema, one that
can modify the data, and one that can only read the data.

CREATE GROUP admins;

CREATE GROUP writers;

CREATE GROUP readers;

GRANT ALL ON atable TO GROUP admins;

GRANT SELECT, INSERT, UPDATE, DELETE, TEMPORARY ON atable TO GROUP writers;
-- you may want to consider EXECUTE and USAGE also, depending on what your
users are doing.

GRANT SELECT ON atable TO GROUP readers;

ALTER GROUP admins ADD USER smartguy;

ALTER GROUP writers ADD USER mostlyharmless;

ALTER GROUP readers ADD USER idiot;


                                                                                                                                           
                      Bruno Wolff III                                                                                                      
                      <bruno@wolff.to>             To:       Tad Marko <tmarko@metrosplash.com>                                            
                      Sent by:                     cc:       Michael Fuhr <mike@fuhr.org>, pgsql-admin@postgresql.org                      
                      pgsql-admin-owner@pos        Subject:  Re: [ADMIN] Limiting user privileges                                          
                      tgresql.org                                                                                                          
                                                                                                                                           
                                                                                                                                           
                      01/11/2005 04:18 PM                                                                                                  
                                                                                                                                           
                                                                                                                                           




On Tue, Jan 11, 2005 at 14:26:15 -0600,
  Tad Marko <tmarko@metrosplash.com> wrote:
>
> I can
>
> GRANT ALL ON a_specific_table TO user
>
> but I can't figure out how to simply give some privilege to a user on
> all tables.

You can't do it with a single GRANT statement. You need to write a script
or function to do it.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend






view thread (8+ messages)  latest in thread

Message-ID: <OFE8C52209.7771AA97-ON05256F86.0076E73C@ftw.us.ray.com>
Permalink:  ../OFE8C52209.7771AA97-ON05256F86.0076E73C@ftw.us.ray.com/
Also on:    postgresql.org/message-id/OFE8C52209.7771AA97-ON05256F86.0076E73C@ftw.us.ray.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: Richard_D_Levine@raytheon.com, bruno@wolff.to, mike@fuhr.org, tmarko@metrosplash.com
  Subject: Re: Limiting user privileges
  In-Reply-To: <OFE8C52209.7771AA97-ON05256F86.0076E73C@ftw.us.ray.com>
  References: <OFE8C52209.7771AA97-ON05256F86.0076E73C@ftw.us.ray.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox