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(at)wolff(dot)to> To: Tad Marko <tmarko(at)metrosplash(dot)com>
Sent by: cc: Michael Fuhr <mike(at)fuhr(dot)org>, pgsql-admin(at)postgresql(dot)org
pgsql-admin-owner(at)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(at)metrosplash(dot)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