CODEWITHSUNDEEP
postgresqlruby-on-rails-4
Code-MonKy
Code-MonKy

Reputation: 2086

Postgres Fatal role name is not permitted to log in

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

Upvotes: 2

Views: 10690

Answers (2)

mohabkelany
mohabkelany

Reputation: 61

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;

Upvotes: 6

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

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.

Upvotes: 1

Related Questions