Re: [HACKERS] Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE

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