search

YanChii / ansible-role-postgres-ha

Create postgresql HA auto-failover cluster using pcs, pacemaker and PAF
Apache License 2.0
33 stars 22 forks source link

Add rule in pg_hba.conf to forbid self-replication #5

Closed anayrat closed 7 years ago

anayrat commented 7 years ago

Hello,

Thanks for this role!

As explained in PAF's documentation you should add a rule in pg_hba.conf to forbid self-replication :

Moreover, if you rely on Pacemaker to move an IP resource on the node hosting the master role of PostgreSQL, make sure to add rules on the pg_hba.conf file of each instance to forbid self-replication.

Regards,

YanChii commented 7 years ago

You're right. Is it sufficient to forbid the localhost from connecting using the replication user? Thanks. Jan

anayrat commented 7 years ago

Following quick start you should put the vip and the hostname : 

host replication postgres 192.168.122.50/32 reject
host replication postgres $(hostname -s) reject
YanChii commented 7 years ago

Well, this means it cannot be generic and pg_hba.conf needs to be altered on every host on every sync. Do I understand it correctly? That's complicates things quite a lot.

anayrat commented 7 years ago

Humm I don't understand, you can put theses rules in all pg_hba.conf. You only have to change it when VIP address change.

YanChii commented 7 years ago

The pg_hba.conf file gets replicated to all slaves. So it needs to be altered afterwards to reflect new $(hostname -s).

YanChii commented 7 years ago

Therefore it applies also to resync of a failed master.

anayrat commented 7 years ago

You are right.

YanChii commented 7 years ago

How about this? After initial basebackup, the pg_hba.conf is altered to forbid self replication. And at the end of the role, a customized shell script is generated that can be used to do manual basebackup when needed. The script itself will take care of the fixing pg_hba.conf downloaded from the master. I don't like half-automated things ;) Jan