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?

Selig

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

Benavides

As I can't psql, how can I ALTER ROLE? – Benedicto

@RomulusUrakagiTs'ai you should enter as a postgres user ( sudo -u postgres psql postgres ) – Synthetic

For me on PeppermintOS the first command is "sudo -u postgres psql". – Prevaricate

Answer 2

CREATE ROLE blog WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  REPLICATION;

COMMENT ON ROLE blog IS 'Test';

Slot

Answer 3

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

Dday

Answer 4

For example, you can alter the existing user(role) john to have LOGIN attribute with ALTER ROLE or ALTER USER statement as shown below:

ALTER ROLE john WITH LOGIN;

Or:

ALTER USER john WITH LOGIN;

*Memos:

• You must log in with any superusers(e.g., postgres).

• You can omit WITH which is optional.

• ALTER GROUP statement cannot alter an existing user(role) to have LOGIN attribute.

And, you can create the user(role) john with the password apple using CREATE ROLE, CREATE GROUP or CREATE USER statement as shown below:

CREATE ROLE john WITH LOGIN PASSWORD 'apple';

Or:

CREATE GROUP john WITH LOGIN PASSWORD 'apple';

Or:

CREATE USER john WITH LOGIN PASSWORD 'apple';

*Memos:

• You must log in with any superusers(e.g., postgres).

• You can omit WITH which is optional.

• You must use '' for PASSWORD instead of "" otherwise there is error.

• By default, CREATE USER without LOGIN can still have LOGIN attribute implicitly.

• By default, PostgreSQL needs a password so you should set PASSWORD.

In addition, if you want to drop the superuser john, first, run these SQL below. *My answer explains how to drop a user(role) properly:

REASSIGN OWNED BY john TO postgres;
DROP OWNED BY john;

Finally, you can drop the superuser john with DROP ROLE, DROP GROUP or DROP USER statement as shown below:

DROP ROLE john;

Or:

DROP GROUP john;

Or:

DROP USER john;

Minus

Answer 5

try to run

sudo su - postgres
psql
ALTER ROLE 'dbname'

Grenadines