Db create all ошибка postgresql

I am trying to create a Rails app that uses PostgreSQL. Here is a description of what I did.

PostgreSQL setup:
I installed PostgreSQL 9.1.3 via the ppa:pitti/postgresql maintained by Martin Pitt. There was PostgreSQL 8.4 installed before; I am not sure if it is still installed or gone.

• I added a database user with superuser rights to the database that has the same name as my Ubuntu account.
• I start the database daemon with sudo service postgresql start.
• I installed pgadmin3, Version 1.14.0 Beta 1 via ppa:rhonda/pgadmin3 maintained by Gerfried Fuchs.
• I can connect via pgadmin3 using my user account and password and port 5433.

My postgres configuration in pg_hba.conf is as follows (removed comments for readability).

[...]
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Rails setup:
Now I want to create a Rails application that uses PostgreSQL.

• I installed Ruby 1.9.3-p125 via RVM.
• I installed Rails 3.2.3 into the Gemset ruby-1.9.3-p125@global.
• I created a .rvmrc and Gemset for the application.
• I created a Rails application via rails new my_test_app -d postgresql.
• I configured the user name and password in config/database.yml for development and test and removed production.
• I configured host: localhost and port: 5433 in config/database.yml.

Here is the content of my config/database.yml (removed comments for readability).

development:
  adapter: postgresql
  encoding: unicode
  database: my_test_app_development
  pool: 5
  username: johndoe
  password: password    
  host: localhost
  port: 5433

test:
  adapter: postgresql
  encoding: unicode
  database: my_test_app_test
  pool: 5
  username: johndoe
  password: password

Problem:
However, when I run bundle exec rake db:create:all I receive the following error message.

could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
[...]
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode",
"database"=>"my_test_app_test", "pool"=>5, "username"=>"johndoe",
"password"=>"password"}

Question:
Why is the port different to the one I use when I successfully connect via pgadmin3?

This is probably not the main reason why the create_all() method call doesn’t work for people, but for me, the cobbled together instructions from various tutorials have it such that I was creating my db in a request context, meaning I have something like:

# lib/db.py
from flask import g, current_app
from flask_sqlalchemy import SQLAlchemy

def get_db():
  if 'db' not in g:
    g.db = SQLAlchemy(current_app)
  return g.db

I also have a separate cli command that also does the create_all:

# tasks/db.py
from lib.db import get_db

@current_app.cli.command('init-db')
def init_db():
  db = get_db()
  db.create_all()

I also am using a application factory.

When the cli command is run, a new app context is used, which means a new db is used. Furthermore, in this world, an import model in the init_db method does not do anything, because it may be that your model file was already loaded(and associated with a separate db).

The fix that I came around to was to make sure that the db was a single global reference:

# lib/db.py
from flask import g, current_app
from flask_sqlalchemy import SQLAlchemy

db = None
def get_db():
  global db
  if not db:
    db = SQLAlchemy(current_app)
  return db

I have not dug deep enough into flask, sqlalchemy, or flask-sqlalchemy to understand if this means that requests to the db from multiple threads are safe, but if you’re reading this you’re likely stuck in the baby stages of understanding these concepts too.

createdb needs to connect to a database to issue a CREATE DATABASE SQL statement, and by default it will use:

• a Unix socket domain connection
• the current username from the shell as the database user (so postgres in your case)
• template1 as the database

When it does that, in your case you get this error message:

createdb: could not connect to database template1: FATAL: no
pg_hba.conf entry for host «[local]», user «postgres», database
«template1″, SSL off»

It indicates that postgres as a database user does not have the permission to connect through a Unix domain socket (that’s what [local] means).

This is unusual, since in general, postgres installers on Unix tend to install a pg_hba.conf starting with this rule:

local   all       postgres       peer

Meaning that the postgres Unix user has the right to connect locally to any database as the postgres database user, which suits the needs of administrating databases, including creating new ones.

In fact your pg_hba.conf does not look like a default one. The simplest way to proceed to immediately solve the problem of database creation would be to edit it, add the above line as the first rule, and reload the postgres service.

Today I tried to setup Postgres as my db for my Rails project, and met with this error:

Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_cute_db", "pool"=>5, "username"=>"rongjun", "password"=>nil}
PG::Error: ERROR:  permission denied to create database

I want to write down how I solved it.

What I intended to do is the create a db called my_cute_db with rake command, rake db:setup. However, it’s giving error like shown above.

Here’s my database.yml

development:  
adapter: postgresql  
encoding: unicode  
database: my_cute_db  
pool: 5  
username: rongjun  
password:

My username is rongjun. And the error is telling me that “permission denied to create database“, so I have to go into psql command-line interface to find out the permission of my role.

my_terminal# psql postgres
postgres=# du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    |                                                | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

du command is listing down the role names and their privileges. We can see that rongjun is not having any privileges. On the other hand daodaowang is able to Create DB. Now we would like to enable rongjun to be able to Create DB just like daodaowang.

postgres=# ALTER ROLE rongjun WITH CREATEDB;

Then we run du again to check:

postgres=# du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    | Create DB                                      | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

rongjun is now having the correct privilege to Create DB. Let’s run rake db:create again, it will do the job without error now. You can check by running psql -l command to list down all databases you have.

After creating the db, you can grant more privileges to your user:

postgres=# GRANT ALL PRIVILEGES ON DATABASE my_cute_db to rongjun;

Reference:
For how to create database refer to: http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

For creating the user with ‘Create DB’ privilege directly without having to ALTER it later:

create user any_username with password 'any_password' createdb;