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
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
Or deleting/modifying all your data, presumably. If you don't trust the
linux user account, don't give it automatic login.
In response to
pgsql-general by date
|Next:||From: Richard Huxton||Date: 2010-03-03 09:41:18|
|Subject: Re: FSM and VM file|
|Previous:||From: Ben Eliott||Date: 2010-03-03 09:07:40|
|Subject: Fwd: createdb but revoke dropdb|