Connecting with PostgreSQL 9.1 using the Ubuntu machine user and peer authentication method

Discussion:

Jorge Arevalo

2014-08-06 22:43:56 UTC

Hello,

I want to connect to my local installation of PostgreSQL 9.1 using my
machine user (who is vagrant). So, after reading PostgreSQL documentation,
I thought I just needed to:

1. Add username map in pg_ident.conf:

# MAPNAME SYSTEM-USERNAME PG-USERNAME

vp vagrant postgres

2. Using the map in pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

local all all
peer map=vp

But I'm getting the error

sql: FATAL: Peer authentication failed for user "vagrant"

If I try to connect to my server using psql.

I guess I'm misunderstanding the PostgreSQL manual. But, how could I get
what I need? (locally connect with the user vagrant like if it was the
postgres user)

Many thanks in advance (and sorry for the cross-posting. I asked this in
serverfault too, but I think this is the right place)

--
Jorge Arevalo

http://about.me/jorgeas80

Frank Pinto

2014-08-06 22:56:25 UTC

Permalink

Looks like you're doing it right, you actually have to specify the user
though:

psql -U postgres

and make sure you restarted the server so your changes take effect.

Frank

Post by Jorge Arevalo
Hello,
I want to connect to my local installation of PostgreSQL 9.1 using my
machine user (who is vagrant). So, after reading PostgreSQL documentation,
# MAPNAME SYSTEM-USERNAME PG-USERNAME
vp vagrant postgres
2. Using the map in pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all all
peer map=vp
But I'm getting the error
sql: FATAL: Peer authentication failed for user "vagrant"
If I try to connect to my server using psql.
I guess I'm misunderstanding the PostgreSQL manual. But, how could I get
what I need? (locally connect with the user vagrant like if it was the
postgres user)
Many thanks in advance (and sorry for the cross-posting. I asked this in
serverfault too, but I think this is the right place)
--
Jorge Arevalo
http://about.me/jorgeas80

Ray Stell

2014-08-07 13:31:42 UTC

Permalink

Post by Frank Pinto
and make sure you restarted the server so your changes take effect.

reload will get it, also, and is sometimes easier to come by.

The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file.
http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html

John R Pierce

2014-08-06 23:08:05 UTC

Permalink

Post by Jorge Arevalo
I want to connect to my local installation of PostgreSQL 9.1 using my
machine user (who is vagrant). So, after reading PostgreSQL

wouldn't it be easier to ...

create user vagrant superuser;
create database vagrant owner vagrant;

?
--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jorge Arevalo

2014-08-07 08:42:42 UTC

Permalink

Post by John R Pierce

Post by Jorge Arevalo
I want to connect to my local installation of PostgreSQL 9.1 using my
machine user (who is vagrant). So, after reading PostgreSQL documentation,

wouldn't it be easier to ...
create user vagrant superuser;
create database vagrant owner vagrant;
?
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
http://www.postgresql.org/mailpref/pgsql-general

That's an option, yes. I just wanted to avoid the creation of a new
superuser, if I can identify my vagrant machine user with db postgres user

--
Jorge Arevalo
Freelance developer

http://about.me/jorgeas80

Adrian Klaver

2014-08-06 23:14:59 UTC

Permalink

Post by Jorge Arevalo
Hello,
I want to connect to my local installation of PostgreSQL 9.1 using my
machine user (who is vagrant). So, after reading PostgreSQL
# MAPNAME SYSTEM-USERNAME PG-USERNAME
vp vagrant postgres
2. Using the map in pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all all
peer map=vp
But I'm getting the error
sql: FATAL: Peer authentication failed for user "vagrant"
If I try to connect to my server using psql.
I guess I'm misunderstanding the PostgreSQL manual. But, how could I get
what I need? (locally connect with the user vagrant like if it was the
postgres user)

What OS are you on?

Per:
http://www.postgresql.org/docs/9.1/interactive/auth-methods.html#AUTH-PEER

"Peer authentication is only available on operating systems providing
the getpeereid() function, the SO_PEERCRED socket parameter, or similar
mechanisms. Currently that includes Linux, most flavors of BSD including
Mac OS X, and Solaris."

Post by Jorge Arevalo
--
Jorge Arevalo
http://about.me/jorgeas80

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jorge Arevalo

2014-08-07 08:39:47 UTC

Permalink

Post by Adrian Klaver

What OS are you on?
http://www.postgresql.org/docs/9.1/interactive/auth-methods.html#AUTH-PEER
"Peer authentication is only available on operating systems providing the
getpeereid() function, the SO_PEERCRED socket parameter, or similar
mechanisms. Currently that includes Linux, most flavors of BSD including
Mac OS X, and Solaris."

Linux system (Ubuntu 12.04). Also tested in Mac OS 10.8.

Forgot to mention: in pg_hba.conf there is a previous line:

local postgres peer

No map specified for that line.

Post by Adrian Klaver

Post by Jorge Arevalo
--
Jorge Arevalo
http://about.me/jorgeas80

--
Adrian Klaver

--
Jorge Arevalo
Freelance developer

http://about.me/jorgeas80

Adrian Klaver

2014-08-07 13:43:39 UTC

Permalink

Post by Adrian Klaver
What OS are you on?
http://www.postgresql.org/__docs/9.1/interactive/auth-__methods.html#AUTH-PEER
<http://www.postgresql.org/docs/9.1/interactive/auth-methods.html#AUTH-PEER>
"Peer authentication is only available on operating systems
providing the getpeereid() function, the SO_PEERCRED socket
parameter, or similar mechanisms. Currently that includes Linux,
most flavors of BSD including Mac OS X, and Solaris."
Linux system (Ubuntu 12.04). Also tested in Mac OS 10.8.
local postgres peer

That would be the issue, assuming you are doing something along lines of
psql -d some_db -U postgres per:

http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html

" The first record with a matching connection type, client address,
requested database, and user name is used to perform authentication.
There is no "fall-through" or "backup": if one record is chosen and the
authentication fails, subsequent records are not considered."

If you are not connecting as above, you will need to show us your
connection string.

Post by Adrian Klaver
No map specified for that line.
--
Jorge Arevalo

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jorge Arevalo

2014-08-08 17:49:46 UTC

Permalink

Post by Adrian Klaver

Post by Adrian Klaver
What OS are you on?
http://www.postgresql.org/__docs/9.1/interactive/auth-__
methods.html#AUTH-PEER
<http://www.postgresql.org/docs/9.1/interactive/auth-
methods.html#AUTH-PEER>
"Peer authentication is only available on operating systems
providing the getpeereid() function, the SO_PEERCRED socket
parameter, or similar mechanisms. Currently that includes Linux,
most flavors of BSD including Mac OS X, and Solaris."
Linux system (Ubuntu 12.04). Also tested in Mac OS 10.8.
local postgres peer

That would be the issue, assuming you are doing something along lines of
http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html
" The first record with a matching connection type, client address,
requested database, and user name is used to perform authentication. There
is no "fall-through" or "backup": if one record is chosen and the
authentication fails, subsequent records are not considered."
If you are not connecting as above, you will need to show us your
connection string.

Actually, it's connecting now. I've applied the map to the first entry in
pg_hba.conf

local all postgres peer map=vp.

So, I'm identifying vagrant with postgres, as desired, and it works.

Many thanks for your responses!

Best regards,
--
Jorge Arevalo

http://about.me/jorgeas80

Post by Adrian Klaver

Post by Adrian Klaver
No map specified for that line.
--
Jorge Arevalo

--
Adrian Klaver