I have a fresh ubuntu 10.10 install with all updates and postgresql 8.4
In order for postgresql to accept login+password connections i have configured it via:
sudo su postgres psql ALTER USER postgres WITH PASSWORD 'password'; CREATE DATABASE myapp; \q exit sudo vi /etc/postgresql/8.4/main/pg_hba.conf change "local all all indent" to "local all all trust"
But, surprisingly, this is not working! The command
psql -U postgres password
Evaluates with error:
psql: FATAL: Ident authentication failed for user "postgres"
Any hints how i can make the psql -U
to work?
Restart the PostgreSQL service from the Services control panel ( start->run->services. msc ) Connect using psql or pgAdmin4 or whatever you prefer. Run ALTER USER postgres PASSWORD 'fooBarEatsBarFoodBareFoot'
PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_authid system catalog. Passwords can be managed with the SQL commands CREATE ROLE and ALTER ROLE, e.g., CREATE ROLE foo WITH LOGIN PASSWORD 'secret' , or the psql command \password .
It is probably a good idea to leave the "postgres" user with ident authentication. By default I believe Ubuntu uses the "postgres" user to perform upgrades, backups, etc, and that requires that it is able to login without a specified password.
I recommend creating another user (probably with your own username) and giving it admin privileges as well. Then you can use that user with passwords on local connections.
Here is what the relevant parts of my pg_hba.conf look like:
# allow postgres user to use "ident" authentication on Unix sockets # (as per recent comments, omit "sameuser" if on postgres 8.4 or later) local all postgres ident sameuser # allow all other users to use "md5" authentication on Unix sockets local all all md5 # for users connected via local IPv4 or IPv6 connections, always require md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5
Also note that psql -U postgres password
will not do what you want. The password should never be specified on the commandline. That will try to login as user "postgres" to a database named "password".
You should use psql -U postgres myapp
instead. Postgres will automatically prompt you for a password, if it is configured properly to require one.
In case we want the password be filled-in automatically, place it in $HOME/.pgpass file
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With