PostgreSQL: role is not permitted to log in

Question

I have trouble connecting to my own postgres db on a local server. I googled some similar problems and came up with this manual 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 a password for it:

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

but it doesn't let me in:

-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 have created is not allowed to log in. You have to give the role permission to log in.

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

psql -U postgres

Once you are logged in, type:

ALTER ROLE "asunotest" WITH LOGIN;

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