Re: Postresql: need a way to initiate a database with a test user
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
The sql file has two create table commands.
I had already created the user 'sql92' with password = '' and createdb
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
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
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
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