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

Question

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.

score 70 · Answer 1 · edited Jul 25 '16 at 22:34

70

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.

edited Jul 25 '16 at 22:34

Thiago Negri

5,221
2
28
39

answered Mar 22 '14 at 17:48

luigi7up

5,779
2
48
58

1

You must specify the database when using heroku pg:reset – zero_cool Feb 28 '15 at 00:12
heroku pg:reset DATABASE Good instructions for resetting DB on Heroku: http://stackoverflow.com/questions/4820549/how-to-empty-db-in-heroku – Gideon Rosenthal Jun 24 '15 at 17:50
4

Also note that you need to run `heroku run rake db:migrate` afterwards because reset doesn't really mean reset, it just means drop. – Ka Mok Aug 29 '16 at 20:47
I got this error on a completely fresh heroku app. Seems odd? – stevec Aug 09 '20 at 07:16

score 6 · Answer 2 · answered Sep 24 '13 at 04:11

6

You can't drop your PG database on Heroku.

answered Sep 24 '13 at 04:11

Nick Veys

23,458
4
47
64

1

Then could you suggest what I might need to do to migrate and get the tables created? – Tom Maxwell Sep 24 '13 at 04:13
1

`heroku run rake db:migrate` doesn't do anything? You can "go nuclear" with `heroku rake run db:schema:load` which will re-load the full schema. – Nick Veys Sep 24 '13 at 04:14
Wait, are you using the Heroku DB from your local machine? If so, remove the `heroku` from in front of the commands and just run `rake db:schema:load`. – Nick Veys Sep 24 '13 at 04:16
2

`rake db:schema:load` seems to have worked, but `rake db:migrate` still does nothing. I'll keep looking around. Thanks for the help, Nick. – Tom Maxwell Sep 24 '13 at 04:21
`db:schema:load` will always apply the `schema.rb` file. `db:migrate` will apply the migrations in `/db/migrate` that haven't been applied (by date, according to the `schema_migrations` table in the database itself. If the database thinks it ran the migration (by having a date >= the latest migration) it won't run any, even if you change the latest migration. Which is why you should never change a migration once it's committed/has been run. Hope it helps! – Nick Veys Sep 24 '13 at 04:24

score 6 · Answer 3 · answered Nov 11 '15 at 19:10

6

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

heroku pg --help gets the name of commands for using postgres
heroku pg:reset DATABASE # resets the db
Answer the prompt to confirm

answered Nov 11 '15 at 19:10

user3162553

2,699
3
37
61

score 5 · Answer 4 · answered Jan 02 '19 at 12:29

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

score 4 · Answer 5 · answered Aug 17 '15 at 03:12

4

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

heroku pg:reset DATABASE

(note: must specify DATABASE as above)

answered Aug 17 '15 at 03:12

rattanak

1,498
2
13
17

score 3 · Answer 6 · answered Jan 23 '21 at 15:30

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

score 1 · Answer 7 · answered Dec 28 '20 at 21:20

1

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

answered Dec 28 '20 at 21:20

Fillype Farias

622
1
8
20

score 0 · Answer 8 · answered May 07 '14 at 19:20

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)

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

8 Answers8