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

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