Postgres + Tests + Permissions = Confusion

Rails
1

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

2

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

3

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

4

On 6/29/07, Peter M. [email protected] 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

5

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:[email protected]
Remote Unix Network Admin, Security, Internet Software Development
Tinker Internet Services - Superior FreeBSD-based Web Hosting
http://www.tinker.com/

6

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.