Re: createdb but revoke dropdb

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