[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

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
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


Reply to: