pg_config is for compliation information, to help extensions and client programs compile and link against PostgreSQL. It knows nothing about the active PostgreSQL instance(s) on the machine, only the binaries.
pg_hba.conf can appear in many other places depending on how Pg was installed. The standard location is pg_hba.conf within the data_directory of the database (which could be in /home, /var/lib/pgsql, /var/lib/postgresql/[version]/, /opt/postgres/, etc etc etc) but users and packagers can put it wherever they like. Unfortunately.
The only valid ways find pg_hba.conf is to ask a running PostgreSQL instance where it’s pg_hba.conf is, or ask the sysadmin where it is. You can’t even rely on asking where the datadir is and parsing postgresql.conf because an init script might passed a param like -c hba_file=/some/other/path when starting Pg.
What you want to do is ask PostgreSQL:
SHOW hba_file;
This command must be run on a superuser session, so for shell scripting you might write something like:
psql -t -P format=unaligned -c 'show hba_file';
and set the environment variables PGUSER, PGDATABASE, etc to ensure that the connection is right.
Yes, this is somewhat of a chicken-and-egg problem, in that if the user can’t connect (say, after screwing up editing pg_hba.conf) you can’t find pg_hba.conf in order to fix it.
Another option is to look at the ps command’s output and see if the postmaster data directory argument -D is visible there, e.g.
ps aux | grep 'postgres *-D'
since pg_hba.conf will be inside the data directory (unless you’re on Debian/Ubuntu or some derivative and using their packages).
If you’re targeting specifically Ubuntu systems with PostgreSQL installed from Debian/Ubuntu packages it gets a little easier. You don’t have to deal with hand-compiled-from-source Pg that someone’s initdb’d a datadir for in their home dir, or an EnterpriseDB Pg install in /opt, etc. You can ask pg_wrapper, the Debian/Ubuntu multi-version Pg manager, where PostgreSQL is using the pg_lsclusters command from pg_wrapper.
If you can’t connect (Pg isn’t running, or you need to edit pg_hba.conf to connect) you’ll have to search the system for pg_hba.conf files. On Mac and Linux something like sudo find / -type f -name pg_hba.conf will do. Then check the PG_VERSION file in the same directory to make sure it’s the right PostgreSQL version if you have more than one. (If pg_hba.conf is in /etc/, ignore this, it’s the parent directory name instead). If you have more than one data directory for the same PostgreSQL version you’ll have to look at database size, check the command line of the running postgres from ps to see if it’s data directory -D argument matches where you’re editing, etc.
https://askubuntu.com/questions/256534/how-do-i-find-the-path-to-pg-hba-conf-from-the-shell/256711
Solution of psql: FATAL: Peer authentication failed for user “postgres” (or any user)
The connection failed because by default psql
connects over UNIX sockets using peer
authentication, that requires the current UNIX user to have the same user name as psql
. So you will have to create the UNIX user postgres
and then login as postgres
or use sudo -u postgres psql database-name
for accessing the database (and psql
should not ask for a password).
If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=database-name --username=postgres
(as pointed out by @meyerson answer) will solve your immediate problem.
But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf
* line:
from
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
to
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
-
peer
means it will trust the identity (authenticity) of UNIX user. So not asking for a password. -
md5
means it will always ask for a password, and validate it after hashing withMD5
. -
trust
means it will never ask for a password, and always trust any connection.
You can, of course, also create more specific rules for a specific database or user, with some users having peer
and others requiring passwords.
After changing
pg_hba.conf
you’ll need to restart PostgreSQL if it’s running. E.g.sudo service postgresql restart
Steps to change/create default postgres
user’s password:
trust
connection by adding inpg_hba.conf
file
local all postgres trust
- Restart postgresql service
sudo service postgresql restart
-
psql -U postgres
-
At the
postgres=#
prompt, change the user namepostgres
password:
ALTER USER postgres with password ‘new-password’;
- Revert the changes in
pg_hba.conf
file fromtrust
tomd5
and restartpostgresql
.
pg_hba.conf file location
The file pg_hba.conf
will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file;
command.
After change pg_hba.conf file, you can execute SELECT pg_reload_conf();
or pg_ctl reload with superuser instead of restart postgresql service.
* Source
Solution of psql: FATAL: Peer authentication failed for user “postgres” (or any user)
The connection failed because by default psql
connects over UNIX sockets using peer
authentication, that requires the current UNIX user to have the same user name as psql
. So you will have to create the UNIX user postgres
and then login as postgres
or use sudo -u postgres psql database-name
for accessing the database (and psql
should not ask for a password).
If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=database-name --username=postgres
(as pointed out by @meyerson answer) will solve your immediate problem.
But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf
* line:
from
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
to
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
-
peer
means it will trust the identity (authenticity) of UNIX user. So not asking for a password. -
md5
means it will always ask for a password, and validate it after hashing withMD5
. -
trust
means it will never ask for a password, and always trust any connection.
You can, of course, also create more specific rules for a specific database or user, with some users having peer
and others requiring passwords.
After changing
pg_hba.conf
you’ll need to restart PostgreSQL if it’s running. E.g.sudo service postgresql restart
Steps to change/create default postgres
user’s password:
trust
connection by adding inpg_hba.conf
file
local all postgres trust
- Restart postgresql service
sudo service postgresql restart
-
psql -U postgres
-
At the
postgres=#
prompt, change the user namepostgres
password:
ALTER USER postgres with password ‘new-password’;
- Revert the changes in
pg_hba.conf
file fromtrust
tomd5
and restartpostgresql
.
pg_hba.conf file location
The file pg_hba.conf
will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file;
command.
After change pg_hba.conf file, you can execute SELECT pg_reload_conf();
or pg_ctl reload with superuser instead of restart postgresql service.
* Source
Помогите разобраться народ !!!
Установлен PostgreSQL 9.6
Это моя настройка в pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD LOCAL ALL postgres md5 # "local" is for Unix domain socket connections only LOCAL ALL ALL md5 # IPv4 local connections: host ALL ALL 127.0.0.1/32 md5
Задан пароль пользователю «postgres» в Ubuntu
sudo echo -e «PAROLnPAROLn» | passwd postgres;
Пароль пользователю «postgres» в базе был задан:
sudo -U postgres psql -c «ALTER USER postgres WITH ENCRYPTED PASSWORD ‘PAROL’;»
Перезапускаю service postgresql restart
В результате, в логах:
2017-03-09 14:55:43 MSK [11185-1] СООБЩЕНИЕ: система БД была выключена: 2017-03-09 14:55:42 MSK 2017-03-09 14:55:43 MSK [11185-2] СООБЩЕНИЕ: Защита от наложения мультитранзакций сейчас включена 2017-03-09 14:55:43 MSK [11184-1] СООБЩЕНИЕ: система БД готова принимать подключения 2017-03-09 14:55:43 MSK [11189-1] СООБЩЕНИЕ: процесс запуска автоочистки создан 2017-03-09 14:55:44 MSK [11191-1] [н/д]@[н/д] СООБЩЕНИЕ: неполный стартовый пакет 2017-03-09 14:55:44 MSK [11194-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:44 MSK [11194-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:45 MSK [11197-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:45 MSK [11197-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:45 MSK [11200-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:45 MSK [11200-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:46 MSK [11203-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:46 MSK [11203-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:46 MSK [11206-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:46 MSK [11206-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:47 MSK [11209-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:47 MSK [11209-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:47 MSK [11212-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:47 MSK [11212-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:48 MSK [11215-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:48 MSK [11215-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:48 MSK [11218-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:48 MSK [11218-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:49 MSK [11221-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:49 MSK [11221-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:49 MSK [11224-1] postgres@postgres ВАЖНО: пользователь "postgres" не прошёл проверку подлинности (по паролю) 2017-03-09 14:55:49 MSK [11224-2] postgres@postgres ПОДРОБНОСТИ: Пароль не подходит для пользователя "postgres". Подключение соответствует строке 86 в pg_hba.conf: "local all postgres md5" 2017-03-09 14:55:49 MSK [11184-2] СООБЩЕНИЕ: получен запрос на "вежливое" выключение 2017-03-09 14:55:49 MSK [11189-2] СООБЩЕНИЕ: процесс запуска автоочистки завершается 2017-03-09 14:55:49 MSK [11186-1] СООБЩЕНИЕ: выключение 2017-03-09 14:55:49 MSK [11184-3] СООБЩЕНИЕ: система БД выключена
When I connect to my local PostgreSQL database server with command $ psql postgres 'postgres'
I encounter the error message psql: FATAL: Peer authentication failed for user “postgres”.
:~$ psql postgres 'postgres' psql: FATAL: Peer authentication failed for user "postgres"
After investigating I finally find the solution to fix this error. This error is because PostgreSQL has two authenticate methods as below.
1. PostgreSQL Authentication Methods.
- Peer Authentication (peer) Mode: This authentication method will use the base operating system’s user name and password as the PostgreSQL database server user account to login, this method is only effective for local PostgreSQL connections.
- Password Authentication (md5) Mode: This authentication method will need the login user to provide a username and password, and this method is effective for both local and remote PostgreSQL database server connections. The password can be saved both in clear-text or md5-encrypted. But we recommend using md5-encrypted. This can make the user account more safety.
- Trust (trust) Mode: This means the PostgreSQL DB server trust any connection, no password for any connection.
2. How To Change Postgresql Authentication Method.
PostgreSQL authentication method is saved in a configuration file, the file name is pg_hba.conf. So you can change the authentication method follow the below steps.
- Locate the pg_hba.conf configuration file uses Linux locate command.
:~$ locate pg_hba.conf /etc/postgresql/10/main/pg_hba.conf /usr/share/postgresql/10/pg_hba.conf.sample
- From the above result, we can see that the pg_hba.conf file is generally saved in /etc/postgresql/10/main/ directory.
- Now edit the file using your favorite text editor, I use gedit in ubuntu.
$ sudo gedit /etc/postgresql/10/main/pg_hba.conf [sudo] password for zhaosong: (gedit:317): IBUS-WARNING **: 11:34:40.540: The owner of /home/zhaosong/.config/ibus/bus is not root!
- Comment database administrative login text line which ends with peer and adds a new line use md5 to replace peer. Do not forget to save the changes.
#local all postgres peer local all postgres md5
- Reload the PostgreSQL service with the below command.
~$ /etc/init.d/postgresql reload [ ok ] Reloading postgresql configuration (via systemctl): postgresql.service.
- Or restart the PostgreSQL server.
$ sudo service postgresql restart
- Now login PostgreSQL server again, you may find another error message psql: error: FATAL: password authentication failed for user “postgres” like below.
$ psql postgres postgres Password for user postgres: psql: error: FATAL: password authentication failed for user "postgres"
- Change the line local all postgres md5 to local all postgres trust<.span> in the file /etc/postgresql/10/main/pg_hba.conf.
#local all postgres peer #local all postgres md5 local all postgres trust
- Now login PostgreSQL server again with the command psql user=postgres or psql postgres postgres, the error will be fixed.
~$ psql user=postgres psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1)) Type "help" for help. postgres=#
- Below is the explanation of the settings text line like host all all 127.0.0.1/32 trust in the pg_hba.conf file. When you meet a login authentication error, you can change the METHOD column value to trust, this method will not require any password and trust any connection. But for the production environment, you had better use the method peer or md5 for safety issues.
# 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 trust
3. How To Fix Psql: Error: Fatal: Password Authentication Failed For User “postgres”.
3.1 Question.
- When I connect to the PostgreSQL in my python source code, I get the below error.
FATAL: Peer authentication failed for user "postgres"
- I have edited the pg_hba.conf file and change the authentication method to md5, but it does not take effect.
- Then I try to create a new user with a new database, but when I run the command sudo -u postgres psql -l to list the databases, it returns the below error. How to fix it.
psql: error: FATAL: password authentication failed for user "postgres"
3.2 Answer1.
- If this is your test environment, you can change the authentication method to trust like below, then it will not require you to provide the password to connect to the PostgreSQL DB server.
local all postgres trust
- On Ubuntu, you can run the command sudo gedit /etc/postgresql/12/main/pg_hba.conf to open the PostgreSQL configuration file to edit it and save it.
- Run the command sudo systemctl restart postgresql.service to reload the above settings to make it take effect.
- Now when you run the command sudo -u postgres psql -l, you only need to provide the OS logged in user’s password, it will not ask you to provides the user postgres‘s password, and then it will run the command psql -l to show the PostgreSQL database tables list.
$ sudo -u postgres psql -l [sudo] password for jerry: List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows)
I just installed PostgreSQL 9.4 on Ubuntu 15.10.
- I created a user with
createuser -P myuser
- I created a database with
createdb -O myuser mydatabase
- I edited
pg_hba.conf
and addedlocal mydatabase myuser md5
- I restarted PostgreSQL with
sudo service postgresql restart
User myuser is a PostgresSQL user only and has no user account on Ubuntu.
When I try to connect to the database with psql -W mydatabase myuser
it fails with psql: FATAL: Peer authentication failed for user "myuser"
.
PostgreSQL is running …
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Thu 2016-03-03 09:53:00 CET; 9min ago
Process: 22219 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 22219 (code=exited, status=0/SUCCESS)
Mar 03 09:53:00 SERVER01 systemd[1]: Starting PostgreSQL RDBMS...
Mar 03 09:53:00 SERVER01 systemd[1]: Started PostgreSQL RDBMS.
… and listening.
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 localhost:postgresql *:* LISTEN
tcp6 0 0 localhost:postgresql [::]:* LISTEN
Active UNIX domain sockets (only servers)
Proto RefCnt Flags Type State I-Node Path
unix 2 [ ACC ] STREAM LISTENING 151534 /var/run/postgresql/.s.PGSQL.5432
What do I have to do to connect with user myuser to database mydatabase?
Evan Carroll
60.6k44 gold badges224 silver badges457 bronze badges
asked Mar 3, 2016 at 9:06
In a fresh install from a few days ago, the second line of my pg_hba.conf
is
local all all peer
I believe this is the one that makes your connection attempt fail.
The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered. If it fails, then there is no second try, so the connection attempt will likely fail. Or, as the documentation states:
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.
The solution is easy: either remove the above line if you don’t plan to use peer
authentication, or move your specific rule above this one.
Evan Carroll
60.6k44 gold badges224 silver badges457 bronze badges
answered Mar 3, 2016 at 9:16
dezsodezso
30.4k13 gold badges98 silver badges143 bronze badges
2
First of all, check if you have the lines giving permission to the myuser user in pg_hba.conf. For example:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
Or any other lines of permission to IPV4 (and IPv6 if you use) with: TYPE DATABASE USER ADDRESS METHOD
After this check, run the psql as follows:
psql -h localhost -U myuser mydatabase
And then, the requested prompt, enter the user’s password myuser.
answered Mar 4, 2016 at 14:52
2
Peer authentication failed error arrives when you try to login to your PostgreSQL user but authentication fails because by default psql
connects over UNIX sockets using peer
authentication instead of password authentication.
In this article we will learn how to get rid of FATAL: Peer authentication failed for user «postgres» error by enforcing password authentication over Unix sockets peer method,
First, navigate to the /etc/postgresql/10/main
directory.
cd /etc/postgresql/10/main
Note that 10
is the PostgreSQL version it can be different for you.
Here resides the pg_hba.conf
file we need to do some changes here you may need sudo
access for this.
sudo nano pg_hba.conf
Scroll down the file till you find this —
# Database administrative login by Unix domain socket
local all postgres peer
Here change the peer to md5 as follows.
# Database administrative login by Unix domain socket
local all postgres md5
peer
means it will trust the authenticity of UNIX user hence does not prompt for the password.md5
means it will always ask for a password, and validate it after hashing withMD5
.
Now save the file and restart the Postgres server.
sudo service postgresql restart
And that’s it!
Try to log in now.
sudo -u postgres psql
Password:
postgres=#
WEB DEVELOPMENT