Re: Oracle Style packages on postgres - Mailing list pgsql-hackers

* Jonah H. Harris (jharris@tvi.edu) wrote:
> Is anyone going to be working on this immediately?  If so, I'd be glad
> to work with someone.  Unfortunately, I don't have the time to devote to
> taking something this big on, but I think it would be a really great
> thing to have.  Just let me know jharris@tvi.edu OR
> jonah.harris@gmail.com.  Thanks!

It strikes me as slightly unlikely that anyone will start working on
this immediately, but I can tell you it's something that some of my
users have been asking for and so once I finish off my current work on
roles I'll probably be interested in working on this.
Stephen

> Bruce Momjian wrote:
>
> >Added to TODO:
> >
> >* Add the features of packages
> >       o  Make private objects accessable only to objects in the same
> >       schema
> >       o  Allow current_schema.objname to access current schema objects
> >       o  Add session variables
> >       o  Allow nested schemas
> >
> >
> >---------------------------------------------------------------------------
> >
> >Bruce Momjian wrote:
> >
> >
> >>OK, so it seems we need:
> >>
> >>    o  make private objects accessable only to objects
> >>       in the same schema
> >>    o  Allow current_schema.objname to access current
> >>       schema objects
> >>    o  session variables
> >>    o  nested schemas?
> >>
> >>---------------------------------------------------------------------------
> >>
> >>Dave Held wrote:
> >>
> >>
> >>>>-----Original Message-----
> >>>>From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> >>>>Sent: Tuesday, May 10, 2005 8:43 AM
> >>>>To: Thomas Hallgren
> >>>>Cc: Tom Lane; rmm@sqlisor.com; pgsql-hackers@postgresql.org
> >>>>Subject: Re: [HACKERS] Oracle Style packages on postgres
> >>>>
> >>>>[...]
> >>>>I suppose.  I think we should focus on the use cases for Oracle
> >>>>packages, rather than the specific functionality it provides.
> >>>>What things do people need PostgreSQL to do that it already
> >>>>doesn't do?
> >>>>
> >>>>
> >>>Is that really the best way to go about things?  Already RDBMSes
> >>>are patchwork quilts of functionality.  Is merely adding another
> >>>patch the most elegant way to evolve the database?  The problem is
> >>>that Oracle et al are trying to be ORDBMSes and aren't exactly sure
> >>>what the best way to go is.  Instead of trying to formulate a
> >>>rational plan for what an ORDBMS should even look like, they simply
> >>>look at what would work with their existing infrastructure and tack
> >>>on features.  Then Postgres plays the copycat game.  Instead of
> >>>trying to play catch-up with Oracle, why not beat them at their own
> >>>game?
> >>>
> >>>What packages provide is encapsulation.  Hiding the data from the
> >>>user and forcing him/her to use the public interface (methods).
> >>>That is an important and admirable OO feature.  Some people think
> >>>that using the DB's security model can achieve the same thing.  It
> >>>can't, exactly, but there's an important lesson to be learned from
> >>>the suggestion.  The problem is that OOP is a *programming* paradigm,
> >>>and a database is not a *programming language*.  In a programming
> >>>language, there really is no such thing as "security".  There is
> >>>only "visibility" and "accessibility".  Private methods in an OOP
> >>>language do not provide *security*; they only limit *accessibility*.
> >>>Like so many other differences between the relational model and the
> >>>OOP model, there is an impedance mismatch here.  However, there is
> >>>also opportunity.
> >>>
> >>>In an OOPL, you can say: "Users can call this method from here, but
> >>>not from there."  What you *can't* say is: "User X can call this
> >>>method, but User Y cannot."  As you can see, these are orthogonal
> >>>concepts.  You could call the first "accessibility by location" and
> >>>the second "accessibility by authentication".  An ORDBMS should
> >>>support both.  "Private" does not respect your identity, only your
> >>>calling location.  An ACL does not respect your calling scope, only
> >>>your identity.  A system that has both is clearly more flexible than
> >>>one that only has one or the other.
> >>>
> >>>Now what you need to keep in mind is that each visibility model
> >>>serves a different purpose.  The purpose of a security model is to
> >>>limit *who* can see/touch certain data because the data has intrinsic
> >>>value.  The purpose of an accessibility model is to limit *where* and
> >>>*how* data can be seen/touched in order to preserve *program
> >>>invariants*.  So if you have an object (or tuple!) that records the
> >>>start and stop time of some process, it is probably a logical
> >>>invariant that the stop time is greater than or equal to the start
> >>>time.  For this reason, in a PL, you would encapsulate these fields
> >>>(attributes) and only provide controlled access to update them that
> >>>checks and preserves the invariant, *no matter who you are*.  You
> >>>don't want a superuser violating this invariant any more than Sue
> >>>User.
> >>>
> >>>Now you might object that constraints allow you to preserve
> >>>invariants as well, and indeed they do.  But constraints do not
> >>>respect calling scope.  Suppose there is a process that needs to
> >>>update the timestamps in a way that temporarily breaks the invariant
> >>>but restores it afterwards.  The only way to effect this in a
> >>>constraint environment is to drop the constraint, perform the
> >>>operation, and restore it.  However, dropping a constraint is not an
> >>>ideal solution because there may be other unprivileged processes
> >>>operating on the relation that still need the constraint to be
> >>>enforced.  There is no way to say: "There is a priviledged class of
> >>>methods that is allowed to violate this constraint because they are
> >>>trusted to restore it upon completion."  Note that this is different
> >>>from saying "There is a priviledged class of users that is allowed
> >>>to violate this constraint."  If you try to do something like give
> >>>read-only access to everybody and only write access to one user and
> >>>define that user to be the owner of the methods that update the data,
> >>>you have to follow the convention that that user only operates
> >>>through the defined interface, and doesn't hack the data directly.
> >>>That's because user-level accessibility is not the same as scope-
> >>>level accessibility.  Whereas, if you define something like a
> >>>package, and say: "Package X is allowed full and complete access
> >>>to relation Y", and stick the interface methods in X, you still have
> >>>all the user-level security you want while preserving the invariants
> >>>in the most elegant way.
> >>>
> >>>So you can think of a package as a scope in a programming language.
> >>>It's like a user, but it is not a user.  A user has privileges that
> >>>cut across scopes.  Now, whether packages should be different from
> >>>schemas is a whole different ballgame.  The purpose of a schema in
> >>>Postgres is not entirely clear to me.  There's lots of different ways
> >>>to use schemas, and there is no obvious best way to use them.  In
> >>>order to implement the accessibility features of packages, schemas
> >>>would have to be changed considerably.  Probably a lot of users would
> >>>be unhappy if schemas were changed in that way.  My guess is that
> >>>this would not be a good idea.
> >>>
> >>>I think we can get some guidance from PLs.  C++ is what you call a
> >>>"multi-paradigm language".  You can do everything from assembly to
> >>>metaprogramming in C++.  As such, it is very loose and open in some
> >>>respects.  C++ has two kinds of scopes: it has classes and namespaces.
> >>>Members of a class are encapsulated and support data hiding.  Members
> >>>of a namespace are only loosely grouped and do not support data hiding
> >>>explicitly.  Namespaces exist primarily to avoid name collisions.
> >>>
> >>>Java, on the other hand, decided that for OOP purity, everything must
> >>>be a class.  That would be like making schemas into packages and
> >>>imposing accessibility rules on them.  At the end of the day, I think
> >>>many PL design experts agree that making everything a class is not
> >>>necessarily the best way to go.
> >>>
> >>>So schemas can be like C++ namespaces - they provide a means to
> >>>loosely group related objects and help avoid name collisions.  So
> >>>the package could be like a class - they provide OOP-like
> >>>encapsulation via accessibility rules.  However, that doesn't mean
> >>>that nested schemas wouldn't also be a good thing.  In C++, nested
> >>>namespaces are extremely useful when one layer of scoping does not
> >>>sufficiently partition the namespace to avoid frequent name
> >>>collisions.  I think the same is true of Postgres.  I certainly would
> >>>like to be able to use nested schema names in several contexts.
> >>>Instead, I have to make a choice between making different schemas,
> >>>or making different name prefixes.  I wouldn't even mind if nested
> >>>schemas were only allowed to contain schemas except at the leaves of
> >>>the tree.  Another feature that is very useful is the "using clause".
> >>>Combined with nested namespaces, this is a very powerful way to give
> >>>programmers/dbas control over names.  You can give everything the
> >>>most natural name, and just put it in the appropriate namespace,
> >>>and use the namespace that is relevant to the given task at hand.
> >>>
> >>>So consider this example:
> >>>
> >>>Tables:
> >>> etl.import.record
> >>> etl.export.record
> >>>
> >>>As you can imagine, I don't really want to make an 'import' and
> >>>'export' schema at the top level.  There's several tables in
> >>>each schema, but that should illustrate the point.  Then, when
> >>>constructing queries, it would be nice to be able to do this:
> >>>
> >>>USING etl.import
> >>>      ;
> >>>SELECT *
> >>> FROM record
> >>> JOIN header ON ...
> >>> JOIN file ON ...
> >>>      ;
> >>>
> >>>The effect of a USING clause would be to import the schema names
> >>>into the public namespace for the duration of the transaction.  If
> >>>that leads to ambiguous names, then the parser/planner should emit an
> >>>error.
> >>>
> >>>__
> >>>David B. Held
> >>>Software Engineer/Array Services Group
> >>>200 14th Ave. East,  Sartell, MN 56377
> >>>320.534.3637 320.253.7800 800.752.8129
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >>>
> >>>
> >>>
> >>--
> >> Bruce Momjian                        |  http://candle.pha.pa.us
> >> pgman@candle.pha.pa.us               |  (610) 359-1001
> >> +  If your life is a hard drive,     |  13 Roberts Road
> >> +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> >> 19073
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 2: you can get off all lists at once with the unregister command
> >>   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >>
> >>
> >>
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)