1

A couple of months ago I started with postgres for Heroku, this worked then. This week I had to use postgres again...

When trying to run a rails server I get: 

PG::ConnectionBad
FATAL: role "codemonkey" is not permitted to log in

config/initializers/quiet_assets.rb:7:in `call_with_quiet_assets'

I am using postgres 9.4.4 and I am trying to use the Postgres.app to get in. (I also have pgAdmin3.)

In the terminal:

When I run su postgres psql I get: /usr/local/bin/psql: /usr/local/bin/psql: cannot execute binary file after entering my password.

When I do su postgres' I first get a 'bash-3.2$ environment. This environment (obviously) gives: bash: ALTER: command not found when I type: ALTER ROLE codemonkey WITH LOGIN;

small additional edit
Thanks to a_horse_with_no_name I know that in this bash-3.2$ environment I can start psql (as the superuser). I am not sure if it's working though, I get could not save history to file "/Library/PostgreSQL/9.4/.psql_history": No such file or directory after my SQL command. And am still left with the same FATAL error.

At some point I got:

shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied

When I use the postgres.app elephant symbol and click "open psql" and enter my password I get: psql: FATAL: role "codemonkey" is not permitted to log in after entering my password.

This is what my ~/.bash_profile looks like: Should I do something to add postgres commands? It never recognizes anything. 

export PATH=/usr/local/bin:$PATH

# Setting PATH for Python 3.4
# The orginal version is saved in .bash_profile.pysave
PATH="/Library/Frameworks/Python.framework/Versions/3.4/bin:${PATH}"
export PATH
### Added by the Heroku Toolbelt
export PATH="/usr/local/heroku/bin:$PATH"
export PATH="/usr/local/bin:/usr/local/sbin:~/bin:$PATH"
if which rbenv > /dev/null; then eval "$(rbenv init -)"; fi
if which rbenv > /dev/null; then eval "$(rbenv init -)"; fi

This is what my pg_hba.conf looks like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                indent
# IPv4 local connections:
host    all             codemonkey     127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     codemonkey                                trust
#host    replication     codemonkey        127.0.0.1/32            trust
#host    replication     codemonkey        ::1/128                 trust

I think at some point I should be able to give the command ALTER ROLE codemonkey WITH LOGIN;

So I can get through. I wish I knew how.

COMPLETE RE-EDIT

Code-MonKy
  • 2,026
  • 2
  • 14
  • 27

2 Answers2

3

You should see your user's privileges by using this command:

SELECT * FROM pg_roles;

if the rolcanlogin corresponding to the user is set to false (f) then use this command: 

ALTER USER username WITH LOGIN;
Paul Roub
  • 36,322
  • 27
  • 84
  • 93
mohabkelany
  • 31
  • 2
1

You don't need to reinstall everything to change password for Postgres user. To change the password run ALTER ROLE youruser PASSWORD 'yourpassword';.

For Heroku(as it's remote connection) you need temporary change ipv4/ipv6 section (depending what you are using) to whitelist your client ip address (x.x.x.x). Don't forget to change in to md5 again after password reset. To apply pg_hba.conf changes restart postgres service.

#IPv4 local connections:
host    all        youruser             x.x.x.x/32            trust

SO has a lot of good answers as well.

Community
  • 1
  • 1
Dmitry S
  • 4,990
  • 2
  • 24
  • 32
  • I get `-bash: ALTER: command not found` when using your first part. – Code-MonKy Sep 02 '15 at 15:20
  • 1
    try to run `su postgres psql`. You can run SQL commands including `ALTER` only after you connected to the database – Dmitry S Sep 02 '15 at 15:41
  • If you have Postgres.app installed it should give you access to psql for the app. By default, for Postgres.App it creates username in postgres the same as your user on your mac os x. `'/Applications/Postgres.app/Contents/Versions/9.4/bin'/psql -p5432 -U yourmacuser` – Dmitry S Sep 02 '15 at 15:43
  • Alright. Now when I do `su postgres psql` I get: /usr/local/bin/psql: /usr/local/bin/psql: cannot execute binary file – Code-MonKy Sep 03 '15 at 09:59
  • @Code-MonKy: It's just `su postgres` and then (after the user has changed) you type the `psql` –  Sep 03 '15 at 10:41
  • I get `shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied bash-3.2$ ` I think I should alter something in the bash paths. – Code-MonKy Sep 03 '15 at 10:44
  • @Code-MonKy: **Again**: `ALTER` is a **SQL** command. You need to start `psql` after you run `su postgres`. At the `psql` prompt you can run SQL commands. –  Sep 04 '15 at 10:05
  • Thanks I didn't know I had to do that after entering `su postgres` in this strange (to me) bash 3.2 environment. I got this though after the SQL command and then `\q` in psql: `could not save history to file "/Library/PostgreSQL/9.4/.psql_history": No such file or directory` and still the no permittion FATAL error – Code-MonKy Sep 04 '15 at 10:26