Re: [GENERAL] 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.
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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need some help creating a database sandbox...
Eric D. Nielsen [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Need some help creating a database sandbox...
On Aug 2, 2005, at 11:33 PM, Tom Lane wrote: Eric D. Nielsen [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
