set up postgres users, roles and databases for rails

Question

This really has me stuck, I'm a bit of a noob trying to use postgres for rails 4

I have postgres installed:

$ psql --version
psql (PostgreSQL) 9.1.10
contains support for command-line editing

$ sudo apt-get install libpq-dev worked fine

I have gem 'pg' in my gemfile

My config/database.yml looks like so:

development:
  adapter: postgresql
  encoding: unicode
  database: myafricastyle_development
  host: localhost
  pool: 5
  username: myafricastyle
  password: myafricastyle

Create the database: $ sudo -u postgres createdb myafricastyle_development

Here are the databases:

$ psql
psql (9.1.10)
Type "help" for help.

jasonshark=# \l
                                           List of databases
           Name            |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------------------+------------+----------+-------------+-------------+-----------------------
 jasonshark                | jasonshark | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 myafricastyle_development | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres                  | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0                 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                           |            |          |             |             | postgres=CTc/postgres
 template1                 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                           |            |          |             |             | postgres=CTc/postgres
(5 rows)

But then when I try to set it up inside the rails project directory I get this:

$ rake db:create:all
FATAL:  password authentication failed for user "myafricastyle"
FATAL:  password authentication failed for user "myafricastyle"

Edit: This is my pg_hba.conf file:

local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Though when I made my edits I did sudo gedit pg_hba.conf so the folder looks kinda funky:

/etc/postgresql/9.1/main$ ls
environment  pg_hba.conf   pg_ident.conf    start.conf
pg_ctl.conf  pg_hba.conf~  postgresql.conf

What can I change?

mads · Accepted Answer · 2013-11-12 10:52:11Z

1

You need to create the user:

sudo -u postgres createuser myafricastyle

After creating the user you would also need to edit the pg_hba.conf file, as documented here:

http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

edited Nov 12, 2013 at 10:52

answered Nov 12, 2013 at 10:23

mads

2482 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Connor Leech Over a year ago

Okay i created a user with that command (not superuser, allowed to createdb and not allowed to create new roles) but I get the same password authentication failed error

AWM Over a year ago

The user 'myafricastyle' should own the database, not 'postgres'. You have to create a new role for 'myafricastyle' that includes the right to create databases. Then, let 'myafricastyle' create the table 'myafricastyle_development'.

AWM Over a year ago

To clarify: sudo su - postgres create role myafricastyle with createdb login password 'myafricastyle' createdb -O myafricastyle myafricastyle_development

Collectives™ on Stack Overflow

set up postgres users, roles and databases for rails

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related