Difference of postgreSQL's "trust" and "ident"?

Question

In postgreSQL's /var/lib/pgsql/data/pg_hba.conf trust and ident can be set as method e.g. like so.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          trust
host    all         postgres    127.0.0.1/32          trust

I was under the impression that the difference of the two was with trust I as root could do

psql postgres

and with ident I had to

sudo -u postgres psql postgres

However I cannot get the first command to work even with trust.

Question

Can someone explain what the difference is of trust and ident?

Solution 1

"Trust" means "whatever username the client uses, you don't need to ask for a password to verify the user".

"Ident" means "accept whatever the identd returns as the username, without asking for a password to verify".

There's more information about the different authentication methods at the postgresql site.

In order to figure out why this particular connection failed, you need to check the postgresql logs. It may be that the username you're using doesn't exist in the postgres database, or it may be some other issue - it's impossible to tell without first looking at the logs.

Solution 2

psql has to connect to a database with a database's username. When it's not specified with the -U option, it takes the OS username as a default value.

So in fact, psql dbname is equivalent to psql -U $USER dbname

As a result, when you're logged as root and you haven't created a database user named root, this normally yields the following error, even if the authentication mode is trust.

# psql postgres
psql: FATAL: role "root" does not exist

On the other hand, it would work if it was invoked like this, still logged as root:

# psql -U postgres postgres

which is OK because the database named postgres and the user named postgres both exist, since they are created automatically during PostgreSQL installation.

Comments

[Craig Ringer]

ident is pretty poor, since it requires an identd to be running, and that identd could answer anything the client computer wants. It's really an awful legacy thing that should be avoided. peer for local connections remains very useful, though.