Thread: Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE
Karel Zak - Zakkr writes: > This patch add to current code NOCREATETABLE and NOLOCKTABLE feature: > > CREATE USER username > [ WITH > [ SYSID uid ] > [ PASSWORD 'password' ] ] > [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] > -> [ CREATETABLE | NOCREATETABLE ] [ LOCKTABLE | NOLOCKTABLE ] > ...etc. IMHO, the syntax for create user is a hell and a half. Adding more keywords in the current fashion is a dead end. (Note: you have to remember the order in which the user "features" have to be entered.) I might as well propose that now, I'd like to see a syntax like CREATE USER name ( password = 'xxx', sysid = 99, superuser = true, ... ); That's much more flexible and extensible. The old syntax could coexist with this too. Regarding your two new features: If you disallow table locking you might as well tell users not to use the database. People need locks to operate a relational database. You will end up disabling the entire transaction mechanism if you want this to work properly. There already is a sufficient amount of checks for users not claiming exlusive locks on tables they shouldn't. Disallowing table creation might seem like a decent idea, but if at all, it should go into the grant/revoke realm. Incidentally, this is quite at odds with the SQL idea of how things should work, and I had hoped we could get there some day. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Tue, 29 Feb 2000, Peter Eisentraut wrote:
> Karel Zak - Zakkr writes:
>
> > This patch add to current code NOCREATETABLE and NOLOCKTABLE feature:
> >
> > CREATE USER username
> > [ WITH
> > [ SYSID uid ]
> > [ PASSWORD 'password' ] ]
> > [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
> > -> [ CREATETABLE | NOCREATETABLE ] [ LOCKTABLE | NOLOCKTABLE ]
> > ...etc.
>
> IMHO, the syntax for create user is a hell and a half. Adding more
> keywords in the current fashion is a dead end. (Note: you have to remember
> the order in which the user "features" have to be entered.)
>
> I might as well propose that now, I'd like to see a syntax like
>
> CREATE USER name (
> password = 'xxx',
> sysid = 99,
> superuser = true,
> ...
> );
>
> That's much more flexible and extensible. The old syntax could coexist
> with this too.
Agree (Why is it not in TODO?). Do you work on this?
> Regarding your two new features:
>
> If you disallow table locking you might as well tell users not to use the
> database. People need locks to operate a relational database. You will end
Here I not agree. I need users account with read-only/non-lock access. Very
simple is say "..not to use the database..", but you not "remake" my users,
you not admin for these users .. :-)
My NOLOCK implementation disallow LOCK TABLE command only, it not change
a low-level locking management.
> Disallowing table creation might seem like a decent idea, but if at all,
> it should go into the grant/revoke realm. Incidentally, this is quite at
> odds with the SQL idea of how things should work, and I had hoped we could
> get there some day.
The grant/revoke is good, but it is not global setting.
The PostgreSQL needs more options/settings for administration, a current
features is very unsatisfactory for real using for large and multiuser
aplication.
My suggestion for PG's priv./accounts:
1/ global setting which overwrite local (acl) settings - read-only account - disable account (oracle: ACCOUNT
LOCK) - create table priv. - user's quotas (but without tablespace?)2/ spit current super-user privileges to -
(dis)allowcreate functions/opretors/trigers - (dis)allow create user ? (dis)allow change system tables
3/ ? - remove current hda.conf to system catalogs4/ user profiles - CONNECT_TIME - IDLE_TIME -
PASSWORD_LIFE_TIME - PASSWORD_VERIFY_FUNCTION (trust/password/kerberos..) - ..etc (- CPU SPENTING ?)
5/ acl mask - default privilege for new table
Karel
On Tue, 29 Feb 2000, Karel Zak - Zakkr wrote: > > I might as well propose that now, I'd like to see a syntax like > > > > CREATE USER name ( > > password = 'xxx', > > sysid = 99, > > superuser = true, > > ... > > ); > > > > That's much more flexible and extensible. The old syntax could coexist > > with this too. > > Agree (Why is it not in TODO?). Do you work on this? Not right now but I'm planning on reworking the privilege system to get in compliance with SQL whenever we are through the beta phase. The creation of users is implementation defined but I guess I'm proposing this to those who care. > > If you disallow table locking you might as well tell users not to use the > > database. People need locks to operate a relational database. You will end > My NOLOCK implementation disallow LOCK TABLE command only, it not change > a low-level locking management. Exactly. The only goal that this will reach is to prevent people from fully using all the features available to them. It does not prevent them from doing denial of service attacks (which is presumably what motivated this). As a simple example: BEGIN; SELECT a FROM b FOR UPDATE; -- or some such -- twiddle thumbs will also claim locks on b. Or consider people wanting to use serializable transactions (SQL requirement, mind you). Then you can't even really guess what will be locked when. To summarize, just disallow using the LOCK command is not a good way to prevent locks. I recall that there already is some permission checking done in the lock manager. For example, you can't claim an exclusive lock on someone else's table. A logical extension to this (which might be done already) would be to disallow write-related locks on a table you don't have write access to in the first place. Preventing malicious locking should be well-integrated with the other privileges. > > Disallowing table creation might seem like a decent idea, but if at all, > > it should go into the grant/revoke realm. Incidentally, this is quite at > > odds with the SQL idea of how things should work, and I had hoped we could > > get there some day. > > The grant/revoke is good, but it is not global setting. Exactly. But create user is a global thing. The only reason to have any prileges at all in pg_shadow is because some cannot be database-specific (such as the right to create a database). I wouldn't object to a GRANT CREATE, if it applies to all creates, not just tables. People clearly want that, and we're not going to have schemas soon. Incidentally, I believe that the privileges necessary to create a table are left to the implementation, so I withdraw part of my argument above. > My suggestion for PG's priv./accounts: Great, as I said, I've been meaning to look into this. I'd be happy to hear any "demands". > - read-only account Just don't give anyone write permissions to anything. Unix doesn't have read-only accounts. I'm not so excited about non-orthogonal privileges. > - disable account (oracle: ACCOUNT LOCK) Hmm, that sounds reasonable. > - create table priv. On its way. ;) > - user's quotas (but without tablespace?) Probably very hard to do. The day you started using a relational database you largely gave up on tightly controlling storage constraints. See the never ending debate on 2x disk usage on drop column. Certainly useful, though. > - (dis)allow create functions/opretors/trigers Could/should be integrated in grant create. > - (dis)allow create user > ? (dis)allow change system tables Exist already. > 3/ ? - remove current hda.conf to system catalogs Won't work. The postmaster must authenticate the user before the database starts up. Well, it doesn't absolutely have to but redesigning that would be a pain. > - CONNECT_TIME > - IDLE_TIME Interesting. That would probably require a lot of work, though. > - PASSWORD_LIFE_TIME Got that. > - PASSWORD_VERIFY_FUNCTION (trust/password/kerberos..) > - ..etc > (- CPU SPENTING ?) > 5/ acl mask - default privilege for new table Definitely. Seems like we have a full bag of plans. Let's argue it out! ;) -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Tue, 29 Feb 2000, Peter Eisentraut wrote: > > My suggestion for PG's priv./accounts: > > Great, as I said, I've been meaning to look into this. I'd be happy to > hear any "demands". > > > - read-only account > > Just don't give anyone write permissions to anything. Unix doesn't have > read-only accounts. I'm not so excited about non-orthogonal privileges. > > > - disable account (oracle: ACCOUNT LOCK) > > Hmm, that sounds reasonable. > > > - create table priv. > > On its way. ;) > > > - user's quotas (but without tablespace?) > > Probably very hard to do. The day you started using a relational database > you largely gave up on tightly controlling storage constraints. See the > never ending debate on 2x disk usage on drop column. Certainly useful, > though. > > > - (dis)allow create functions/opretors/trigers > > Could/should be integrated in grant create. > > > - (dis)allow create user > > ? (dis)allow change system tables > > Exist already. > > > 3/ ? - remove current hda.conf to system catalogs > > Won't work. The postmaster must authenticate the user before the database > starts up. Well, it doesn't absolutely have to but redesigning that would > be a pain. > > > - CONNECT_TIME > > - IDLE_TIME > > Interesting. That would probably require a lot of work, though. > > > - PASSWORD_LIFE_TIME > > Got that. I said about a PROFILE, it is more flexible than current simple CREATE USER. > > > - PASSWORD_VERIFY_FUNCTION (trust/password/kerberos..) > > - ..etc > > (- CPU SPENTING ?) > > > 5/ acl mask - default privilege for new table > > Definitely. > > > Seems like we have a full bag of plans. Let's argue it out! ;) I not only want new features and send suggestion, I can help with "full bag of plans". But it is really great work and I not sure if is possible create it as one-man project, it needs consensus between developers. If you plan make changes to acl/account code it must be non-isolate change (it must include user-profiles ..etc). (IMO of course :-) A question: who is not user account defined for db and is it global? The global account is probably not a problem, a problem is account settings. IMHO is better use global account in 'pg_shadow' (with passwords, basic options ..) and non-global 'pg_accountoption' in specific DB (with CONNECT_TIME, IDLE_TIME, acl_mask ...etc.). This concept is better extendable... (We have free hands for this, it is not in SQL92 :-)) Karel
> plan make changes to acl/account code it must be non-isolate change (it
> must include user-profiles ..etc). (IMO of course :-)
While I'm thinking about it...
The current acl storage scheme flattens the acl info into a single
string, with a special character ("=" as I recall) to delimit the
user/group name from the permissions. But by quoting the user name, it
is possible to create a user name which contains an equals sign,
screwing up the acl handling.
If you are redoing the acls, a good first step is to fix this, perhaps
by recoding the acl field into a structure with at least two fields
for username and permissions.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Tue, 29 Feb 2000, Thomas Lockhart wrote:
> > plan make changes to acl/account code it must be non-isolate change (it
> > must include user-profiles ..etc). (IMO of course :-)
>
> While I'm thinking about it...
>
> The current acl storage scheme flattens the acl info into a single
> string, with a special character ("=" as I recall) to delimit the
> user/group name from the permissions. But by quoting the user name, it
> is possible to create a user name which contains an equals sign,
> screwing up the acl handling.
>
> If you are redoing the acls, a good first step is to fix this, perhaps
> by recoding the acl field into a structure with at least two fields
> for username and permissions.
Yes. And..the current schema (acl in pg_class) is not relation schema, in
the pg_class is username not user's oid...ect. Is possible create it as
relation? (Example, in pg_group we haven't username, we use oid here.)
My acl idea:
Why not create specific pg_acl table and split a current monolitic
acl string to more columns? Example:
Columns in pg_acl table:
reloid (oid) - relation (table) oiduser_insert (text[]) - privilege for users for insert - in
textarray is {"username1","username2"... } (or use user's oid instead username)
group_insert - simular as previous, but for groupuser_delete - ....etc....
example:
SELECT * from pg_acl;
reloid | user_insert | group_insert | user_delete ..........etc
---------------------------------------------------------
12345 | {"karel", "peter"} | {"group1"} | {"karel"} ..........etc
Is it bad idea? (It never needs any specific acl string parser, take
informatios from this table is very simple and very standard
"tuple-operation".) Yes, it is a little "talkative", but if instead
user/group name we use oid, it will right and nice.
...as I said: is the current acl/account schema good?
Karel
On Tue, 29 Feb 2000, Karel Zak - Zakkr wrote:
> On Tue, 29 Feb 2000, Thomas Lockhart wrote:
(I didn't get this email, but anyway ...)
> > The current acl storage scheme flattens the acl info into a single
> > string, with a special character ("=" as I recall) to delimit the
> > user/group name from the permissions. But by quoting the user name, it
> > is possible to create a user name which contains an equals sign,
> > screwing up the acl handling.
Try creating a user "group xxx" ...
> > If you are redoing the acls, a good first step is to fix this, perhaps
> > by recoding the acl field into a structure with at least two fields
> > for username and permissions.
This was precisely the idea. Everything else should fall in place more
easily after that.
> My acl idea:
> reloid | user_insert | group_insert | user_delete ..........etc
> ---------------------------------------------------------
> 12345 | {"karel", "peter"} | {"group1"} | {"karel"} ..........etc
This still has arrays. (shudder) Try getting the information 'Does Peter
have access to x?' out of that. I was thinking along the lines of
create table pg_privilege/pg_acl/? ( objoid oid, -- not only reloid, but types, functions, etc. userid int,
privilegechar, -- maybe 'U' update, 'I' insert, etc. grant_option bool
)
To be extended to cover column access as well. (Might have to be yet
another table.) Mathematically, this will be slower (especially since you
can't use SysCache on composite keys(???)) but similar schemas are
employed throughout by triggers etc.
> ...as I said: is the current acl/account schema good?
The SCHEME is good. The SCHEMA isn't.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
On Tue, 29 Feb 2000, Peter Eisentraut wrote:
> On Tue, 29 Feb 2000, Karel Zak - Zakkr wrote:
>
> > My acl idea:
>
> > reloid | user_insert | group_insert | user_delete ..........etc
> > ---------------------------------------------------------
> > 12345 | {"karel", "peter"} | {"group1"} | {"karel"} ..........etc
>
> This still has arrays. (shudder) Try getting the information 'Does Peter
> have access to x?' out of that. I was thinking along the lines of
As I say: we can use oid or string with oids instead array.
Example
reloid | user_insert | group_insert |--------------------------------------12345 | "1111,2222" | "545454" |
.. parse these strings a easy and 'Does Peter have access to x?' is realy
simple.
>
> create table pg_privilege/pg_acl/? (
> objoid oid, -- not only reloid, but types, functions, etc. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
yes, yes, yes ! (the oracle allow grant priv. to 25(!) different features)
> userid int,
If I understend you, you want save to one line information about one
user only (?), if yes this table will huge (sum(object) * sum(users)),
but probably fastly (because it not needs parse any array or string).
Hmm, perhaps it is not bad idea. What say the other?
> privilege char, -- maybe 'U' update, 'I' insert, etc.
I really not sure if is good still create this monolithic string,
why not use one (bool) column for update one for insert ..etc?
It is fastly and easy (a string needs parse, etc).
> grant_option bool
> )
It is goods if you agree with separate acl table :-)
> To be extended to cover column access as well. (Might have to be yet
> another table.) Mathematically, this will be slower (especially since you
> can't use SysCache on composite keys(???)) but similar schemas are
> employed throughout by triggers etc.
Yes, a speed will problem, it must be cached (in an separate acl cache?),
or after (connection) start create a temp table with acl for a current user
and with relevant information only. Karel
On Wed, 1 Mar 2000, Karel Zak - Zakkr wrote: > Example > > reloid | user_insert | group_insert | > -------------------------------------- > 12345 | "1111,2222" | "545454" | > > .. parse these strings a easy and 'Does Peter have access to x?' is realy > simple. > > privilege char, -- maybe 'U' update, 'I' insert, etc. > > I really not sure if is good still create this monolithic string, > why not use one (bool) column for update one for insert ..etc? > It is fastly and easy (a string needs parse, etc). Space. A char column takes one byte (when using the internal char1 type), five or six bool columns take five or six bytes plus all the overhead. Also you get into the problem where certain flag combinations are not even valid. > Yes, a speed will problem, it must be cached (in an separate acl cache?), > or after (connection) start create a temp table with acl for a current user > and with relevant information only. Steal the code on how triggers are looked up. It does an index scan. This table will be huge (on average probably O(#tables * #users)) but it has the advantage that it is a direct mapping from what SQL calls a "privilege descriptor", so implementation could be easier. I would like to take a look at SQL3 first, because they define some more privilege stuff which we could take into account (ROLES, for example). By the way: Regarding your original patch that disallowed LOCK to users, I looked it up in the source and it turns out that in order to lock a table you need write access to it. Isn't that sufficient? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> I would like to take a look at SQL3 first, because they define some more
> privilege stuff which we could take into account (ROLES, for example).
Yes. Just today I look at Oracle's documentation for ROLEs, PROFILEs
... my idea is prepare acl/account code for this freatures too. What?
IMHO this discussion good adept for any new-acl&accout project. Agree?
> By the way: Regarding your original patch that disallowed LOCK to users, I
... and I see your web page, you listen good music :-)
> looked it up in the source and it turns out that in order to lock a table
> you need write access to it. Isn't that sufficient?
You mean this original PG's code (?):
if (lockstmt->mode == AccessShareLock) aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL
else aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL
if (aclresult != ACLCHECK_OK) elog(ERROR, "LOCK TABLE: permission denied");
Yes. The my patch create a lock-permission level over this current code.
It is global setting and example for all non-AccessShareLocks you must have
pg_shadow->locktable privilege and 'write' privilage for table.
It is because I have users which needs update/insert access to tables, but
I not want allow a lock command for these users.
Karel
On Wed, 1 Mar 2000, Karel Zak - Zakkr wrote: > Yes. Just today I look at Oracle's documentation for ROLEs, PROFILEs > ... my idea is prepare acl/account code for this freatures too. What? I read about that in SQL3 yesterday and I think we could transparently adapt the current group scheme to it. > > looked it up in the source and it turns out that in order to lock a table > > you need write access to it. Isn't that sufficient? > Yes. The my patch create a lock-permission level over this current code. > It is global setting and example for all non-AccessShareLocks you must have > pg_shadow->locktable privilege and 'write' privilage for table. > > It is because I have users which needs update/insert access to tables, but > I not want allow a lock command for these users. Why? You are saying to these users, "You can write data to these tables but I can't guarantee you that anything you do will actually be written, consistent, and non-corrupted." And as I said before, this doesn't prevent users from actually *locking* tables either, because there are several other methods to do that. One thing I thought about is that you might want to reserve exclusive locks and access exclusive locks, and possibily ShareRowExclusiveLock (that name makes a lot of sense to me, btw.) and ShareLock to table owners and superusers. That way vanilla users with write access can only do a RowExclusiveLock at best. Perhaps there could be a grant fancylock on table command (kind of :), but that would have to be reviewed closely. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Thu, 2 Mar 2000, Peter Eisentraut wrote: > You are saying to these users, "You can write data to these tables but I > can't guarantee you that anything you do will actually be written, > consistent, and non-corrupted." And as I said before, this doesn't prevent No. How often you LOCKing table? If you work in transaction block and DB design is good (or very simple), you not need very often the LOCK. I'm working with my large DB every day and without locking and my DB is consistent (example for me is more iteresting full-time full-access to table than any a transaclion abort.) The LOCK command is not primary tool for data integrity (primary it is transaction,primary/foreign keys/check-triggers..etc). Set/Not-Set NOLOCK is admin choice, if you not want it you not must set it... OK? I good understand you, but life and a SQL DB is not black or white, the world is coloured :-) IMHO will better LOCK privilage add to "local" table acl and differentiate between write-access and lock-access (a good item to TODO). This acl option will better than my NOLOCK.IMHO will better "recast" this discussion to discussion about new acl/account features. Agree? I a little speculated about it and IHO is real possible make CRATE ROLE, CREATE PROFILE and global pg_acl table and extend GRANT (function,alter..). See example Oracle8 documentation (example on: http://mravenec.jcu.cz/oracle), it is more readable than SQL standards :-) Karel
On Thu, 2 Mar 2000, Peter Eisentraut wrote:
> On Wed, 1 Mar 2000, Karel Zak - Zakkr wrote:
>
> > Yes. Just today I look at Oracle's documentation for ROLEs, PROFILEs
> > ... my idea is prepare acl/account code for this freatures too. What?
>
> I read about that in SQL3 yesterday and I think we could transparently
> adapt the current group scheme to it.
Sorry, I skip this part in my previous letter. Why you mean adaptation
to current group scheme?
Karel
Karel Zak - Zakkr writes: > > I read about that in SQL3 yesterday and I think we could transparently > > adapt the current group scheme to it. > > Sorry, I skip this part in my previous letter. Why you mean adaptation > to current group scheme? I said adapt the current group scheme to roles. The current groups are a functional subset of what roles do. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
ACL enhancements (was Re: [HACKERS] Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE)
From
Peter Eisentraut
Date:
Karel Zak - Zakkr writes: > No. How often you LOCKing table? If you work in transaction block and DB > design is good (or very simple), you not need very often the LOCK. I agree with providing access restrictions for locking tables in ShareLock and above, perhaps via a GRANT LOCK ON TABLE command. But just saying "Don't use LOCK" isn't going to cut it, it must be done throughout *all* commands that do any locking, otherwise it's just inconsistent. You have a point that these higher level locks aren't really anybody's business other than the table owner, superusers, and those explicitly granted access to them. But saying that you can run your database without locks is false because even plain UPDATE gets a row exclusive lock. > I a little speculated about it and IHO is real possible make CRATE ROLE, > CREATE PROFILE and global pg_acl table and extend GRANT (function,alter..). > See example Oracle8 documentation (example on: http://mravenec.jcu.cz/oracle), > it is more readable than SQL standards :-) I don't have any real problems with reading SQL standards. I'd rather do that than submit to some vendor's ideas. Having said that, I'll still read the above, especially because profiles are not in SQL. 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. 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 ... -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
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.
> 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
Yes. IMHO will good initialize more user's information after connection start.
Now is init only username, but we can save to any persistent struct full
user's pg_shadow row. (My bash (shell) not see the /etc/password before
each command, it is initialize after bash start and it is persistent to
its end.) The current code look at pg_shadow very often...etc.
> 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 ...
Agree. ...a project page with more details, implementation steps ..etc.
Karel
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