PostgreSQL: role is not permitted to log in

PostgresqlPermissionsRoles

Postgresql Problem Overview


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?

Postgresql Solutions


Solution 1 - Postgresql

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

Solution 2 - Postgresql

CREATE ROLE blog WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  REPLICATION;

COMMENT ON ROLE blog IS 'Test';

Solution 3 - Postgresql

Using pgadmin4 :

  1. Select roles in side menu
  2. Select properties in dashboard.
  3. Click Edit and select privileges

Now there you can enable or disable login, roles and other options

Solution 4 - Postgresql

try to run

sudo su - postgres
psql
ALTER ROLE 'dbname'

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionkurtgnView Question on Stackoverflow
Solution 1 - Postgresqlchalitha geekiyanageView Answer on Stackoverflow
Solution 2 - PostgresqlYuer_1008View Answer on Stackoverflow
Solution 3 - PostgresqlDilshadView Answer on Stackoverflow
Solution 4 - Postgresqlمشعل العنزيView Answer on Stackoverflow