X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D1983A5B09; Tue, 11 Jan 2005 22:03:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86963-08; Tue, 11 Jan 2005 22:03:24 +0000 (GMT) Received: from lax-gate4.raytheon.com (lax-gate4.raytheon.com [199.46.200.233]) by svr1.postgresql.org (Postfix) with ESMTP id 6A70C3A5AC8; Tue, 11 Jan 2005 22:03:23 +0000 (GMT) Received: from ds02t00.directory.ray.com (ds02t00.directory.ray.com [147.25.154.117]) by lax-gate4.raytheon.com (8.12.10/8.12.10) with ESMTP id j0BM3ETW018174; Tue, 11 Jan 2005 14:03:16 -0800 (PST) Received: from ds02t00 (localhost [127.0.0.1]) by ds02t00.directory.ray.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id j0BM33Ic029765; Tue, 11 Jan 2005 22:03:06 GMT Received: from ds02t00.directory.ray.com with LMTP by ds02t00 (2.0.6/sieved-2-0-build-559); Tue, 11 Jan 2005 22:03:02 +0000 Received: from notesserver5.ftw.us.ray.com (notesserver5.ftw.us.ray.com [151.168.145.35]) by ds02t00.directory.ray.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id j0BM1RpW029393 sender Richard_D_Levine@raytheon.com; Tue, 11 Jan 2005 22:01:28 GMT Subject: Re: Limiting user privileges To: Bruno Wolff III Cc: Michael Fuhr , pgsql-admin@postgresql.org, pgsql-admin-owner@postgresql.org, Tad Marko X-Mailer: Lotus Notes Release 5.0.8 June 18, 2001 Message-ID: From: Richard_D_Levine@raytheon.com Date: Tue, 11 Jan 2005 16:51:48 -0500 X-MIMETrack: Serialize by Router on NotesServer5/HDC(Release 6.5.2|June 01, 2004) at 01/11/2005 05:01:29 PM MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII X-SPAM: 0.00 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.284 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200501/77 X-Sequence-Number: 15936 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 To: Tad Marko Sent by: cc: Michael Fuhr , 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 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