PostgreSQL: role not allowed to log in

Question

I am having trouble connecting to my own postgres db on the local server. I ran into some similar problems and came up with this guide https://help.ubuntu.com/stable/serverguide/postgresql.html

So:

pg_hba.conf says:

 # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 trust 

then I create a user and assign him a password:

 postgres=# create role asunotest; CREATE ROLE postgres=# alter role asunotest with encrypted password '1234'; ALTER ROLE 

but this does not allow me:

 -bash-4.2$ psql -h 127.0.0.1 -U asunotest Password for user asunotest: 1234 psql: FATAL: role "asunotest" is not permitted to log in 

what could be the problem?

Answer 1

The role you created cannot log in. You must give permission to the role to log in.

One way to do this is to log in as the postgres user and update the role:

 psql -U postgres 

After logging in, enter:

 ALTER ROLE "asunotest" WITH LOGIN; 

Here's the documentation http://www.postgresql.org/docs/9.0/static/sql-alterrole.html

Answer 2

 CREATE ROLE blog WITH LOGIN SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION; COMMENT ON ROLE blog IS 'Test';