Setting up a PostgreSQL db for Ruby on Rails locally

Question

I'm stumbling on the real basics because I cannot find any clear directions.

I have installed Postgres.app 9.3.5 on OS X.

The documentation tells me how to create a database, createdb mydb, but to use it I'd need a PostgreSQL user and a password for that user.

What are the basic steps to create a PostgreSQL user with a password (and a database if different than the above) on a local PostgreSQL install?

• Craig Ringer: Thanks for including your Pg version. Your operating system would be relevant too though. The mention of Postgres.app lets me guess some version of OS X, but ... yeah.

Answer 1

The Postgres.app documentation used to explain this, but appears to have been split up and cut short recently.

You don't need a dedicated user; you can simply use the default postgres user. However, because that's a superuser, you're wise to create less privileged users for your apps.

You generally want to make a database owned by the app's user, as Rails expects to be able to use the same user to change the schema with migrations as it does to run queries. (It's not great security-wise, but it's the path of least resistance with Rails).

So create the user, then create the db.

Here's how it should look. Don't type the prompts ($ or postgres=#), they're there to show what you're running. Just enter the commands after the prompts.

$ psql -q
postgres=# CREATE USER myapp WITH ENCRYPTED PASSWORD 'mypassword';
postgres=# CREATE DATABASE myappdb OWNER myapp;
postgres=# \q

You may now specify the user, password and database in your database.yml.

(Note that Postgres.app may be configured not to require passwords; in this case the password will be ignored by PostgreSQL).

Answer 2

createdb mydb
psql -s mydb
create user space_pilot password 'hello';
GRANT ALL PRIVILEGES ON DATABASE mydb TO space_pilot;