pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Peer authentication failed ???
4+ messages / 3 participants
[nested] [flat]

* Peer authentication failed ???
@ 2023-10-03 09:55 Amn Ojee Uw <amnojeeuw@gmail.com>
  2023-10-03 12:35 ` Re: Peer authentication failed ??? David G. Johnston <david.g.johnston@gmail.com>
  2023-10-03 13:37 ` Re: Peer authentication failed ??? Peter J. Holzer <hjp-pgsql@hjp.at>
  0 siblings, 2 replies; 4+ messages in thread

From: Amn Ojee Uw @ 2023-10-03 09:55 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi there.

Using the 'sudo -u postgres psql' command I created a group and two 
accounts in the group, I also created a database for one of the 
accounts. Now, I'd like to login PG-15, using my new account and 
connecting to my new database, but PG-15 complains. See the example 
below, I think it is clear than my English.

*CREATE ROLE my_group; // Create a group**
**CREATE ROLE my_group_admin LOGIN PASSWORD 'AdminPassword' CREATEDB 
CREATEROLE; // Create admin**
**CREATE ROLE my_group_worker LOGIN PASSWORD 'WorkerPassword' CREATEDB; 
// Create Worker**
**GRANT my_group TO my_group_admin WITH INHERIT TRUE;**
**GRANT my_group TO my_group_worker WITH INHERIT TRUE;**
**
**CREATE DATABASE my_group_db WITH OWNER my_group_worker;**
**\q**
**psql -U my_group_worker -d my_group_db*

/psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_group_worker/*"*

*** What am I doing wrong?


Thanks in advance.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Peer authentication failed ???
  2023-10-03 09:55 Peer authentication failed ??? Amn Ojee Uw <amnojeeuw@gmail.com>
@ 2023-10-03 12:35 ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: David G. Johnston @ 2023-10-03 12:35 UTC (permalink / raw)
  To: Amn Ojee Uw <amnojeeuw@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Tuesday, October 3, 2023, Amn Ojee Uw <amnojeeuw@gmail.com> wrote:
>
> Using the 'sudo -u postgres psql' command
>
> *psql -U my_group_worker -d my_group_db*
>
> *psql: error: connection to server on socket
> "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication
> failed for user "my_group_worker**"*
>
> *** What am I doing wrong?
>

postgres != my_group_worker and you haven’t setup an ident mapping to
associate the two

David J.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Peer authentication failed ???
  2023-10-03 09:55 Peer authentication failed ??? Amn Ojee Uw <amnojeeuw@gmail.com>
@ 2023-10-03 13:37 ` Peter J. Holzer <hjp-pgsql@hjp.at>
  2023-10-04 09:28   ` Re: Peer authentication failed ??? Amn Ojee Uw <amnojeeuw@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Peter J. Holzer @ 2023-10-03 13:37 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 2023-10-03 05:55:51 -0400, Amn Ojee Uw wrote:
> psql -U my_group_worker -d my_group_db
> 
> psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
> failed: FATAL:  Peer authentication failed for user "my_group_worker"
> 
> *** What am I doing wrong?

PostgreSQL uses the first matching rule from pg_hba.conf. Presumably
(because that's the default on Debian/Ubuntu) you have it set up to use
peer authentication on the unix socket and password authentication
(scram or md5) on everything else.

You are connecting via the Unix socket (/var/run/postgresql/.s.PGSQL.5432),
so the server will attempt peer authentication and nothing else. To get
it to attempt password authentication connect via a tcp socket:

psql -U my_group_worker -h localhost -d my_group_db

(More conveniently set up ident authentication for your users, then you
don't need a password.)

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Peer authentication failed ???
  2023-10-03 09:55 Peer authentication failed ??? Amn Ojee Uw <amnojeeuw@gmail.com>
  2023-10-03 13:37 ` Re: Peer authentication failed ??? Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2023-10-04 09:28   ` Amn Ojee Uw <amnojeeuw@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Amn Ojee Uw @ 2023-10-04 09:28 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Yes!
Thanks so much it worked!

On 10/3/23 9:37 a.m., Peter J. Holzer wrote:
> On 2023-10-03 05:55:51 -0400, Amn Ojee Uw wrote:
>> psql -U my_group_worker -d my_group_db
>>
>> psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
>> failed: FATAL:  Peer authentication failed for user "my_group_worker"
>>
>> *** What am I doing wrong?
> PostgreSQL uses the first matching rule from pg_hba.conf. Presumably
> (because that's the default on Debian/Ubuntu) you have it set up to use
> peer authentication on the unix socket and password authentication
> (scram or md5) on everything else.
>
> You are connecting via the Unix socket (/var/run/postgresql/.s.PGSQL.5432),
> so the server will attempt peer authentication and nothing else. To get
> it to attempt password authentication connect via a tcp socket:
>
> psql -U my_group_worker -h localhost -d my_group_db
>
> (More conveniently set up ident authentication for your users, then you
> don't need a password.)
>
>          hp
>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-10-04 09:28 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-03 09:55 Peer authentication failed ??? Amn Ojee Uw <amnojeeuw@gmail.com>
2023-10-03 12:35 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-03 13:37 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-10-04 09:28   ` Amn Ojee Uw <amnojeeuw@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox