Subject: read/write access to other databases

From: Francois Lambert <[email-masked]>
Date: Mon, 22 Mar 93 15:53:35 -0500

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?  Is there a way to make each database 
(and/or tables) private to its owner?

Thank you,

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Francois Lambert                  | Office: H961-43                        |
| Programmer/Analyst                | Phone : 848-3037                       |
| Computer Science Department       | email : [email-masked],       |
| Concordia University              |         [email-masked]      |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 22 Mar 1993 16:11:00 -0800

Francois Lambert <[email-masked]> writes:,
> 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  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki