Postgres acl

I believe I found a bug. If a user other than the postgres superuser is
given permission to create databases, then he should be able to destroy
the databases he creates. Currently he can't, at least in version 6.2.1
complied for SunOS 5.5. Only the poostgres superuser can delete
databases. If otherusers try they get the following error message:

"WARN:pg_database: Permission denied.
destroydb: database destroy failed on tmpdb."

eventhough this user is the database admin for tmpdb as shown in the
pd_database table.

At the moment, one requires "create users" privilege to destroy your own
database, but only "create databases" privilege to create one. I think
there is something about this on the ToDo list...

- Tom

I believe I found a bug. If a user other than the postgres superuser is
given permission to create databases, then he should be able to destroy
the databases he creates. Currently he can't, at least in version 6.2.1
complied for SunOS 5.5. Only the poostgres superuser can delete
databases. If otherusers try they get the following error message:

"WARN:pg_database: Permission denied.
destroydb: database destroy failed on tmpdb."

eventhough this user is the database admin for tmpdb as shown in the
pd_database table.

Here is the fix. This bug has been around for a while:

---------------------------------------------------------------------------

*** ./aclchk.c.orig	Tue Jan  6 00:10:25 1998
--- ./aclchk.c	Tue Jan  6 00:18:40 1998
***************
*** 410,416 ****
  		 * pg_database table, there is still additional permissions
  		 * checking in dbcommands.c
  		 */
! 		if (mode & ACL_AP)
  			return ACLCHECK_OK;
  	}

--- 410,416 ----
  		 * pg_database table, there is still additional permissions
  		 * checking in dbcommands.c
  		 */
! 		if ((mode & ACL_WR) || (mode & ACL_AP))
  			return ACLCHECK_OK;
  	}

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

Forwarded message:

I believe I found a bug. If a user other than the postgres superuser is
given permission to create databases, then he should be able to destroy
the databases he creates. Currently he can't, at least in version 6.2.1
complied for SunOS 5.5. Only the poostgres superuser can delete
databases. If otherusers try they get the following error message:

"WARN:pg_database: Permission denied.
destroydb: database destroy failed on tmpdb."

eventhough this user is the database admin for tmpdb as shown in the
pd_database table.

Here is the fix. This bug has been around for a while:

---------------------------------------------------------------------------

*** ./aclchk.c.orig	Tue Jan  6 00:10:25 1998
--- ./aclchk.c	Tue Jan  6 00:18:40 1998
***************
*** 410,416 ****
* pg_database table, there is still additional permissions
* checking in dbcommands.c
*/
! 		if (mode & ACL_AP)
return ACLCHECK_OK;
}

--- 410,416 ----
* pg_database table, there is still additional permissions
* checking in dbcommands.c
*/
! 		if ((mode & ACL_WR) || (mode & ACL_AP))
return ACLCHECK_OK;
}

I am now thinking about this patch, and I don't think I like it. The
original code allowed APPEND-only for users who can create databases,
but no DELETE. The patch gives them DELETE permission, so they can
destroy their database, but they could issue the command:

select from pg_database

and destroy everyone's. 'drop database' does checkes, but the acl check
is done in the executor, and it doesn't know if the the checks have been
performed or not.

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?

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

On Tue, 6 Jan 1998, Bruce Momjian wrote:

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?

Bruce Momjian wrote:

Forwarded message:

I believe I found a bug. If a user other than the postgres superuser is
given permission to create databases, then he should be able to destroy
the databases he creates. Currently he can't, at least in version 6.2.1
complied for SunOS 5.5. Only the poostgres superuser can delete
databases. If otherusers try they get the following error message:

"WARN:pg_database: Permission denied.
destroydb: database destroy failed on tmpdb."

eventhough this user is the database admin for tmpdb as shown in the
pd_database table.

Here is the fix. This bug has been around for a while:

---------------------------------------------------------------------------

*** ./aclchk.c.orig   Tue Jan  6 00:10:25 1998
--- ./aclchk.c        Tue Jan  6 00:18:40 1998
***************
*** 410,416 ****
* pg_database table, there is still additional permissions
* checking in dbcommands.c
*/
!             if (mode & ACL_AP)
return ACLCHECK_OK;
}

--- 410,416 ----
* pg_database table, there is still additional permissions
* checking in dbcommands.c
*/
!             if ((mode & ACL_WR) || (mode & ACL_AP))
return ACLCHECK_OK;
}

I am now thinking about this patch, and I don't think I like it. The
original code allowed APPEND-only for users who can create databases,
but no DELETE. The patch gives them DELETE permission, so they can
destroy their database, but they could issue the command:

select from pg_database

and destroy everyone's. 'drop database' does checkes, but the acl check
is done in the executor, and it doesn't know if the the checks have been
performed or not.

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?

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

Can't you check to see if they own the database before you let them
delete the row in pg_database. If a row is deleted from pg_database, it
is disallowed unless the userid is the same as the datdba field in that
row?

On Tue, 6 Jan 1998, Bruce Momjian wrote:

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?

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

On Tue, 6 Jan 1998, Bruce Momjian wrote:

On Tue, 6 Jan 1998, Bruce Momjian wrote:

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

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

On Tue, 6 Jan 1998, Bruce Momjian wrote:

On Tue, 6 Jan 1998, Bruce Momjian wrote:

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

Yes, we must replace the SQL commands in commands/dbcommands.c with
lower-level C table access routines so we do not have to go to the
executor, where the access permissions are checked.

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

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