Return-Path: aoki
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA08298; Mon, 22 Mar 93 16:10:46 -0800
Message-Id: <9303230010.AA08298@postgres.Berkeley.EDU>
From: aoki@postgres.berkeley.edu (Paul M. Aoki)
Subject: Re: read/write access to other databases
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: Your message of Mon, 22 Mar 93 15:53:35 -0500 
	     <9303221553.aa29824@concour.cs.concordia.ca> 
Date: Mon, 22 Mar 1993 16:11:00 -0800
Sender: aoki@postgres.Berkeley.EDU

Francois Lambert <lambert@cs.concordia.ca> writes:,
> I have created users w/o privileges (can't create user, can't create
> database) and a database for each user.  I turns out users can query each 
> others' databases and can even delete information from another user's table. 
> Is this the way it was meant to be?

sure.  up to 4.1, there have been no protection mechanisms at all.  as
shipped, 4.1 preserves these semantics, but you can change that.

> Is there a way to make each database (and/or tables) private to its owner?

for tables, yes.  read the 4.1 documentation under "change acl" and the
sections to which it refers.  you can turn on access controls for 
individual classes.

for databases, yes -- but not in a straightforward way.  a given *set* of 
databases (called an "installation" in the documentation, and corresponding 
to one $PGDATA directory with its associated postmaster) is defined to 
have have a set of valid users.  there are currently two approaches to
protecting a given database:

	* create multiple installations (i.e., run several postmasters
	using different $PGDATA directories).  this is the firewall method.
	personally, i would do this if i *really cared* about isolating 
	groups of users, since this also protects you from other people's 
	crashing backends (which will in turn crash your backend).

	* protect the tables in a database using ACLs.

and even if you do all of that, i can still easily spoof your postmaster 
unless you run a network authentication system (like kerberos).

since you're the second person to mention it, access control for databases 
(a shorthand for protecting a set of tables) may find its way into the next 
release.  no promises, of course -- i may actually get a life between now
and then. :-)

there are lots of security holes in the protection scheme in 4.1.  most
of the known holes will go away as we deprecate (well, remove) certain 
"features" of the system, but don't ever expect to have an orange-book 
postgres. :-)  for now (unless you run as multiple installations), think 
of these mechanisms as "keeping the users from hosing each other by 
accident" rather than "keeping the users out of each other's data".
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki