# Postgres + Tests + Permissions = Confusion **URL:** \ **Category:** Rails\ **Created:** [June 30, 2007, 3:27am UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212 "2007-06-30T03:27:43Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\ **Post date:** [June 30, 2007, 3:27am UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212/1 "2007-06-30T03:27:43Z")
Hi, I’m trying PostgreSQL for the first time and writing a Rails app for the first time in a long time. I have PostgreSQL installed on my Mac with owner user “postgres”. The password for this user on my Mac is also “postgres”. Working as this postgres user on my Mac, I made a database called foo\_test and granted all permissions to db user “dev” with password “pass”. When I work on my Rails app I am Mac user “peter”. In My rails app database.yml file I have test: adapter: postgresql database: foo\_test username: dev password: pass When I run “rake” I see dropdb: database removal failed: ERROR: must be owner of database foo\_test createdb: database creation failed: ERROR: permission denied to create database If I change my database.yml file to the following then all seems to work well. test: adapter: postgresql database: foo\_test username: postgres password: postgres Who is getting denied dropping db permission? Mac user “peter” or is it db user “dev”? 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. 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! Thanks, Peter ---
**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\ **Post date:** [June 30, 2007, 5:58am UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212/2 "2007-06-30T05:58:52Z")
> Working as this postgres user on my Mac, I made a database called > foo\_test and granted all permissions to db user “dev” with password > “pass”. Actually you didn’t give them all permissions or it would have worked. Maybe all the ones you know about, but not all:) The postgresql docs are actually very good and it would pay to read up on database roles and privileges so you have the basics. > Who is getting denied dropping db permission? Mac user “peter” or is > it db user “dev”? user dev, because dev is not the owner and also not given the superuser role. One of those is required to drop databases. > 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. They won’t unless they changed the default user, and if they do that it’s on their heads, since it takes a bit of effort with no good reason that I can think of. It’s probably a good idea to create another user for your databases though. Just create a user and give them the createdb role. When they create databases they will have ownership so they can drop them also. And don’t give up on Postgres, you won’t ever look back at mysql after using it a while, and the mailing lists have some very sharp people on them if you ever need to tune your database or run into problems. Every time I do work for people using mysql I have to keep my mouth shut and stop myself from saying ‘well if you used postgresql you could do this…’ Chris ---
**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\ **Post date:** [June 30, 2007, 6:40am UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212/3 "2007-06-30T06:40:54Z")
Hi Chris, Thanks for the info. I’ll look into the createdb role. PostgreSQL users keep saying things about it being better than MySQL but I don’t know what specifically is better for a Rails app. Perhaps I’ll find out. Peter ---
**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\ **Post date:** [June 30, 2007, 7:41am UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212/4 "2007-06-30T07:41:57Z")
On 6/29/07, Peter M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Hi Chris, > > Thanks for the info. I’ll look into the createdb role. > > PostgreSQL users keep saying things about it being better than MySQL > but I don’t know what specifically is better for a Rails app. Perhaps > I’ll find out. One of the biggest pluses is the help that’s available for free when you need to tune your database or find out how to optimize your queries. The level of clue on the mailing lists is just so much higher in general. There is a much higher standard for keeping your data safe imo. Documentation is also very good. Lots of the advanced stuff you might never need, but when you do it’s there and has probably been there for years. Chris ---
**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\ **Post date:** [June 30, 2007, 9:35pm UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212/5 "2007-06-30T21:35:59Z")
On Jun 29, 2007, at 7:27 PM, Peter M. wrote: … > dropdb: database removal failed: ERROR: must be owner of database > foo\_test > createdb: database creation failed: ERROR: permission denied to > create database 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. Yes. > 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 permissions. ## Kim Kim S. - principal, Shrier and Deihl - [mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) Remote Unix Network Admin, Security, Internet Software Development Tinker Internet Services - Superior FreeBSD-based Web Hosting [http://www.tinker.com/](http://www.tinker.com/) ---
**Author:** ![dracos](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ea666f/32.png) [@dracos](https://www.ruby-forum.com/u/dracos)\ **Post date:** [June 30, 2007, 10:54pm UTC](https://www.ruby-forum.com/t/postgres-tests-permissions-confusion/108212/6 "2007-06-30T22:54:56Z")
I think you have some good suggestions here. I just want to chime in with my approach. I install postgresql using the usual instructions. From the postgres account, I create a user that has createdb privileges – but not super user. I then create the database as the new user. In the database.yaml file, I put the new user name and password. This seems as secure as any other method.