Обсуждение: Need some help creating a database sandbox...

I'm trying to setup a "safe" testing database environment for some
unit testing of a web application.  I would like to have the unit
tests restore the database to a known state before every test.  The
simplest way I thought would be to have the testing tools drop/create
the testing database on every test case, and then populate the
database from a specified file.  However I don't want to give the
test user superuser privileges.  Thus I don't think I can restrict it
to only drop/create a single named DB.

My next thought was to code up a "DELETE ALL" script that would
delete all entities in the database.  However it seems easy to miss
something and its not robust against schema changes, even though it
can be looked down to the test_db_owner.

A third thought would be to run a second cluster containing only the
test database(s).  Then the users wouldn't be shared, so even if it
someone connected to the wrong DB it would lack any permissions.  I
don't have much experience running multiple clusters, however.  So I
don't know if thats opening up another whole can of worms.

Any suggestions?

Thanks
Eric

"Eric D. Nielsen" <nielsene@mit.edu> writes:
> ... simplest way I thought would be to have the testing tools drop/create
> the testing database on every test case, and then populate the
> database from a specified file.  However I don't want to give the
> test user superuser privileges.  Thus I don't think I can restrict it
> to only drop/create a single named DB.

CREATEDB is a pretty restricted privilege --- I don't actually see
the problem here?

            regards, tom lane

On Aug 2, 2005, at 11:33 PM, Tom Lane wrote:

> "Eric D. Nielsen" <nielsene@mit.edu> writes:
>
>> ... simplest way I thought would be to have the testing tools drop/
>> create
>> the testing database on every test case, and then populate the
>> database from a specified file.  However I don't want to give the
>> test user superuser privileges.  Thus I don't think I can restrict it
>> to only drop/create a single named DB.
>>
>
> CREATEDB is a pretty restricted privilege --- I don't actually see
> the problem here?
>
>             regards, tom lane
>

I was equating CREATEDB as superuser.  Its not I see.  So if a user
has CREATEDB, but not CREATEUSER, I should be safe.

Thanks!

Eric

> I'm trying to setup a "safe" testing database environment for
> some unit testing of a web application.  I would like to have
> the unit tests restore the database to a known state before
> every test.  The simplest way I thought would be to have the
> testing tools drop/create the testing database on every test
> case, and then populate the database from a specified file.
> However I don't want to give the test user superuser
> privileges.  Thus I don't think I can restrict it to only
> drop/create a single named DB.

No, AFAIK there is no way to do that.


> My next thought was to code up a "DELETE ALL" script that
> would delete all entities in the database.  However it seems
> easy to miss something and its not robust against schema
> changes, even though it can be looked down to the test_db_owner.

If you're giong to drop *everything* in the db, you can drive something
off the system tables or information schema. Like:

SELECT 'DROP TABLE ' || table_schema || '.' || table_name FROM
information_schema.tables WHERE table_type='BASE TABLE' AND table_schema
NOT IN ('pg_catalog','information_schema')

And then feed the generated script back through a different psql prompt.

Similar scripts for other object types of coruse (views, functions etc).
It might be easier to drive it off the system tables directly instead of
information schema, if you can live with possible backend version
dependencies.


> A third thought would be to run a second cluster containing
> only the test database(s).  Then the users wouldn't be
> shared, so even if it someone connected to the wrong DB it
> would lack any permissions.  I don't have much experience
> running multiple clusters, however.  So I don't know if thats
> opening up another whole can of worms.

Just make them run in completely different directories, and use
different accouts to start each cluster (each only having permissions on
it's own data directory, of course). It's going to mean two sets of
shared buffer caches etc, so you may need to trim the memory values in
your postgresql.conf, and of course run them on different ports, but it
should work just fine.

//Magnus