On Fri, Sep 15, 2017 at 01:03:36PM +0000, Tom Browder wrote:
I get the following error when trying to create a table with psql:
psql: FATAL: Peer authentication failed for user "sql92"
The spawned command 'psql -f ./t/t.sql -U sql92' exited unsuccessfully (exit
code: 2)
The sql file has two create table commands.
I had already created the user 'sql92' with password = '' and createdb
privileges.
Don't try to set a blank password that way in postgresql. You've got a
couple of options for authentication:
1) allow identity based authentication. by default, unix user "x" connecting
via a unix socket is authenticated as postgres user "x" with no password
required. it is possible to map a unix user to a different postgres
user.
2) allow password authentication. for this to work there needs to be an
actual password (not '') and by default you need to connect to "localhost"
rather than the unix socket (which uses ident authentication). it is
possible to create a .pgpass file to store the password (though there
are obviously security concerns to consider when storing a password on
disk)
3) allow "trust" authentication. you can configure postgresql to allow
anyone to authenticate as any user without a password.
4) use a different authentication mechanism like TLS certificates.
Which mechanism to use depends on what you're trying to accomplish. In
general, option 1 is usually the best in the context of a local postgres
server becauase it doesn't introduce the need to manage additional
passwords. If it is too complicated to map existing db users to local
users, option 2 is available and more similar to the behavior of other
db systems.
Check out https://www.postgresql.org/docs/9.3/static/auth-methods.html
for more information. I've found that the postgresql docs are generally
very good.
Mike Stone
