Обсуждение: content-based access control (Re: views, access control, etc)

Seems like the discussion on views and access control is
drifting in a direction that interests me.  At the risk of
once again bringing up an issue that's already been solved
and tabled, I offer the following wish item:

Content-based access control (CBAC).  In my experience,
when these words are uttered, DBAs and MIS designers groan.
I wish CBAC were never required.  Unfortunately sometimes
it is, and I wonder if the PG team is thinking about it.

Column protection is not CBAC, of course, though it sorta
feels like it.  Column protection can be useful, but I've
had less need for it than true CBAC.  I'd like to see
column AC in PG someday, but it's not very important to me
personally -- whereas  I have immediate requirements for CBAC.

In true CBAC, the whole record is confidential.  In table T,
User X "owns" some records and User Y "owns" some records, and
the two of them should not see each other's records.  You can
address this problem with views (if your view mechanism allows
RSE as well as FSE, and if views don't inherit AC from parent
tables).  But this gets you into a maintenance headache, where
you're creating new views every time a new user joins the
crowd.

What I'd like, when I really think about it, is a rule
mechanism for selects.  Perhaps PG already has this, but my
conceptual model is the Sybase trigger feature.  On Update and
Delete (but not select) in the older Sybase engines, the DB
designer can interrupt the transaction and abort or alter it
according to rules coded in SQL.  This was *very* useful, but
(at least back then) it didn't work on select.

I wish that all I had to do was code the records with the
owner UID, and slap a "select trigger" or rule on the table
that said effectively:  on a select, if record N has a user
code not matching the user code of the query connection,
suppress that record from the output stream. [We could get
more sophisticated than that, of course:  if (RSE) and
(group|user code) in (list)...]

Of course the privileged user (database owner or postgres)
would have to be exempt.  I'd like to see this suppression
mechanism work for COUNT and all other stats. In fact, to each
user, the table should look like it contains only that user's
data.  That would be truly cool.  Like a view, but rule-based.

I *could* write a canned UI that creates this kind of view
dynamically as it starts up, but (imho) rules don't belong in
the app, they belong in the engine.  I want my access control
to be proof against C code using API lib, interactive sql
sessions, and any other way users can query the db.

So, is this "already in 6.3"?  Has anyone faced this problem
and solved it by various other clever means?  I haven't
thought it through as clearly as I would like, so I'd be
interested to hear from those who have.

de

.............................................................................
:De Clarke, Software Engineer                     UCO/Lick Observatory, UCSC:
:Mail: de@ucolick.org | "There is no problem in computer science that cannot:
:Web: www.ucolick.org |  be solved by another level of indirection"  --J.O. :

De Clarke wrote:
>
> Seems like the discussion on views and access control is
> drifting in a direction that interests me.  At the risk of
> once again bringing up an issue that's already been solved
> and tabled, I offer the following wish item:
>
> Content-based access control (CBAC).  In my experience,
> when these words are uttered, DBAs and MIS designers groan.
> I wish CBAC were never required.  Unfortunately sometimes
> it is, and I wonder if the PG team is thinking about it.
>
> Column protection is not CBAC, of course, though it sorta
> feels like it.  Column protection can be useful, but I've
> had less need for it than true CBAC.  I'd like to see
> column AC in PG someday, but it's not very important to me
> personally -- whereas  I have immediate requirements for CBAC.
>
> In true CBAC, the whole record is confidential.  In table T,
> User X "owns" some records and User Y "owns" some records, and
> the two of them should not see each other's records.  You can
> address this problem with views (if your view mechanism allows
> RSE as well as FSE, and if views don't inherit AC from parent
> tables).  But this gets you into a maintenance headache, where
> you're creating new views every time a new user joins the
> crowd.
>
> What I'd like, when I really think about it, is a rule
> mechanism for selects.  Perhaps PG already has this, but my
> conceptual model is the Sybase trigger feature.  On Update and
> Delete (but not select) in the older Sybase engines, the DB
> designer can interrupt the transaction and abort or alter it
> according to rules coded in SQL.  This was *very* useful, but
> (at least back then) it didn't work on select.

You could use PG triggers on Updates, Deletes and Inserts (to insert
owner user name) and try to use RULEs to rewrite SELECT statement.
(I never played with RULEs...)

Vadim

Vadim said:

> You could use PG triggers on Updates, Deletes and Inserts (to insert
> owner user name) and try to use RULEs to rewrite SELECT statement.
> (I never played with RULEs...)

Perhaps if it can be done easily with rules, and then the
method can be published -- but I have to wonder about using
features that even the hardcore developers have never played
with :-)   I guess I was hoping for some kind of specific
support in the core.

