38

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?

grigoryvp
  • 37,371
  • 60
  • 164
  • 268

4 Answers4

49

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

Nam G VU
  • 30,868
  • 67
  • 216
  • 353
cecilkorik
  • 1,371
  • 11
  • 17
  • What is the correct way to add new user with admin privileges? – grigoryvp Dec 01 '10 at 20:38
  • Adding user with "CREATE USER puser WITH PASSWORD 'password'" and doing "psql -U puser" evaluates to error "psql: FATAL: database "puser" does not exist" – grigoryvp Dec 01 '10 at 20:41
  • 1
    It is usually easier to use the `createuser` commandline utility instead of creating the user with SQL. It has options to make the user an admin. Also, if it is telling you the database does not exist, then the login was successful. By default, postgres tries to login to the user's "home" database, which would have the same name as the user. Either create a "puser" database, or try `psql -U puser myapp` to login to "myapp". – cecilkorik Dec 01 '10 at 20:44
  • Also to make your new "puser" into an admin, you should be able to do: `ALTER USER puser WITH CREATEUSER CREATEDB` – cecilkorik Dec 01 '10 at 20:50
  • 3
    As of postgresql 8.4, "ident sameuser" needs to be changed to "ident". – ryandesign Dec 03 '12 at 23:16
2

I think your pg_ident.conf file is misconfigured. Also, have you tried

psql -U postgres -W
Max
  • 3,131
  • 1
  • 30
  • 30
1

Another thing that can cause this is expired credentials. I don't think this happened in version 8, but in version 9 when you create a new role in pgadmin, it is created in an expired state and you need to change or clear the role's expiration date before you will be able to login with it.

elc
  • 1,942
  • 20
  • 24
0

You may find it helpful to create the database's user and schema in PostgreSQL:

  1. Log into PostgreSQL from the postgres user
$ sudo -u postgres psql postgres
  1. Once in, create the user and database
CREATE ROLE myuser LOGIN PASSWORD 'mypass';
CREATE DATABASE mydatabase WITH OWNER = myuser;
  1. Log into PostgreSQL from the new user account
$ psql -h localhost -d mydatabase -U myuser -p <port>
Adonis Gaitatzis
  • 1,941
  • 20
  • 19