when i create a new user, but it cannot login the database.
I do that like this:
postgres@Aspire:/home/XXX$ createuser dev
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
then create a database:
postgres@Aspire:/home/XXX$ createdb -O dev test_development
after that, I try psql -U dev -W test_development
to login, but get the error:
psql: FATAL: Peer authentication failed for user "dev"
I tried to solve the problem but failed.
This question is related to
postgresql
Your 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 dev
and then login as dev
or use sudo -u dev psql test_development
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=test_development --username=dev
(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 with MD5
.
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
if PostgreSQL is running you'll need to make it re-read the configuration by reloading (pg_ctl reload
) or restarting (sudo service postgresql restart
).
* The file pg_hba.conf
will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
Edited: Remarks from @Chloe, @JavierEH, @Jonas Eicher, @fccoelho, @Joanis, @Uphill_What comments incorporated into answer.
Try:
psql -U role_name -d database -h hostname..com -W
When you specify:
psql -U user
it connects via UNIX Socket, which by default uses peer
authentication, unless specified in pg_hba.conf
otherwise.
You can specify:
host database user 127.0.0.1/32 md5
host database user ::1/128 md5
to get TCP/IP connection on loopback interface (both IPv4 and IPv6) for specified database
and user
.
After changes you have to restart postgres or reload it's configuration. Restart that should work in modern RHEL/Debian based distros:
service postgresql restart
Reload should work in following way:
pg_ctl reload
but the command may differ depending of PATH configuration - you may have to specify absolute path, which may be different, depending on way the postgres was installed.
Then you can use:
psql -h localhost -U user -d database
to login with that user
to specified database
over TCP/IP.
md5
stands for encrypted password, while you can also specify password
for plain text passwords during authorisation. These 2 options shouldn't be of a great matter as long as database server is only locally accessible, with no network access.
Important note:
Definition order in pg_hba.conf
matters - rules are read from top to bottom, like iptables, so you probably want to add proposed rules above the rule:
host all all 127.0.0.1/32 ident
In my case I was using different port. Default is 5432. I was using 5433. This worked for me:
$ psql -f update_table.sql -d db_name -U db_user_name -h 127.0.0.1 -p 5433
I simply had to add -h localhost
The easiest solution:
CREATE USER dev WITH PASSWORD 'dev';
CREATE DATABASE test_development;
GRANT ALL PRIVILEGES ON DATABASE test_development to dev;
ALTER ROLE dev CREATEROLE CREATEDB;
Peer authentication means that postgres asks the operating system for your login name and uses this for authentication. To login as user "dev" using peer authentication on postgres, you must also be the user "dev" on the operating system.
You can find details to the authentication methods in the Postgresql documentation.
Hint: If no authentication method works anymore, disconnect the server from the network and use method "trust" for "localhost" (and double check that your server is not reachable through the network while method "trust" is enabled).
This works for me when I run into it:
sudo -u username psql
While @flaviodesousa's answer would work, it also makes it mandatory for all users (everyone else) to enter a password.
Sometime it makes sense to keep peer authentication for everyone else, but make an exception for a service user. In that case you would want to add a line to the pg_hba.conf that looks like:
local all some_batch_user md5
I would recommend that you add this line right below the commented header line:
# TYPE DATABASE USER ADDRESS METHOD
local all some_batch_user md5
You will need to restart PostgreSQL using
sudo service postgresql restart
If you're using 9.3, your pg_hba.conf would most likely be:
/etc/postgresql/9.3/main/pg_hba.conf
For people in the future seeing this, postgres
is in the /usr/lib/postgresql/10/bin
on my Ubuntu server.
I added it to the PATH in my .bashrc file, and add this line at the end
PATH=$PATH:/usr/lib/postgresql/10/bin
then on the command line
$> source ./.bashrc
I refreshed my bash environment. Now I can use postgres -D /wherever
from any directory
pg_dump -h localhost -U postgres -F c -b -v -f mydb.backup mydb
Source: Stackoverflow.com