Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0B37E9FB618 for ; Thu, 24 May 2007 13:16:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 21246-01 for ; Thu, 24 May 2007 13:16:34 -0300 (ADT) X-Greylist: delayed 07:33:51.500868 by SQLgrey-1.7.5 Received: from infbssys.ips.cs.tu-bs.de (infbssys.ips.cs.tu-bs.de [134.169.32.1]) by postgresql.org (Postfix) with ESMTP id 1B0A59FB530 for ; Thu, 24 May 2007 13:16:33 -0300 (ADT) Received: from is38 (is38.idb.cs.tu-bs.de [134.169.32.148]) by infbssys.ips.cs.tu-bs.de (8.13.6+Sun/8.13.6) with ESMTP id l4OGGWuY003188 for ; Thu, 24 May 2007 18:16:32 +0200 (CEST) From: Thomas Mack To: pgsql-admin@postgresql.org Subject: Re: Role based database access Date: Thu, 24 May 2007 18:16:30 +0200 User-Agent: KMail/1.5.4 References: <200705241042.40007.mack@ifis.cs.tu-bs.de> <20070524135704.GA4320@alvh.no-ip.org> In-Reply-To: <20070524135704.GA4320@alvh.no-ip.org> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200705241816.30156.mack@ifis.cs.tu-bs.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/273 X-Sequence-Number: 25593 Am Donnerstag, 24. Mai 2007 15:57 schrieben Sie: > Thomas Mack wrote: > > Hello! > > > > I face a problem here with restricting access to databases to > > the owners of the database (Postgres 8.1.4). > > > > We would like any postgres user to have database access restricted > > to their own databases only. This is so, as we use postgres for > > educational purposes. > > > > I looked into pg_hba.conf, but unfortunately, when restricting > > database access in the database field, the NAME(!) of the database > > is concerned, which we do not restrict. Any user can create as > > many databases as needed, and the name may be choosen freely. > > In the database name you can specify "@file", which points to a file > containing a list of database names that the pg_hba.conf lines applies > to. One idea is to have the database creation routine put a line into > that file (which would be specific to each user). Ok, this looks reasonable. It probably also means, postgres cannot really restrict database access based on the ownership, which is not nice in this case. But probably no one cares in 'real world' situations. Thanks, Thomas Mack TU Braunschweig, Institut f=FCr Informationssysteme