How to update local files on all the postgres nodes

Question

I have a multi-node Postgres cluster running in High availability mode following the primary-standby architecture.

postgres-0                                               5/5     Running            0          111s
postgres-1                                               5/5     Running            0          3m4s
postgres-monitor-0                                       4/4     Running            0          13m

One of the pod is primary and the other pod is in standby mode which gets replicated from primary in a synchronous manner, while standby remains in the read-only mode.

I am running the following CRUD commands to update a local file:

-- Update pg_config to allow access to the users
create table hba ( lines text );
copy hba from '/pgsql/data/pg_hba.conf';

insert into hba (lines) values ('host     all              all       0.0.0.0/0    md5');
insert into hba (lines) values ('host     all              all       ::/0         md5');

copy hba to '/pgsql/data/pg_hba.conf';

-- reload the config
select pg_reload_conf();

The issue is, the file /pgsql/data/pg_hba.conf is getting updated on the primary node and not on the standby node (as all queries go to master), this means when the primary node goes down and standby becomes a new primary the config changes will be missing.

Answer 1

The copy command can be executed on all the Postgres nodes:

psql -U postgres -h postgres-0.postgres-agent.default -c "copy hba to '/pgsql/data/pg_hba.conf'"
psql -U postgres -h postgres-1.postgres-agent.default -c "copy hba to '/pgsql/data/pg_hba.conf'"

The copy command can be executed on the standby node even though it's in ready-only mode.

---

Below are the complete sequence of steps after verifying all the nodes have joined the cluster and replication have started:

Step 1: (Execute on Primary node, this will get replicated to all the standby nodes)

-- Update pg_config to allow access to the users
create table hba ( lines text );
copy hba from '/pgsql/data/pg_hba.conf';

insert into hba (lines)
    select 'host     <database>              all       0.0.0.0/0    scram-sha-256'
        where not exists (
            select 1 from hba where lines = 'host     <database>              all       0.0.0.0/0    scram-sha-256'
            );

insert into hba (lines)
    select 'host     <database>              all       ::/0         scram-sha-256'
        where not exists (
            select 1 from hba where lines = 'host     <database>              all       ::/0         scram-sha-256'
            );

Step 2: (Depends on the number of replicas)

psql -U postgres -h postgres-0.postgres-agent.default -c "copy hba to '/pgsql/data/pg_hba.conf'"
psql -U postgres -h postgres-1.postgres-agent.default -c "copy hba to '/pgsql/data/pg_hba.conf'"

Step 3:

-- reload the config
select pg_reload_conf();

Answer 2

I must add that this approach does not look very secure to me.

You cannot use tables (even temporary ones) on a standby server. I think the easiest way to approach this is to write a PL/PerlU or PL/PythonU to do the necessary file modifications. You also have to call the function pg_reload_comf() to activate the modifications.