PostgreSQL: role is not permitted to log in
PostgresqlPermissionsRolesPostgresql 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 :
- 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
Solution 4 - Postgresql
try to run
sudo su - postgres
psql
ALTER ROLE 'dbname'