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 527F03A5683 for ; Mon, 10 Jan 2005 18:31:28 +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 23839-01 for ; Mon, 10 Jan 2005 18:31:19 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 8D27F3A5509 for ; Mon, 10 Jan 2005 18:31:18 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0AIVB5R048935 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Mon, 10 Jan 2005 11:31:14 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0AIVBN6005191; Mon, 10 Jan 2005 11:31:11 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0AIVBNp005190; Mon, 10 Jan 2005 11:31:11 -0700 (MST) (envelope-from mfuhr) Date: Mon, 10 Jan 2005 11:31:11 -0700 From: Michael Fuhr To: Tad Marko Cc: pgsql-admin@postgresql.org Subject: Re: Limiting user privileges Message-ID: <20050110183111.GA2279@winnie.fuhr.org> References: <1105377550.11245.0.camel@boojiboy.metrosplash.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1105377550.11245.0.camel@boojiboy.metrosplash.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/58 X-Sequence-Number: 15917 On Mon, Jan 10, 2005 at 11:19:10AM -0600, Tad Marko wrote: > I'm coming to postgresql from mysql, so I have a few preconceived > notions that are causing me some trouble. Preconceived notions will do that. Execute a DROP PRECONCEIVED NOTIONS statement. > Specifically, I'm trying to create users that have limited privileges > on one database. It appears that by default, a new user has a lot > of privileges all over all the databases. In typical default configurations users can connect to any database and they have CREATE and USAGE privileges on the "public" schema. See the "Schemas" section in the "Data Definition" chapter of the documentation, in particular "The Public Schema" and "Schemas and Privileges." See also the "Client Authentication" chapter, the "Template Databases" section in the "Managing Databases" chapter, and the documentation for GRANT and REVOKE under "SQL Commands" in the "Reference" part. > 1) How can I remove all privileges from all databases for a given user? A user's ability to connect to a database is based on pg_hba.conf (see the "Client Authentication" chapter), so you can configure that file to define which users can connect to which databases. Within each database you can use GRANT and REVOKE to set privileges on the "public" schema and on other database objects (schemas, tables, views, etc.). For example, you could execute "REVOKE CREATE ON SCHEMA public FROM PUBLIC" to remove CREATE privilege on the "public" schema from all users (the PUBLIC keyword). You could even DROP the "public" schema if you don't need it. You can change how newly-created databases are set up by modifying the template1 database. See "Template Databases" as mentioned above. > 2) If I put back insert, delete and table creation privileges only on > one database for a given user, will that user magically have all sorts > of privileges on any databases that are created after doing #1 above? No, not if you've prevented the user from connecting to the other databases or revoked whatever privileges they had there (or created the database after modifying template1 so users have limited privileges by default). -- Michael Fuhr http://www.fuhr.org/~mfuhr/