Rake db:reset 'user does not have CONNECT privilege'

45
votes

So I'm using Heroku Postgres in my Rails app, but I'm not hosting my app on Heroku itself. I used the Active Record connection details from Heroku in my database.yml, and it looks like this:

development:
adapter: postgresql
encoding: unicode
pool: 5
database: [database]
username: [username]
password: [password]
host: ec2-54-227-243-78.compute-1.amazonaws.com
port: 5432

However, now I'm trying to rake db:migrate my app so the database gets all set up with my models. Running that command doesn't do anything, so I tried rake db:reset and I get this:

Couldn't drop df2cokjfj0k4vu : #<PG::Error: FATAL:  permission denied for database "postgres"                                                                  
DETAIL:  User does not have CONNECT privilege.

df2cokjfj0k4vu already exists
-- initialize_schema_migrations_table()
-> 1.3997s
-- assume_migrated_upto_version(20130924040351, ["/home/action/braindb/db/migrate"])
-> 0.0882s

Any idea what I'm doing wrong. I'm still pretty new to Rails so sometimes I forget how to get my Postgres database setup properly when migrating hosts.

8
ruby-on-railsrubypostgresqlheroku

8 Answers

69
votes

Use heroku pg:reset DATABASE instead as noted in https://devcenter.heroku.com/articles/rake

You cannot drop databases in Heroku with rake db:reset because user has no privileges.

6
votes

I recently had this problem and resolved it through the following steps.

  1. heroku pg --help gets the name of commands for using postgres

  2. heroku pg:reset DATABASE # resets the db

  3. Answer the prompt to confirm

5
votes

Like others I had a similar problem but running pg:reset did not help: that's where I ran into the user does not have CONNECT privilege issue.

1) heroku pg:reset you will be prompted to enter the project name to confirm.

2) From Heroku's docs run heroku rake db:schema:load

4
votes

I had the same problem. I fixed it by running:

heroku pg:reset DATABASE

(note: must specify DATABASE as above)

2
votes

If you got this because you were running rake db:create, instead, simply skip to the migration. For whatever reason, postgresql on heroku doesn't require the rake db:create step as may be required on local installations.

In other words, instead of

heroku run rake db:create db:migrate db:seed

run this instead

heroku run rake db:migrate db:seed
0
votes

For one of my apps, upgrading to the first paid tier of a Heroku database seemed to work for me: https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups#provision-new-plan

Reason was that 'heroku pg:info' revealed that I was over my row limit as shown here: Rows: 12392/10000 (Write access revoked)

0
votes

The solution to me was to just go straight to migration because the database is already created (don't go on "heroku run rails db:create")

heroku run rails db:migrate -a YOURAPPNAME