Hi,
In using 8.3. I'm trying to set up programmatic database creation but  
is there a way that the user creating the databases can be restricting  
from dropping them?

I have two roles, 'adminuser' with createdb permission, and 'dbuser' a  
user with CRUD privileges.

adminuser is a member of the dbuser role, this seems to allow  
adminuser to createdb databases for dbuser with:
createdb -U adminuser -O dbuser  new_database_name
Adding .pgpass to the linux user's home directory allows createdb to  
work without additional user input.

But now it seems the linux user also has dropdb privileges. How can i  
restrict this?
Perhaps there is a recommended method to disable dropdb? Can anyone  
suggest?

The adminuser has no login privileges so by removing dropdb this  
should remove the possibility for any hacker chaos other than creating  
more databases?

Thanks in advance for any advice,
Ben

Sleep often seems a better problem solver than thinking. Create  
databases ahead of time and assign at the appropriate time.

Begin forwarded message:

> From: Ben Eliott <ben(dot)apperrors(at)googlemail(dot)com>
> Date: 2 March 2010 18:22:17 GMT
> To: pgsql-general(at)postgresql(dot)org
> Subject: createdb but revoke dropdb
>
> Hi,
> In using 8.3. I'm trying to set up programmatic database creation  
> but is there a way that the user creating the databases can be  
> restricting from dropping them?
>
> I have two roles, 'adminuser' with createdb permission, and 'dbuser'  
> a user with CRUD privileges.
>
> adminuser is a member of the dbuser role, this seems to allow  
> adminuser to createdb databases for dbuser with:
> createdb -U adminuser -O dbuser  new_database_name
> Adding .pgpass to the linux user's home directory allows createdb to  
> work without additional user input.
>
> But now it seems the linux user also has dropdb privileges. How can  
> i restrict this?
> Perhaps there is a recommended method to disable dropdb? Can anyone  
> suggest?
>
> The adminuser has no login privileges so by removing dropdb this  
> should remove the possibility for any hacker chaos other than  
> creating more databases?
>
> Thanks in advance for any advice,
> Ben
>

On 02/03/10 18:22, Ben Eliott wrote:
> I have two roles, 'adminuser' with createdb permission, and 'dbuser' a
> user with CRUD privileges.
>
> adminuser is a member of the dbuser role, this seems to allow adminuser
> to createdb databases for dbuser with:
> createdb -U adminuser -O dbuser new_database_name
> Adding .pgpass to the linux user's home directory allows createdb to
> work without additional user input.
>
> But now it seems the linux user also has dropdb privileges. How can i
> restrict this?
> Perhaps there is a recommended method to disable dropdb? Can anyone
> suggest?

 From the SQL reference page for "GRANT"
"The right to drop an object, or to alter its definition in any way, is 
not treated as a grantable privilege; it is inherent in the owner, and 
cannot be granted or revoked. (However, a similar effect can be obtained 
by granting or revoking membership in the role that owns the object; see 
below.) The owner implicitly has all grant options for the object, too."

Don't make "dbuser" the owner of the database, make "adminuser" the 
owner, then grant whatever top-level privileges dbuser needs. Make sure 
you don't have adminuser as an automatic login through .pgpass

> The adminuser has no login privileges so by removing dropdb this should
> remove the possibility for any hacker chaos other than creating more
> databases?

Or deleting/modifying all your data, presumably. If you don't trust the 
linux user account, don't give it automatic login.

-- 
   Richard Huxton
   Archonet Ltd

Hi,

Thank-you for coming back and your advice. I understand what you mean.  
However, in order to run the script without additional user  
input, .pgpass is always needed. One way or another, which ever way i  
try and twist this, something has to give on security.  Perhaps it  
would be just about ok-ish if I could restrict the linux user to just  
creating databases, but the privilege to add a database means the  
privilege to drop them too. And ok-ish isn't great either.

So, rather than fight this  I think perhaps instead another approach -  
to pre-prepare sets of databases ahead of time and then, rather than  
create them programmatically, just assign them programmatically  
instead. It doesn't exactly solve the original problem, but I think i  
prefer it from a security standpoint anyhow.

Ben

On 3 Mar 2010, at 09:17, Richard Huxton wrote:

> On 02/03/10 18:22, Ben Eliott wrote:
>> I have two roles, 'adminuser' with createdb permission, and  
>> 'dbuser' a
>> user with CRUD privileges.
>>
>> adminuser is a member of the dbuser role, this seems to allow  
>> adminuser
>> to createdb databases for dbuser with:
>> createdb -U adminuser -O dbuser new_database_name
>> Adding .pgpass to the linux user's home directory allows createdb to
>> work without additional user input.
>>
>> But now it seems the linux user also has dropdb privileges. How can i
>> restrict this?
>> Perhaps there is a recommended method to disable dropdb? Can anyone
>> suggest?
>
> From the SQL reference page for "GRANT"
> "The right to drop an object, or to alter its definition in any way,  
> is not treated as a grantable privilege; it is inherent in the  
> owner, and cannot be granted or revoked. (However, a similar effect  
> can be obtained by granting or revoking membership in the role that  
> owns the object; see below.) The owner implicitly has all grant  
> options for the object, too."
>
> Don't make "dbuser" the owner of the database, make "adminuser" the  
> owner, then grant whatever top-level privileges dbuser needs. Make  
> sure you don't have adminuser as an automatic login through .pgpass
>
>> The adminuser has no login privileges so by removing dropdb this  
>> should
>> remove the possibility for any hacker chaos other than creating more
>> databases?
>
> Or deleting/modifying all your data, presumably. If you don't trust  
> the linux user account, don't give it automatic login.
>
> -- 
>  Richard Huxton
>  Archonet Ltd