Unable to change Backup user from postgres to new repuser

[Richard Keit]

Hi Team,

I do not want to use the postgres user within the Barman connection file, when changing to a new replication user I get the below response:

Starting backup for server main in /app/barman/main/base/20150724T144116

ERROR: Backup failed issuing start backup command.

DETAILS: Cannot connect to postgres: FATAL:  no pg_hba.conf entry for host "<IPADDRESS>", user "replicator", database "replication", SSL off

Barman config:

;; ; 'main' PostgreSQL Server configuration

[main]

;; ; Human readable description

description =  "Main PostgreSQL Database"

;;

;; ; SSH options

ssh_command = ssh postgres@pghost

;;

;; ; PostgreSQL connection string

conninfo = host=pghost user=replicator dbname=replication

Replication User created as such:

CREATE ROLE replicator LOGIN

  ENCRYPTED PASSWORD 'md5e7f3f83999c6c6da0675a4e8abc4929a'

  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;

 PG_HBA.conf

local   replication     replicator                                trust

host    replication     replicator        127.0.0.1/32            md5

host    replication     replicator        ::1/128                 md5

host    replication     replicator      *                       md5

PG_Log file:

< 2015-07-24 14:47:58.805 AEST >LOG:  connection received: host=barmanhost port=48001

< 2015-07-24 14:47:58.806 AEST >FATAL:  no pg_hba.conf entry for host "BARMANIP", user "replicator", database "replication", SSL off

Please let me know why the above configuration is not working.

Many thanks,
Richard 

[Chris Withers]

On 24/07/2015 05:49, Richard Keit wrote:

 PG_HBA.conf

local   replication     replicator                                trust

host    replication     replicator        127.0.0.1/32            md5

host    replication     replicator        ::1/128                 md5

host    replication     replicator      *                       md5

Try changing that * to 0.0.0.0/0.

cheers,

Chris

[Richard Keit]

Hi Chris,

No luck changing it to 0.0.0.0/0, gives the below:

< 2015-07-25 22:52:57.508 AEST >LOG:  specifying both host name and CIDR mask is invalid: "0:0:0:0/0"

Also changed it to 0.0.0.0 and still gives the similar "no entry in pg_hba.conf" file error.

I am curious around the config needed to use a different user because I haven't seen any 2ndQuadrant documents on it and seems to be unheard of.

Regards,
Richard

[David Hancock]

Try 0.0.0.0 (periods, not commas--it's an IPV4 address).

[Richard Keit]

Thanks for your response David,

Can confirm that with the below I get the same response:

host    replication     replicator      0.0.0.0/32                      trust

Is it possible to turn logging to such a state that it would report the outcome of each entry of the pg_hba.conf file against the incoming connection?

[Chris Withers]

On 25/07/2015 15:15, Richard Keit wrote:
> Thanks for your response David,
>
> Can confirm that with the below I get the same response:
> host replication replicator 0.0.0.0/32 trust
>
> Is it possible to turn logging to such a state that it would report
> the outcome of each entry of the pg_hba.conf file against the incoming
> connection?

Look in your postgres server's logs, ph_hba problems are normally both
logged there and reported to the client.

cheers,

Chris

[Richard Keit]

Hi Chris,

< 2015-07-24 13:37:25.520 AEST >LOG:  connection received: host=<hostname> port=45640

< 2015-07-24 13:37:25.521 AEST >FATAL:  no pg_hba.conf entry for host "<hostIP>", user "replicator", database                                                      "replicator", SSL off

< 2015-07-24 13:37:25.521 AEST >DETAIL:  Client IP address resolved to "<hostname>", forward lookup                                                      not checked.

I turned connection logging on but the detail is no that impressive unfortunately.

Regards,

Richard

--

Richard Keit
(M) +61 411 744 921

(E) raj...@gmail.com

[Chris Withers]

Sounds silly, but have you remembered to restart (not just reload!) postgres after making changes?

If so, try changing to /32 to a /0...

Also, have you tried a database name other than "replication"? That might be a magic keyword in pg_hba, specifically for specifying security for replication connections...

Chris

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

[Gabriele Bartolini]

Hi Richard,

  Barman does not (yet) support PostgreSQL's streaming replication protocol. You must connect to Postgres as a user with 'superuser' privileges. If you don't want to connect as 'postgres' user, you can create a 'barman' user and use that, with a command similar to this:

 createuser -s -E -P barman

  Then assign the required privileges in pg_hba.conf as any standard PostgreSQL client (I recommend using a password file for the barman user).

Regards,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support
 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Richard Keit]

Hi Gabriele , Chris,

As stated, if Barman doesn't support replication then I can only assuming specifying "replication" as the database in pg_hba.conf will not work, and by the same account specifying "replication" as the dbname in Barman will not satisfy what Barman may require.

Currently, pg_hba.conf (most insecure, to get working):

host    all     barman  all     trust

Currently, in Barman conf:

 conninfo = host=<host>l user=barman dbname=replication

Error given on Barman:

 ERROR: Backup failed issuing start backup command.

DETAILS: Cannot connect to postgres: FATAL:  database "replication" does not exist

Error given on Postgres:

< 2015-07-27 19:20:43.056 AEST >FATAL:  database "replication" does not exist

< 2015-07-27 19:20:43.060 AEST >FATAL:  database "replication" does not exist

< 2015-07-27 19:20:43.067 AEST >FATAL:  database "replication" does not exist

< 2015-07-27 19:20:43.071 AEST >FATAL:  database "replication" does not exist

I have tried identical configuration for "postgres" user and still no luck.

Gabriele, are you able to give a break down on how Barman initiates the backup? Eg, does it ssh to postgres(assuming it does), invoke pg_basebackup, etc ,etc

 

Thank you all for your inputs, much appreciated so far

Regards,

Richard