Re: [HACKERS] Re: ACL enhancements - Mailing list pgsql-hackers

On Mon, Mar 06, 2000 at 02:47:36PM +0100, Karel Zak - Zakkr wrote:
> 
> 
> On Sat, 4 Mar 2000, Peter Eisentraut wrote:
> 
> > Karel Zak - Zakkr writes:
> 
> > I have given some more thought to the design of the pg_acl table (which
> > should not be global if it wants to be SQL compliant). I realize that the
> > size of my proposed 'one row per user/object/privilege' can grow rather
> > huge (20 users, 100 tables/things -> probably ca. 5000 rows) but I see
> > this as the best way some of the things (column access, grant options,
> > roles) can be implemented in the first place and it will be much easier to
> > verify the implementation because you can read it right out of SQL.
> 
> It must be fast! It is watchword for this project. The ACL is checked
> for each query. I agree with one global pg_acl for one DB.

Peter's point, if I understand it, is that pg_acl _cannot_ be global
per DB if we're striving for SQL compliance.

On the topic of SQL compliance: I spent some time this weekend looking
at the NIST's test suite for FIPS 127-2 (Federal Information Processing
Standard) This is the reg. that controls US gov't procurement of RDBMS
software, and includes by reference SQL92 (via ANSI and ISO docs.)

NIST was in the business of actually verifying conformance, until they
lost funding for it (Version 6.0 was released December 31, 1996). The test
suite is available from their website, and being a product of U.S. Gov't,
has no copyright. 

http://www.itl.nist.gov/div897/ctg/sql_form.htm

My inital take is that the tests will be useful internally to test our
SQL92 conformance. So far, I've tried building the test schemas. During
building these, the privilege system get's a workout, with lots of GRANT
... WITH GRANT OPTION, etc. The other problem is accepting column specific
privileges, as well as column aliases in views specified like so:
  CREATE VIEW viewname (alias1, alias2, alias3) AS ...

I rewrote those to use the SELECT something AS alias1 ... syntax.

I can continue on and run the 899 interactive SQL tests, as soon as I
figure out how the lack of SCHEMA support will impact them. It strikes me
that (future) SCHEMA support should impact the design for the ACL system.

> 
> > I think caching can be done pretty effectively, too, since ACL items
> > rarely change once they're set up. I'm inclined to ask other people's
> > opinions on this item. Other than that, I think we have a winner
> > here. Time to bring this up the the rest of the folks and draw up a
> > project page ...
> 

I think the general maxim: "Design for function, tune for performance"
may fit in here.

> Agree. ...a project page with more details, implementation steps ..etc.
> 

I'd be willing to assist in discussing what the SQL92 standard seems to 
require for privileges.

Peter, you were just saying something about having three weeks free ... ;-)

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005