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.

Laurenz Albe · Answer 1 · 2022-02-04T19:19:59.297

0

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.

edited Feb 04 '22 at 19:19

answered Feb 04 '22 at 05:12

Laurenz Albe

209,280
17
206
263

Thanks, Laurenz for the response. Are you suggesting to create a script and then run it in the standby server? – Vishrant Feb 04 '22 at 05:23
I should also mention, the standby nodes are in ready only. Also updated the question. – Vishrant Feb 04 '22 at 05:23
I agree the pg_hba.conf is making it un-secure, but the infrastructure is secured using istio mtls. – Vishrant Feb 04 '22 at 05:24
1

You could use a `DO` statement or a PL/pgSQL function. – Laurenz Albe Feb 04 '22 at 05:25
I created this https://gist.github.com/vishrantgupta/57a2c50f1081d6793f7d87cdb326596e psql but it is not running on all the postgres nodes. – Vishrant Feb 04 '22 at 05:59
Of course not. I told you to use an array rather than a table. – Laurenz Albe Feb 04 '22 at 07:32
Thanks, Laurenz for reviewing it. I am not sure if I followed you, could you please point me to an example of how I can change the `COPY` command to use `ARRAY`? – Vishrant Feb 04 '22 at 16:53
You cannot use `COPY` for that. – Laurenz Albe Feb 04 '22 at 18:46
Thanks for updating the answer! `PL/PerlU` or `PL/PythonU` will add a dependency on package `postgresql-plpython3`, and I will have to add another docker layer to install the dependencies on top of the existing postgres image, which I am avoiding. – Vishrant Feb 07 '22 at 16:16

Vishrant · Accepted Answer · 2022-03-20T02:49:17.987

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();

How to update local files on all the postgres nodes

2 Answers2