Skip site navigation (1) Skip section navigation (2)

Re: createdb but revoke dropdb

From: Richard Huxton <dev(at)archonet(dot)com>
To: Ben Eliott <ben(dot)apperrors(at)googlemail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: createdb but revoke dropdb
Date: 2010-03-03 09:17:11
Message-ID: 4B8E2917.4050805@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2010-03-03 09:41:18
Subject: Re: FSM and VM file
Previous:From: Ben EliottDate: 2010-03-03 09:07:40
Subject: Fwd: createdb but revoke dropdb

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group