Permission denied with Heroku pg:pull to my local database

Question

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"

Are you sure your user has the permission to create databases? — Gabriel Ilharco, Jan 08 '16 at 00:45
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, Jan 08 '16 at 00:45
Yeah, did that. postgres was the superuser so i changed that password. Then re-ran the command. See the edit to the question. — Programmingjoe, Jan 08 '16 at 00:51
You should change `peer` to `md5` for user `postgres` in that file — Gabriel Ilharco, Jan 08 '16 at 00:54
Generally in `(path to postgresql)/postgresql/9.X/main/pg_hba.conf` — Gabriel Ilharco, Jan 08 '16 at 00:55
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100097/discussion-between-joe-and-gabriel-ilharco). — Programmingjoe, Jan 08 '16 at 00:56

score 2 · Accepted Answer · edited May 23 '17 at 12:34

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.

So I tried restarting postgres after the md5 change and that didn't work. Then I tried making a new superuser and running the command with that and the command still fails. Do you think you could walk me through what you did in more detail? What each piece of the command is supposed to be etc? After I run the command it asks for the password to something and I'm not exactly sure what that is but I have tried all the passwords I use. Do this happen to you? — Programmingjoe, Jan 22 '16 at 19:02
Also, if I don't specify a user what user is it going to run the command with? At this point I have postgres, myuser, mynewsuperuser. — Programmingjoe, Jan 22 '16 at 19:10
What lines do you have in pg_hba.conf for `local` connections (1st column)? — Ed Lee, Feb 01 '16 at 22:27
For reference, if you **don't** specify `PGUSER` (and make sure you're not exporting a value either -- you can check this with `echo $PGUSER`), then it will probably be assumed that you are trying to connect as your OS login (i.e., `echo \`whoami\``) — Ed Lee, Feb 01 '16 at 22:33
I ended up making a new super user, changing the password and username so I knew exactly what it was, then re-running the command again. — Programmingjoe, Feb 01 '16 at 22:55

Permission denied with Heroku pg:pull to my local database

1 Answers1