Thread: Re: [HACKERS] Postgres acl (fwd)

> > > > Can someone who has permission to create databases be trusted not to
> > > > delete others?  If we say no, how do we make sure they can change
> > > > pg_database rows on only databases that they own?
> > >
> > >     deleting a database is accomplished using 'drop database', no?
> > > Can the code for that not be modified to see whether the person dropping
> > > the database is the person that owns it *or* pgsuperuser?
> >
> > It already does the check, but issues an SQL from the C code to delete
> > from pg_database.  I believe any user who can create a database can
> > issue the same SQL command from psql, bypassing the drop database
> > checks, no?
>
>     Okay, I understand what you mean here...so I guess the next
> question is should system tables be directly modifyable by non-superuser?
>
>     For instance, we have a 'drop database' SQL command...can we
> restrict 'delete from pg_database' to just superuser, while leaving 'drop
> database' open to those with createdb privileges?  Same with 'create
> user', and, possible, a 'create group' command instead of 'insert into
> pg_group'?

IMHO, the system tables should _never_ be directly modifiable by anyone
other than the superuser/dba.  The rest of the population should have to
use a command of some sort that can be grant/revoked by said superuser/dba.

darrenk

> > > > > Can someone who has permission to create databases be trusted not to
> > > > > delete others?  If we say no, how do we make sure they can change
> > > > > pg_database rows on only databases that they own?
> > > >
> > > >   deleting a database is accomplished using 'drop database', no?
> > > > Can the code for that not be modified to see whether the person dropping
> > > > the database is the person that owns it *or* pgsuperuser?
> > >
> > > It already does the check, but issues an SQL from the C code to delete
> > > from pg_database.  I believe any user who can create a database can
> > > issue the same SQL command from psql, bypassing the drop database
> > > checks, no?
> >
> >       Okay, I understand what you mean here...so I guess the next
> > question is should system tables be directly modifyable by non-superuser?
> >
> >       For instance, we have a 'drop database' SQL command...can we
> > restrict 'delete from pg_database' to just superuser, while leaving 'drop
> > database' open to those with createdb privileges?  Same with 'create
> > user', and, possible, a 'create group' command instead of 'insert into
> > pg_group'?
>
> IMHO, the system tables should _never_ be directly modifiable by anyone
> other than the superuser/dba.  The rest of the population should have to
> use a command of some sort that can be grant/revoked by said superuser/dba.

Are there any maintenance operations which require a "delete from pg_xxx"? If
not, then we could just modify the parser (or the executor?) to check the table
name and not allow insert/delete from any table whose name starts with "pg_". Had
to ask, although I'm sure this is too easy to actually work :)

                                            - Tom

On Wed, 7 Jan 1998, Thomas G. Lockhart wrote:

> Are there any maintenance operations which require a "delete from pg_xxx"? If
> not, then we could just modify the parser (or the executor?) to check the table
> name and not allow insert/delete from any table whose name starts with "pg_". Had
> to ask, although I'm sure this is too easy to actually work :)

    As long as what you are suggesting doesn't break "drop database", "drop
table", "drop view"...I realize that this is obvious, but...

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

> >
> > IMHO, the system tables should _never_ be directly modifiable by anyone
> > other than the superuser/dba.  The rest of the population should have to
> > use a command of some sort that can be grant/revoked by said superuser/dba.
>
> Are there any maintenance operations which require a "delete from pg_xxx"? If
> not, then we could just modify the parser (or the executor?) to check the table
> name and not allow insert/delete from any table whose name starts with "pg_". Had
> to ask, although I'm sure this is too easy to actually work :)

Interesting thought.  Wonder if it would work?

--
Bruce Momjian
maillist@candle.pha.pa.us

>
> On Wed, 7 Jan 1998, Thomas G. Lockhart wrote:
>
> > Are there any maintenance operations which require a "delete from pg_xxx"? If
> > not, then we could just modify the parser (or the executor?) to check the table
> > name and not allow insert/delete from any table whose name starts with "pg_". Had
> > to ask, although I'm sure this is too easy to actually work :)
>
>     As long as what you are suggesting doesn't break "drop database", "drop
> table", "drop view"...I realize that this is obvious, but...

Good point.  Yes it does.  dbcommands.c and user.c both do direct calls
to pg_exec to pass everything into the parser, optimizer, and executor.

The real fix is to do things like copy.c does, by directly calling the C
routines and making the desired changes there.  Or to have some global
flag that says "Backend performed the rights test, let this SQL
succeed."  That may be cleaner.  Table access rights are tested in just
one function, I think.

We still have the pg_user.passwd problem, and pg_user is not readable by
general users.  I can't think of a fix for this.

--
Bruce Momjian
maillist@candle.pha.pa.us