[Bucardo-general] Peer authentication error preventing herd creation

[Nelson, Alexander J.]

Hi David,

Thank you for the response.

I had hoped I noted the production inapplicability sufficiently in the scripts, but I suppose I could have favored the caps lock key more.  Understood on “Trust” being a non-recommendation.

MD5 doesn’t seem to be working.  I set the pg_hba line to end with ‘md5' instead of ‘peer’, and added another line for the host ‘localhost’, but I get the same FATAL message from Bucardo.  Running ‘su` to be the bucardo user and then running ‘psql’, flagless, runs psql without a hitch, and 'CREATE DATABASE foo;’ worked.

I also tried copy-pasting the password field from the ~bucardo/.pgpass, but it failed.  Shouldn’t this have worked?  If not, is there a disconnect between the password Bucardo’s generating and the password it’s storing?

    me at vm:~$ sudo cat ~bucardo/.pgpass
    localhost:5432:*:bucardo:scrubbed_string
    me at vm:~$ psql -U bucardo --password
    Password for user bucardo:         #pasted scrubbed_string
    psql: FATAL:  Peer authentication failed for user "bucardo"

This is the end of the pg_hba.conf for the freshly-restarted postgres server:

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer
    
    host bucardo bucardo localhost md5
    host bucardo bucardo 127.0.0.1/32 md5
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

The md5 rules don’t seem to be catching.  I tried restarting the Postgres service after deleting everything after the md5 rule, removing the ‘local all all peer’ catch-all, and now authentication attempts with the bucardo shell account end suggesting --password just fails.

    me at vm:~$ sudo su bucardo
    bucardo at vm:/home/ajn$ cd
    bucardo at vm:~$ psql
    psql: FATAL:  no pg_hba.conf entry for host "[local]", user "bucardo", database "bucardo", SSL off
    bucardo at vm:~$ psql --password
    Password: 
    psql: FATAL:  no pg_hba.conf entry for host "[local]", user "bucardo", database "bucardo", SSL off
    bucardo at vm:~$

Thank you again for helping with what’s boiling down to an authentication issue.

—Alex


On Jul 20, 2015, at 5:38 PM, David Christensen <david at endpoint.com> wrote:

> Hi Alex,
> 
> ‘Trust’ is obviously not a favorable authentication method to run in production; my recommendation would be ‘md5’/password authentication with the .pgpass information holding the actual passwords used.  ‘Peer’ authentication works only on local connections, not ones with hostname specified in the DSN; your example line here:
> 
>>   + sudo -u bucardo bucardo status
>>   DBI connect('dbname=bucardo;host=localhost;port=5432','bucardo',...) failed: FATAL:  password authentication failed for user "bucardo"
>>   FATAL:  password authentication failed for user "bucardo" at /usr/bin/bucardo line 267.
> 
> Note the “host=localhost” segment and the error messageshows that in fact it is trying to use the password authentication associated with the localhost connection, not peer connection.  (IME, peer tends to be a PITA generally, but I also generally am working with systems with dedicated postgres user accounts.)
> 
> HTH,
> 
> David
> --
> David Christensen
> PostgreSQL Team Manager
> End Point Corporation
> david at endpoint.com
> 785-727-1171
> 
> 
> 
> 
>