[Bucardo-general] peer authentication

Sat Jul 18 23:40:06 UTC 2020

Hi Folks,

so I get the impression that bucardo is really meant to run in a 
password only authentication scheme, but I haven't read any where that 
peer/ident won't work, and they are listed on the old wiki pages as 
options.

I get that I need passwords for remote connections, but I would much 
prefer if local connections could use peer authentication instead of 
md5, mostly because I intend to try scripting things for future usage 
and would avoid more passwords than necessary involved in said script.

Hopefully I can show what I mean.  in the below examples, mooglian is 
the local machine, and moogle is the remote one.

Every thing seems to work up to the part where I add the sync.
These commands work:

su -c "bucardo add db computerisms_ca_cal_mooglian 
dbname=computerisms_ca_cal" bucardo

su -c "bucardo add db computerisms_ca_cal_moogle 
dbname=computerisms_ca_cal host=2607:feb8::3:48 user=postgres" bucardo

su -c "bucardo add dbgroup computerisms_ca_cal_bucardogroup 
computerisms_ca_cal_mooglian:source computerisms_ca_cal_moogle:source" 
bucardo

and each of these commands will produce postgres logs like this 
(connection logging enabled):

[12912] [unknown]@[unknown] LOG:  connection received: host=[local]
[12912] bucardo at bucardo LOG:  connection authorized: user=bucardo 
database=bucardo
[12913] [unknown]@[unknown] LOG:  connection received: host=[local]
[12913] bucardo at computerisms_ca_cal LOG:  provided user name (bucardo) 
and authenticated user name (postgres) do not match
[12913] bucardo at computerisms_ca_cal FATAL:  Peer authentication failed 
for user "bucardo"
[12913] bucardo at computerisms_ca_cal DETAIL:  Connection matched 
pg_hba.conf line 33: "local   all             all        peer"
[12914] [unknown]@[unknown] LOG:  connection received: host=[local]
[12914] bucardo at computerisms_ca_cal LOG:  connection authorized: 
user=bucardo database=computerisms_ca_cal

so seems something in there tries to connect as the postgres user, but 
then somehow falls back again to connecting as bucardo user and 
succeeds.  Bucardo list shows they worked:

su -c "bucardo list dbgroup" bucardo
dbgroup: computerisms_ca_cal_bucardogroup  Members: 
computerisms_ca_cal_moogle:source computerisms_ca_cal_mooglian:source

If I disable line 33 in pg_hba.conf, the command does indeed fail trying 
to add another db:

su -c "bucardo add db easysmart_ca_cal_mooglian dbname=easysmart_ca_cal" 
bucardo
DBI 
connect('dbname=bucardo;host=/var/run/postgresql;port=5432','bucardo',...) 
failed: FATAL:  no pg_hba.conf entry for host "[local]", user "bucardo", 
database "bucardo", SSL off at /usr/bin/bucardo line 310.

so up until now, peer authentication is working/not working as expected 
(by me, at least).  Now, when I try to add the sync:

su -c "bucardo add sync computerisms_ca_cal_sync tables=all 
dbs=computerisms_ca_cal_bucardogroup" bucardo
DBD::Pg::st execute failed: ERROR:  DBI 
connect('dbname=computerisms_ca_cal','bucardo',...) failed: FATAL:  Peer 
authentication failed for user "bucardo" at line 64.
CONTEXT:  PL/Perl function "validate_goat" at /usr/bin/bucardo line 5269.

and the postgres logs show the same thing, where it is trying to connect 
as postgres, but this time it doesn't fall back:

[18035] [unknown]@[unknown] LOG:  connection received: host=[local]
[18035] bucardo at bucardo LOG:  connection authorized: user=bucardo 
database=bucardo
[18036] [unknown]@[unknown] LOG:  connection received: host=[local]
[18036] bucardo at computerisms_ca_cal LOG:  connection authorized: 
user=bucardo database=computerisms_ca_cal
[18037] [unknown]@[unknown] LOG:  connection received: host=[local]
[18037] bucardo at computerisms_ca_cal LOG:  provided user name (bucardo) 
and authenticated user name (postgres) do not match
[18037] bucardo at computerisms_ca_cal FATAL:  Peer authentication failed 
for user "bucardo"
[18037] bucardo at computerisms_ca_cal DETAIL:  Connection matched 
pg_hba.conf line 33: "local   all             all     peer"
[18035] bucardo at bucardo ERROR:  DBI 
connect('dbname=computerisms_ca_cal','bucardo',...) failed: FATAL:  Peer 
authentication failed for user "bucardo" at line 64.
bucardo at bucardo CONTEXT:  PL/Perl function "validate_goat"
[18035] bucardo at bucardo STATEMENT:  INSERT INTO bucardo.goat 
(schemaname,tablename,reltype,db) VALUES ($1,$2,$3,$4) RETURNING id

so it still seems the postgres user is coded into the connection some 
how.  But even using the -U argument and runnning under different su 
users produces results I am finding confusing.  Try to force 
authenticated username to be bucardo:

su -c "bucardo add sync computerisms_ca_cal_sync tables=all 
dbs=computerisms_ca_cal_bucardogroup -U bucardo" bucardo
[20354] bucardo at computerisms_ca_cal LOG:  provided user name (bucardo) 
and authenticated user name (postgres) do not match

Still connecting as postgres. okay, try to connect as postgres:

su -c "bucardo add sync computerisms_ca_cal_sync tables=all 
dbs=computerisms_ca_cal_bucardogroup -U postgres" bucardo
[19739] postgres at bucardo LOG:  provided user name (postgres) and 
authenticated user name (bucardo) do not match

Now bucardo is authenticated username?  why??  okay, flip them:

su -c "bucardo add sync computerisms_ca_cal_sync tables=all 
dbs=computerisms_ca_cal_bucardogroup -U bucardo" postgres
[23428] bucardo at bucardo LOG:  provided user name (bucardo) and 
authenticated user name (postgres) do not match

exact same as when running su bucardo :/.  so run the whole thing as 
postgres:

su -c "bucardo add sync computerisms_ca_cal_sync tables=all 
dbs=computerisms_ca_cal_bucardogroup -U postgres" postgres
[20750] bucardo at computerisms_ca_cal LOG:  provided user name (bucardo) 
and authenticated user name (postgres) do not match
[20750] bucardo at computerisms_ca_cal FATAL:  Peer authentication failed 
for user "bucardo"
[20750] bucardo at computerisms_ca_cal DETAIL:  Connection matched 
pg_hba.conf line 33: "local   all             all        peer"
[20751] [unknown]@[unknown] LOG:  connection received: host=[local]
[20751] postgres at computerisms_ca_cal LOG:  connection authorized: 
user=postgres database=computerisms_ca_cal
[20751] postgres at computerisms_ca_cal ERROR:  role "bucardo" already exists
[20751] postgres at computerisms_ca_cal STATEMENT:  CREATE USER bucardo 
SUPERUSER

Now it still provides username bucardo, then falls back to user postgres 
and tries to recreate the username bucardo, but then it seemingly won't 
be able to connect with bucardo username any way?

I am sure there is some logic happening here, but I am failing to 
understand what it is.  I am sure the program is doing what it is 
supposed to be doing and the problem is me, but could someone be so kind 
as to set my poor little brain cells straight?

For the sake of completeness, I am running version 5.5 from debian 
repos.  I considered installing 5.6, but I see nothing in the Changes 
file that indicates this has been addressed.

-- 
Bob Miller
Cell: 867-334-7117
Office: 867-633-3760
Office: 867-322-0362
www.computerisms.ca