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

Groups > linux.debian.user > #186847 > unrolled thread

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

Back to article view | Back to linux.debian.user

Contents

  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

#186847 — Postresql: need a way to initiate a database with a test user

[Multipart message — attachments visible in raw view] — view raw

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.

Is there any way to create a user that can be used outside an open database
connection in a script?

Thanks.

Best regards,

-Tom

[toc] | [next] | [standalone]

#186851

[Multipart message — attachments visible in raw view] — view raw

On Fri, Sep 15, 2017 at 08:03 Tom Browder <tom.browder@gmail.com> wrote:

> I get the following error when trying to create a table with psql:
>

Re: OP subject: s/Postresql/PostgreSQL/

-Tom

[toc] | [prev] | [next] | [standalone]

#187006

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.
> 
> Is there any way to create a user that can be used outside an open database
> connection in a script?

If you want something "just for testing", you can spin up a pg
instance on demand - custom access port and other params (so you
don't clash with any existing instance/install) - this is running it
like e.g. MSAccess "as an application", although "true db admins"
will of course highlight that this is very inefficient and pg is
designed to readily run any number of databases you might need ... in
the single running instance.

Haven't used it for years, so other than that ... Good luck :)

[toc] | [prev] | [next] | [standalone]

#187022

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

[toc] | [prev] | [standalone]

Back to top | Article view | linux.debian.user

csiph-web