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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
