I’ve normally been a mysql user, and know just enough admin stuff with mysql to get rails started up. Today, I taught mysql just enough postgres admin stuff to get up and running with postgres. Using homebrew complicates the process slightly.
Here’s what you expect from reading stackoverflow:
brew install postgresql
psql -U postgres
This won’t work. Neither will any solution like:
sudo su - postgres psql
The reason is that a lot of people are writing stack overflow from linux. Most linux package managers perform the following set of operations to install postgres:
- create a postgres user
- su to the postgres user
- install postgres
The documentation for postgres tells you that You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account in order to access the default admin account.
Homebrew installs run as the a normal user account, by default. My default account name on my mac os box is james. As such, the default postgres admin login is james:
psql -U james
and that gets me logged in. The prompt shows a # sign when you’re logged in as the superuser role:
james$ psql
psql (9.3.4)
Type "help" for help.
james=#
====
What do you need to do now to get postgres set up for rails? Let’s assume you’re create a rails app called unimportant. Here’s what you need to do:
rails new unimportant --database=postgresql
cd unimportant
cat config/database.yml
Note that in database.yml, you’re specifically told that the database names you need to set up are:
development:
<<: *default
database: unimportant_development
test:
<<: *default
database: unimportant_test
production:
<<: *default
database: unimportant_production
username: unimportant
password:
So you need to make 3 new databases, and a user named “unimportant” who can access the production database. Assuming you’re running everything locally, here’s what you need to know. Postgres comes with some command line tools for making databases a little easier; it’s equivalent to writing the sql yourself:
createdb unimportant_development
createdb unimportant_test
createdb unimportant_production
Postgres also comes with a tool for making new users. We need an unimportant user:
createuser unimportant
You’re not done yet, though, because the unimportant user doesn’t have permission to write to the unimportant production database. First, let’s look at what postgres sees as permissions for our users. From the postgres pdf, chapter 20:
The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.
So we’re actually looking for how to view roles. From the same chapter: To determine the set of existing roles, examine the pg_roles system catalog, for example SELECT rolname FROM pg_roles;
The psql program’s \du meta-command is also useful for listing the existing roles.
Let’s try it out:
james$ psql
psql (9.3.4)
Type "help" for help.
james=# \q
greentreeredsky:unimportant james$ psql -U james
psql (9.3.4)
Type "help" for help.
james=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------+-----------
james | Superuser, Create role, Create DB, Replication | {}
unimportant | Create DB | {}
james=# SELECT rolname FROM pg_roles;
rolname
-------------
james
unimportant
(2 rows)
james=# SELECT * FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
-------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
james | t | t | t | t | t | t | t | -1 | ******** | | | 10
unimportant | f | t | f | f | f | f | f | -1 | ******** | | | 16389
(2 rows)
This is a bit difficult to read, sorry, but the last piece is telling us all the permissions that the admin user and the unimportant user have. In order for the unimportant user to actually be capable of managing the rails production db, it needs the rolcreatedb permission. To be able to log in to the psql command console you’ll need the rolcanlogin permission too. The postgresql documentation tells us to use the ALTER sql command to grant this, rather than the mysql GRANT command. The docs for ALTER tell us what roles we can add.
james=# alter role unimportant login;
james=# alter role unimportant createdb;
All done!
Jam and Bees 