Re: ACL enhancements

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(at)gmx(dot)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)allow create functions/opretors/trigers
- (dis)allow create user
? (dis)allow change system tables

3/ ? - remove current hda.conf to system catalogs

4/ 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(at)gmx(dot)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(at)alumni(dot)caltech(dot)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) oid
user_insert (text[]) - privilege for users for insert
- in text array is
{"username1","username2"... }
(or use user's oid instead username)

group_insert - simular as previous, but for group
user_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,
privilege char, -- 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(at)gmx(dot)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(at)gmx(dot)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(at)gmx(dot)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(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden

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(at)gmx(dot)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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005