10

I am unable to use rails with postgres. The database wont create:

sayth@sayth-TravelMate-5740G:~/testapp2$ rake db:create:all
PG::InsufficientPrivilege: ERROR:  permission denied to create database
: CREATE DATABASE "testapp2_development" ENCODING = 'unicode'
....
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"testapp2_production", "pool"=>5, "username"=>"testapp2", "password"=>nil}

So I am following the solution here https://stackoverflow.com/a/8639649

These are the steps i am having issue with.

$ psql -d postgres
postgres=# create role app_name login createdb;
postgres=# \q

However when I am in the psql shell to give the create role with createdb ability it fails auth.

sayth@sayth-TravelMate-5740G:~/testapp2$ psql -d postgres
psql (9.2.4)
Type "help" for help.

postgres=> create role sayth createdb;
ERROR:  permission denied to create role
postgres=>

How then do I create the auth?

Edit Update Reviewing http://www.postgresql.org/docs/9.2/static/tutorial-createdb.html And http://www.postgresql.org/docs/9.2/static/database-roles.html

But cannot use root to create accounts as it doesn't exist and I need to be root to create it.

sayth@sayth-TravelMate-5740G:~$ sudo psql -d postgres
[sudo] password for sayth: 
Sorry, try again.
[sudo] password for sayth: 
psql: FATAL:  role "root" does not exist
Community
  • 1
  • 1
sayth
  • 6,696
  • 12
  • 58
  • 100

3 Answers3

39

Add this using during gitlab_ci DB setup. I solve the problem as described by @Audrius Meškauskas on Ubuntu 12.04:

Connect as Admin

sudo -u postgres psql -d template1

Alter role

ALTER ROLE gitlab_ci WITH CREATEDB;

Re-run the task

sudo -u gitlab_ci -H bundle exec rake db:setup RAILS_ENV=production
Édouard Lopez
  • 40,270
  • 28
  • 126
  • 178
7

The username / password you use lack the administrator rights that are necessary for creation users and databases.

Login as administrator and fix the permissions. If you have administrator login credentials, login and use ALTER ROLE to fix the rights on that account.

Audrius Meškauskas
  • 20,936
  • 12
  • 75
  • 93
  • Meskauskas. If this a default install and the user I have created is Sayth. Is an admin created by default? – sayth Aug 17 '13 at 14:28
  • 2
    Please do take an hour out to skim the extensive PostgreSQL manuals. You'll find life a lot easier if you have a few basic facts to hand. The admin user is probably called "postgres" and you can probably use "sudo" to run psql as that user and create the other user. Then read up on pg_hba.conf, authentication and roles. – Richard Huxton Aug 17 '13 at 15:10
  • CREATEDB was enough to get mine working... alter ROLE newuser with CREATEDB; – Jeff Ancel Apr 07 '14 at 01:32
  • A much better answer is given by Edouard – Anwar Sep 27 '15 at 15:38
0

Open the interface with psql postgres and try creating the user now.

Galuoises
  • 2,630
  • 24
  • 30