Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > linux.debian.user > #187022

Re: Postresql: need a way to initiate a database with a test user

Show all headers | View raw

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

Back to linux.debian.user | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Postresql: need a way to initiate a database with a test user Tom Browder <tom.browder@gmail.com> - 2017-09-15 15:10 +0200
  Re: Postresql: need a way to initiate a database with a test user Tom Browder <tom.browder@gmail.com> - 2017-09-15 16:10 +0200
  Re: Postresql: need a way to initiate a database with a test user Zenaan Harkness <zenaan@freedbms.net> - 2017-09-19 14:50 +0200
  Re: Postresql: need a way to initiate a database with a test user Michael Stone <mstone@debian.org> - 2017-09-19 20:10 +0200

csiph-web