Need some help creating a database sandbox...

"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