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
-
peermeans it will trust the identity (authenticity) of UNIX user. So not asking for a password. -
md5means it will always ask for a password, and validate it after hashing withMD5. -
trustmeans 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.confyou’ll need to restart PostgreSQL if it’s running. E.g.sudo service postgresql restart
Steps to change/create default postgres user’s password:
trustconnection by adding inpg_hba.conffile
local all postgres trust
- Restart postgresql service
sudo service postgresql restart
-
psql -U postgres -
At the
postgres=#prompt, change the user namepostgrespassword:
ALTER USER postgres with password ‘new-password’;
- Revert the changes in
pg_hba.conffile fromtrusttomd5and 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
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.confand 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
59.1k43 gold badges217 silver badges445 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
59.1k43 gold badges217 silver badges445 bronze badges
answered Mar 3, 2016 at 9:16
dezsodezso
29.9k13 gold badges95 silver badges140 bronze badges
2
First… check that you have the lines permissioning 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