Permission denied with Heroku pg:pull to my local database

2
votes

I have a live app running with a database on Heroku but the database needs an update so my plan is to pull the database, update it, test it on my development server, then push it to production. With the first step I am struggling with pulling the heroku db to overwrite my local db. I am using the following command:

PGUSER=userforlocaldb PGPASSWORD=passwordforlocaldb heroku pg:pull HEROKU_POSTGRESQL_herokudbcolor localdbname --app herokuappname

I get this error message:

 !    createdb: database creation failed: ERROR:  permission denied to create database
 !    
 !    Unable to create new local database. Ensure your local Postgres is working and try again.

I have also tried filling the PGUSER and PGPASSWORD values with my heroku db but that did not work.

What am I missing here?

EDIT: I changed the password for superuser postgres and re-ran the command. I get this error: Peer authentication failed for user "postgres"

1
postgresqlherokupull
Are you sure your user has the permission to create databases? – Gabriel Ilharco
How would I check that? I believe so as this is the only user I remember using. EDIT: Ahh, the user im using is not the superuser – Programmingjoe
Yeah, did that. postgres was the superuser so i changed that password. Then re-ran the command. See the edit to the question. – Programmingjoe
can you show me your pg_hba.conf file please? – Gabriel Ilharco
You should change peer to md5 for user postgres in that file – Gabriel Ilharco

1 Answers

2
votes

I am able to reproduce your issue, but I am also able to correct it with Gabriel's suggestion to change the access method for postgres from peer to md5. You have to remember to restart Postgres whenever you change pg_hba.conf. On Ubuntu, this should do it:

sudo service postgresql restart

Another solution:

Alternatively, if user permissions are not a huge concern for you (e.g., you have no other users on your system), such as in a VM, you can give yourself PostgreSQL-superuser privileges:

sudo -u postgres createuser -s $YOUR_USERNAME [-P]

This will then allow you to create a new db (localdbname) as well as load the plpgsql extension which seems to be necessary in this case. (It appears to be insufficient to have the CREATEDB role because of plpgsql.)

Now you should be able to run your original command without PGUSER or PGPASSWORD:

heroku pg:pull HEROKU_POSTGRESQL_herokudbcolor localdbname --app herokuappname

EDITED ANSWER:

If you are still being prompted for a password when running the heroku pg:pull command, you will want to set a password for the new Postgres user, which may be the same as your OS login user (echo \whoami``). You can do this by including the -P flag in the createuser command above, which will prompt you for a password for the new user, or by logging in as a Postgres superuser (e.g., postgres) and setting the password in psql.

ADDENDUM/CAVEAT:

It may not be a good idea to combine the two solutions because you may have issues running sudo -u postgres ... if the postgres user is authenticated by md5 but no password is set, which is usually the case on Linux installations by default. Personally I find it more convenient to issue commands as myself (whoami) as a Postgres superuser than as the postgres superuser, once the new user/role is created.

Either way, to run the heroku pg:pull command, I think you're going to need to set a password the user and also have superuser privileges.