On Jun 29, 2007, at 7:27 PM, Peter M. wrote:
dropdb: database removal failed: ERROR: must be owner of database
createdb: database creation failed: ERROR: permission denied to
As has been pointed out by snacktime, you didn’t grant createdb
privileges to user dev. You can fix that with the following
command while logged in as postgres:
alter user dev createdb;
Is it normal that the database is completely dropped when starting a
test. I thought it used to be that all the tables were just dropped.
Bad memory? I never had this problem with MySQL.
For Postgres, the test database is dropped and re-created and then
the schema is cloned from the development database.
Is there any way that I can set my test database user/pass to
something other than postgres/postgres. It doesn’t seem very portable
if another developer has their PostgreSQL installed under a different
system user/pass combination.
Any enlightenment greatly appreciated!
I take a different approach to database users for my Rails apps. I
submitted this to DHH as a patch but he said that it did not fit in
with the way he wanted to handle databases. I only mention this to
let you know that my approach is NOT the standard Rails way.
I have made a few patches to Rails that allow me to have a separate
development database user and an app database user. When running the
tests, Rails uses the development user to clean out the test database
and clone the development structure. The development user is also
used to load the fixtures and clean up after the tests. During the
tests, the app database user is used. I have also modified the start
up code for testing so that the test database is not dropped and
re-created. It is just emptied and then the development database is
cloned into it. I do this because I don’t like granting createdb
privileges to database users other that the superuser. When I create
a database, I make the development user be the owner of the database
so it has all the privileges needed to do anything to the database.
My philosophy about database users is that development users get to
create, alter, drop, tables and indexes and columns but the app user
should only be able to create, read, update, and delete data, not
make any structural updates to the database. Actually, I usually
go farther than that and only grant read access to most tables and
only grant insert, update, and delete access as needed. In my unit
tests, I make sure that the app database user can do everything it
needs to do and that it can not do anything I don’t want it to do.
For instance, in an e-commerce app, the Rails app should not be able
to update the catalog of products, only browse it. However, the
app does need to be able to create/update orders. Having a restrictive
database user for the Rails app gives me another layer of protection
against abuse of the web site.
Using Postgres makes my changes relatively easy. It is much harder
to get MySQL to cooperate because of the weird way it handles database
Kim S. - principal, Shrier and Deihl - mailto:[email protected]
Remote Unix Network Admin, Security, Internet Software Development
Tinker Internet Services - Superior FreeBSD-based Web Hosting