11

I'm trying to run the heroku pg:pull command, but I can't seem to get the amazingly cryptic authentication process.

The command I'm running:

> heroku pg:pull app_name::RED localdb

I then get a password prompt, which I can't, for the life of me, figure out. After 2 guesses I get password authentication failed for user "Hanan", and that's it.

I tried Heroku's password, my Windows account password, every password I use, but nothing happens. I checked, and "Hanan" is not a role in Postgresql, so trying to change the password through psql doesn't work. I have no problem logging in to Postgresql through other roles, but it's this 'default' log-in process which I can't seem to crack.

Also, since I'm using windows, I'm not sure how to run commands like sudo -u postgres psql, which I see as a possible solution.

Will appreciate any help regarding this issue, I'm really frustrated by now...

hananamar
  • 1,166
  • 15
  • 25
  • Having the same issue... haven't found a solution yet. – aardvarkk Jan 29 '14 at 22:34
  • while `heroku pg:pull` is short and easy, as long as it's not working I'm trying to use the method described in link below to pull db to locally installed postgres, then use taps server to populate my app's db. This worked on my mac-book, but, as you can guess, not on my pc :/ link: http://stackoverflow.com/questions/5649868/is-there-a-faster-way-to-pull-production-data-from-heroku-than-taps – hananamar Jan 30 '14 at 09:01

6 Answers6

14

Apparently it's possible to set the environment variables PGUSER and PGPASSWORD, as described here.

However, this won't work on windows in the given syntax. To do this on windows run the following:

SET PGUSER=[pg_username]

SET PGPASSWORD=[pg_password]

after entering these two lines Postgres will log you in with the given authentication info, instead of trying to sign in with the windows username

Community
  • 1
  • 1
hananamar
  • 1,166
  • 15
  • 25
7

I've run into this problem a lot when running heroku pg:pull. The issue in my case was that the pg:pull command only works if my local PostgreSQL server has a password set.

To set a password, run psql localdb and execute this SQL:

ALTER USER my_user_name with password 'my_new_password';

(You won't necessarily be required to use this password all the time. Run psql localdb and see whether you're prompted; in my case, I can still log in to psql without the password.)

Now run heroku pg:pull --app my_heroku_app POSTGRESQL_COLOR localdb, and enter your new password (twice) when prompted.

joel3822021
  • 116
  • 1
  • 4
  • Unbelievable! For some reason this isn't necessary on my local laptop but it is on my desktop...you saved me! – bkunzi01 May 18 '16 at 02:39
  • @bkunzi01: This depends on the authentication method in /etc/postgresql//main/pg_hba.conf file. Change local/all/all from md5 to peer (and restart postgres cluster or whole service) and the password prompt will disapear. In my case this is neccessary, because (with md5 and proper answer for the current users postgres password) the task starts, but hangs a minute later with authentication failure. This is probably bug. But with peer authentication this works. (peer authentication, I think, works on linux only) – mirek Jan 11 '18 at 17:00
3

I'm using Windows 10, 64-bit, Powershell and had to use the following commands to properly set the local PostgreSQL environment variables:

C:\> $Env:PGUSER="[pg_username]"  
C:\> $Env:PGPASSWORD="[pg_password]"

To verify that these are set properly, list all local environment variables with this:

C:\> Get-ChildItem Env:

After doing this, I was able to run heroku pg:pull without being prompted for a password.

Paul Maurer
  • 360
  • 3
  • 6
1

The previous answers did not work for me or were not to my liking so I kept searching. Thanks to the answer provided by Rayz on this post How to add a user to PostgreSQL in Windows? I was able to come up with this one liner for windows powershell.

 & { $env:PGUSER="username";$env:PGPASSWORD="password"; heroku pg:push local-db DATABASE_URL --app heroku-app}

You apparently have to pass the variables as a list seperated by semicolons, surrounded by braces which are preceeded by an ampersand. Your funtion (heroku pg:pull/ pg:push/...) has to be a member of the list. As of my current testing it works in powershell with pg:push and the order of items within the braces does not matter.

Rad
  • 11
  • 2
  • Nice, I was actually using standard command prompt, but this should be helpful for powershell users. Thanks – hananamar Feb 22 '21 at 07:50
0

I was too lazy to find out how to change credentials used by heroku. SET PGUSER and SET PASSWORD did not work for me, what i did was this: Error said invalid credentials for "janbr" so i have created the user in the local db with a lot of priviledges. I used DBeaver for that with postgres credentials i have set up upon instalation of postgres. Not a very clean solution though.

JanBrus
  • 1,198
  • 9
  • 13
0

Using Windows 10, I tried all of the solutions here without any luck.

What ended up working for me was going into System Properties and editing my Environment Variables there. I added these to my System Variables, restarted my terminal and was able to run after that. One thing to note is that I first tried to add user and password to my User Variables but that didn't work.

PGUSER YourPostgresUser 
PGPASSWORD YourPassword

I also ran Get-ChildItem Env: after updating my variables to check that they had been added.

DarkMarcos
  • 11
  • 2