Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA00540 for ; Wed, 5 May 1999 12:44:18 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.prevuenet.com (prevuepo1.uvsg.com [192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id J5RFYS16; Wed, 5 May 1999 11:43:56 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2232.9) id ; Wed, 5 May 1999 11:46:58 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC14548B@lambic.prevuenet.com> From: Michael J Davis To: "'greg@malthouse.demon.co.uk'" , pgsql-sql@postgreSQL.org Subject: RE: [SQL] User and Groups Date: Wed, 5 May 1999 11:39:42 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2232.9) Content-Type: text/plain; charset="iso-8859-1" For each group in pg_group you need to do the following: UPDATE pg_group set grolist = '{501,514,502,503,504}' where grosysid = 1; Where 501 - 504 is a list of pg_user.usesysid that should belong to the group. This really needs to be added to the documentation and "create user" needs to be fixed to automatically do this. -----Original Message----- From: Greg Frith [SMTP:greg@malthouse.demon.co.uk] Sent: Wednesday, May 05, 1999 8:35 AM To: pgsql-sql@postgreSQL.org Subject: [SQL] User and Groups Hi, I'm having some problems trying to get my head around how users and groups work. I have 2 groups of users: client & consultants. I create these groups as follows:- /* Group : create group consultants */ INSERT INTO pg_group VALUES ('consultants', '100'); INSERT INTO pg_group VALUES ('clients', '200'); I then create some tables and set the permissions on these tables as follows:- REVOKE ALL ON clients, clientaddr, consultants, consultantaddr, systemconstants, clients_consultants, tasks, client_extras, timesheet, schedule, invoice, invoice_items, pg_user, pg_shadow FROM GROUP clients; GRANT ALL ON clients, clientaddr, consultants, consultantaddr, systemconstants, clients_consultants, tasks, client_extras, timesheet, schedule, invoice, invoice_items TO GROUP consultants; Now I create a user:- testdb=> CREATE USER paul IN GROUP consultants; CREATE USER I login as this user and try a select on a table that should have permissions set:- testdb=> select * from consultants; NOTICE: in_group: group 100 not found ERROR: consultants: Permission denied. I can't understand this - should I insert the user id of each consultant user into grolist of pg_group where groname = consultants? TOA -- --------------------------- Greg Frith - University Of Leeds : School of Computer Studies