Re: createdb but revoke dropdb

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