If CBAC were supported in a straightforward, easy way in PG it
would be a major "selling" point imho.  We could say "and
PostgreSQL supports content-based access control..."

        ----------------------------

On the related subject, I like oids.  They are very useful.
But I *also* want table-specific autonumbering (monotonic
integer series in which gaps do indicate deleted records) and
I'd really prefer that it be provided automatically as a table
creation option, rather than having to write the same darn
trigger N hundred times (as I have using the older
Sybase engines).

In fact, while I'm dreaming:  why not have a set of *three*
table creation options?
    -recno     causes an autonumber field to be prepended
        to each record.
    -user    causes the user ID of the inserting process
        to be prepended to each record.
    -stamp    causes the timestamp at insert time to be
        prepended.
The fields could have fixed names (like oid), say "recno",
"user", and "stamp" -- or as a luxury option the user could
supply their names as part of the option syntax:
    -user uid -stamp itime -recno seqno

Like oid, these fields would *not appear* in result streams
unless explicitly included in the FSE.  'select *' would not
reveal them, but 'select oid,recno,user,stamp,*' would show
all fields. (If these options are specified at table create
time, then all subsequent inserts to that table automatically
fill the special fields, without the user or developer having
to know or care.)

Of the couple of hundred tables I've designed and deployed in
various apps over the last 5 years, perhaps 10 or 20 have
*not* needed these fields.  I've cut-n-pasted the same darn
boilerplate code a couple of hundred times to implement these
fields on all the other tables.  If I could specify a few
simple options on 'create table' instead, so much drudgery
would be eliminated! And users would not see the "bookkeeping"
fields by default.  Sounds heavenly to me.

Once you imagine that automatic entry options of this sort
exists, then CBAC seems like a logical accompaniment,
available only on those tables where the UID auto-stamp has
been selected at create time. It would be just one more
option:  CBAC: Y/N. If Y, then the uid field is checked
against the owner of the select query and the filtering done
as described in my last post to HACKERS.

This seems (to me anyway) like a killer value-added feature,
something that would make PG so-o-o-o attractive for real,
practical, bread-n-butter app development.  How hard would
it be?  (I know, that's the most annoying question anyone
can ask a developer).

Am I alone in thinking this would be incredibly cool and
worth some effort?  I would love to see PG be demonstrably
BETTER than the commercial competition in several specific
areas (then maybe I could get approval to use it for
production work) and this looks like a good opportunity.

de

.............................................................................
:De Clarke, Software Engineer                     UCO/Lick Observatory, UCSC:
:Mail: de@ucolick.org | "There is no problem in computer science that cannot:
:Web: www.ucolick.org |  be solved by another level of indirection"  --J.O. :

> Am I alone in thinking this would be incredibly cool and
> worth some effort?  I would love to see PG be demonstrably
> BETTER than the commercial competition in several specific
> areas (then maybe I could get approval to use it for
> production work) and this looks like a good opportunity.
>

Yes, these would all be nice, but we have to get the 'bread and butter'
features working perfectly first, like subselects.

--
Bruce Momjian
maillist@candle.pha.pa.us

On Thu, 15 Jan 1998, De Clarke wrote:

> Am I alone in thinking this would be incredibly cool and
> worth some effort?  I would love to see PG be demonstrably
> BETTER than the commercial competition in several specific
> areas (then maybe I could get approval to use it for
> production work) and this looks like a good opportunity.

    Look forward to seeing the patches? :)

    I think the concepts are cool (uid, recno, timestamp) for having
this directly in the tables, and the concept of being able to have record
based restrictions on a table doubly so.

    An example of the usefulness of this is the radiusd accounting
package I've been working on.  Be nice to be able to let the salesman have
direct select access to the login records for *their* clients, and then
have full access to the personal information records to actually make
changes...without them having access to another clients records...

Thus spake De Clarke
> In fact, while I'm dreaming:  why not have a set of *three*
> table creation options?
>     -recno     causes an autonumber field to be prepended
>         to each record.
>     -user    causes the user ID of the inserting process
>         to be prepended to each record.
>     -stamp    causes the timestamp at insert time to be
>         prepended.
> The fields could have fixed names (like oid), say "recno",
> "user", and "stamp" -- or as a luxury option the user could
> supply their names as part of the option syntax:
>     -user uid -stamp itime -recno seqno
>
> Like oid, these fields would *not appear* in result streams
> unless explicitly included in the FSE.  'select *' would not
> reveal them, but 'select oid,recno,user,stamp,*' would show
> all fields. (If these options are specified at table create

I like the general idea but I think they should either show up
when '*' is selected or else automatically put them into each
table.  If you don't then generic functions will have problems.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.