Обсуждение: dropdb ; createdb equivalent without createdb permission?

Hello,

I would like to be able to grant a user the ability to completely
reset a database for testing purposes.  If possible, I would like the
user to be able to do a "dropdb" followed by a "createdb", but without
allowing this user to create any database.  In other words, I would
like the user to be granted createdb permission for only a particular
database.  I don't believe this is possible in postgresql.  Is there a
dropdb-followed-by-createdb equivalent the user could use?

Thanks,
Tim

On Mon, Jul 09, 2007 at 02:13:55PM -0400, Tim Olsen wrote:
> like the user to be granted createdb permission for only a particular
> database.  I don't believe this is possible in postgresql.  Is there a
> dropdb-followed-by-createdb equivalent the user could use?

The reason it isn't possible is because there's no such thing as "a
particular database" at createdb time.  It's just an empty database
with an arbitrary string (the name) identifying it.

You could, however, limit all of this by giving sudo access to the
person in question, where the sudo access is for a (set of) script(s)
that achieve what you want (e.g. scripts with the appropriate
createdb, psql -c "something" &c. inside them).  Obviously, if the
user can edit the scripts, then your intention is still foiled.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun

Tim Olsen wrote:
> Hello,
>
> I would like to be able to grant a user the ability to completely
> reset a database for testing purposes.  If possible, I would like the
> user to be able to do a "dropdb" followed by a "createdb", but without
> allowing this user to create any database.  In other words, I would
> like the user to be granted createdb permission for only a particular
> database.  I don't believe this is possible in postgresql.  Is there a
> dropdb-followed-by-createdb equivalent the user could use?

As an alternative, how about a separate database cluster. A user can run
their own copy of PostgreSQL - just initdb but set the directory to
someplace that user has appropriate access privilege. This might require
use of a different port if PostgreSQL is already running on that machine
but then your user is free to create, change, test and destroy as they
see fit.

Or I suppose you could drop...cascade all the user tables, views, etc.
in the database and then do a vacuum full.

Cheers,
Steve

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Mon, Jul 09, 2007 at 02:13:55PM -0400, Tim Olsen wrote:
>> like the user to be granted createdb permission for only a particular
>> database.  I don't believe this is possible in postgresql.  Is there a
>> dropdb-followed-by-createdb equivalent the user could use?

> You could, however, limit all of this by giving sudo access to the
> person in question, where the sudo access is for a (set of) script(s)
> that achieve what you want (e.g. scripts with the appropriate
> createdb, psql -c "something" &c. inside them).

A SECURITY DEFINER function (living in some other database of course)
could accomplish this without going outside Postgres.

            regards, tom lane

On Mon, Jul 09, 2007 at 02:42:17PM -0400, Tim Olsen wrote:
>
> I take it the privilege table directly references the database by some ID
> number?

Well, sort of.  It's going to be very hard to enforce anything in
advance, though.

>  The reason I ask is because this sort of privilege is possible in
> MySQL: just grant all privs on a database (not yet necessarily created) and
> the user can drop and create that database at will.  They probably store the
> privilege with a string for the database name instead of an ID of some sort.

Could be.  How you'd do ALTER DATABASE. . .RENAME TO under such a
scheme is sort of beyond me, though.  But anything I once knew about
MySQL has probably been replaced, so I'm not the one to ask.

> This is for software that we intend to distribute as open-source.  I'd like
> to keep the createdb (to any db) and sudo requirements for running tests to
> a minimum.

I think you're going to need to think about this more.  If people can
look at and modify the code, it's not at all clear to me how you're
going to limit their ability to createdb and such like.

> After further investigation, it looks like schemas might give me what I'm
> looking for.

Certainly inside a single database, schemas give you better control
than trying to limit createdb to particular effects.  But if they have
your code, then they can still do this.  Are you just trying to make
this newbie-friendly, or is this more a control attempt?  If the
latter, I fear you are going down the wrong road.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The very definition of "news" is "something that hardly ever happens."
        --Bruce Schneier

On Mon, Jul 09, 2007 at 02:50:02PM -0400, Tom Lane wrote:
> A SECURITY DEFINER function (living in some other database of course)
> could accomplish this without going outside Postgres.

Good point.  I was originally imagining a sort of scripted QA
environment, and sudo for that sort of thing is pretty useful.  But
for other applications, it's probably a lousy choice.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin

"Tim Olsen" <tolsen718@gmail.com> writes:
> I take it the privilege table directly references the database by some ID
> number?

You've got it backwards: there is no separate privilege table.  We
attach privilege ACLs to the catalog entries of the objects they describe.
Hence, no object, no ACL.

> The reason I ask is because this sort of privilege is possible in
> MySQL: just grant all privs on a database (not yet necessarily created) and
> the user can drop and create that database at will.

If you are looking for a MySQL equivalent, consider the idea that MySQL
"databases" are really schemas in our terminology.  So all you really
need is to give the user CREATE privs on the database he is working in
(ie, the right to create a schema) and he can create/drop/recreate his
schema at will.  With DROP CASCADE, he can get rid of everything within
the schema at a blow, so this is really pretty close to the same
functionality.

            regards, tom lane

> With DROP CASCADE, he can get rid of
> everything within
> the schema at a blow, so this is really pretty close to the same
> functionality.

but beware of cross-schema dependencies! e.g., a DROP SCHEMA CASCADE of
schema X containg a table that has a column defined using a domain from
schema Y will result either in that object (the domain) being dropped
from schema Y (if user has privileges on Y) or in a failure of the DROP
(if not)...