[Babase] Babase security model, please comment

Karl O. Pinc babase@www.eco.princeton.edu
Tue, 12 Oct 2004 22:55:11 -0500


Hi,

This is what I'm thinking of for security now that
I know more about schemas (just introduced in our
postgresql release, IIRC.)

From: http://www.postgresql.org/docs/7.3/static/ddl-schemas.html
-----------------------------------------
  A database contains one or more named schemas, which in turn contain 
tables. Schemas also contain other kinds of named objects, including 
data types, functions, and operators. The same object name can be used 
in different schemas without conflict; for example, both schema1 and 
myschema may contain tables named mytable. Unlike databases, schemas 
are not rigidly separated: a user may access objects in any of the 
schemas in the database he is connected to, if he has privileges to do 
so.

There are several reasons why one might want to use schemas:

     *

       To allow many users to use one database without interfering with 
each other.
     *

       To organize database objects into logical groups to make them 
more manageable.
     *

       Third-party applications can be put into separate schemas so 
they cannot collide with the names of other objects. 
Schemas are analogous to directories at the operating system level, 
except that schemas cannot be nested.
------------------------------------------

You can reference tables in different schemas in a single query, 
joining them on sname or whatever.
This is not true of tables in different databases.


Here's the new plan.

We'll have 3 databases:

babase         live data
babase_test    developers (me) break things here
babase_copy    more or less static test data, db users break things here

I imagine we periodicly copy babase into babase_test and babase_copy.

Mostly, everybody does everything in the babase database, but if you
want to test something the babase_copy db is available for mucking
about without fear of data destruction.

Each database has 3 schemas:

babase         the real babase data
sandbox        tables that are used by Susan and Jeanne but haven't 
made it into babase
playpen        tables made by students and staff for anything at all

The idea here is that there seem to be 3 degrees of 'officialness',
which corresponds to the 3 schemas.  Totally babase, documented and all,
goes in babase.  Real scientific stuff that probably should be in 
babase eventually
but isn't now goes in sandbox.  Everything else goes in the
playpen schema.

If desired, we can give postdocs or grad students their own
schemas, like the sandbox schema shared by Susan and Jeanne,
so that they don't step on each other's toes in the playpen.
I expect that undergrads just have to live with each other
in the playpen.

There are 3 groups of users:

babase_editors
   This is the core group, everybody reading this now.
People in this group can change the data in the babase schema.
People in this group can do anything in the sandbox and playpen
schemas.  They can make new tables, delete tables, make functions
and triggers, whatever.

babase_readers
   This group can only see but not change the data in the babase
and sandbox schemas.  They can do anything in the playpen
schema.  Postdocs, grad students, and undergrads are in
this group.

babase_public
   This is the public at large.  I expect they'll be able to
see but not change some subset of the babase schema
and do nothing else.  Really, this is not a group of
users but a single special user id that is shared
by the public at large.

There is also the special user "babase_admin" that is
database owner (god) in all the databases.  Right now I have the 
password
and Steph does too because she needs it to run the conversion.
In the future Jeanne and Susan should have the password
(or not, they can always get Hunter to reset it) and I should
be the only one who actually connects to the database
as this user.

I thought about having another group that, unlike babase_public,
would be able to view everything in all the schemas, but change
nothing.  But it seemed to me that anybody that's given
access to babase could very likely want to create tables
that they keep over time, so this would not be so useful.
(Anybody can make temporary tables that go away when
they logout.  These are useful for storing intermediate
results in analysis, but obviously cannot be used to
keep stuff from day-to-day.)  I'm thinking even Jeanne's
undergrads should have permission to write in the playpen.

(FWIW, if the same name is used for an object in multiple schemas, then 
the
visiblity is first babase, then sandbox, then playpen.  So, for
example, if the table foo is in both babase and sandbox,
then "SELECT * FROM foo;" selects from the babase table.
To get the sandbox table you have to write "SELECT * FROM
sandbox.foo;".)

Is this all clear?  Does it make sense?  I don't want
to setup complication we're not going to use, so
will we use it?  (Except for babase_public, which we have
yet to work out the details of.)  Any suggestions?

Please comment.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein