Thread

Re: [HACKERS] Postgres acl (fwd)

Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-01-07T01:25:47Z

> > > > > 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