28000 Invalid Authorization Specification
Connecting to PostgreSQL may trigger an error as shown below:
FATAL: no pg_hba.conf entry for host "172.118.10.5", user "postgres", database "postgres", SSL off
This error generally triggers due to incomplete entries in the pg_hba.conf file. Generally, this configuration file controls the client authentication and is stored in the database instance’s data directory.
The general format of the pg_hba.conf file contains a set of records, one per line. A record contains a number of fields separated by spaces and/or tabs. Fields can contain white space, but we need to quote the field value.
Each record specifies a connection type, a client IP address range, a database name, a user name, and the authentication method for connections matching these parameters.
Further, the first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.
Solution:
Modify the pg_hba.conf file in data directory to define what authentication policy should be used from all networks to the PostgreSQL server, and add an entry of the host IP address from which we try to connect. We can input the entry of the host to which we would like to provide access to:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 172.118.10.5/32 md5
Then, restart the database service to reload configurations:
# systemctl restart postgresql.service
Try again in order to connect with your database client tool and we should be able to connect without any errors.
Connecting to PostgreSQL may trigger an error as shown below:
FATAL: role "andym" does not exist
The indicated database user was not found. Note that if you do not specify a user name, the name of the operating system user running the database client tools is used as the user name by default, which might or might not be the right thing.
Connecting to PostgreSQL may trigger an error as shown below:
FATAL: role "andym" is not permitted to log in
The indicated database role 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 a super user and update the role:
ALTER ROLE "andym" WITH LOGIN;