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?

score 481 · Accepted Answer · edited Aug 14 '19 at 01:16

481

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

edited Aug 14 '19 at 01:16

Marvo

17,845
8
50
74

answered Feb 07 '16 at 15:10

chalitha geekiyanage

6,464
5
25
32

3

As I can't `psql`, how can I ALTER ROLE? – Romulus Urakagi Ts'ai Sep 22 '17 at 03:53
3

@RomulusUrakagiTs'ai you should enter as a postgres user ( sudo -u postgres psql postgres ) – Mi Ka Nov 16 '17 at 16:11
1

For me on PeppermintOS the first command is "sudo -u postgres psql". – Nicholas Obert Nov 29 '21 at 19:59

score 25 · Answer 2 · edited Mar 30 '19 at 08:44

25

CREATE ROLE blog WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  REPLICATION;

COMMENT ON ROLE blog IS 'Test';

edited Mar 30 '19 at 08:44

Wai Ha Lee

8,598
83
57
92

answered Mar 30 '19 at 06:48

Yuer_1008

261
3
2

score 3 · Answer 3 · answered Jun 14 '20 at 16:55

3

Using pgadmin4 :

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

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

answered Jun 14 '20 at 16:55

Dilshad

73
1
6

score -8 · Answer 4 · edited Oct 05 '19 at 09:08

-8

try to run

sudo su - postgres
psql
ALTER ROLE 'dbname'

edited Oct 05 '19 at 09:08

shahaf

4,750
2
29
32

answered Oct 05 '19 at 06:32

مشعل العنزي

25

PostgreSQL: role is not permitted to log in

4 Answers4

Linked

Related