Re: Limiting user privileges

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