How to fork pg_dump or psql w/o leaking secrets?

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to fork pg_dump or psql w/o leaking secrets?
Date: 2023-09-21 16:42:38
Message-ID: CAFCRh-_wXVEVx9y3OUama5Qjp7_-LuUq-pkhyo7=o1iJwrq1zQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. To administer our PostgreSQL-based system, we have custom tools
to manage the schemas, load data, etc... Including a versatile CLI tool.

But that tool is special purpose, while sometimes we want/need the general
purpose PSQL. But because our ROLE and SCHEMA names are rather long
and unwieldly, it's cumbersome to SET ROLE and SET SEARCH_PATH manually
to setup the session for easy access to the data. Thus I'd like to fork
PSQL from
our tool, which requires reconnecting to the server (thus possibly
"secrets"),
and I also need to run some PSQL \commands and SQL to config PSQL correctly
for the context our tool was run with (i.e. our tool's own CLI options).

So how would I:
1) "pass secrets" somehow, so they don't leak to the PS command for
example. stdin?
2) configure the forked PSQL in terms of SET ROLE, SET SEARCH_PATH, and
possible other \commands?

In a very similar vein, I do my own backups using COPY, similar to
[pg_dumpbinary][1],
for the same reasons, but using a different "container" (not several
files), for several reasons,
but I'd like to reuse `pg_dump --pre/post-data` by also forking it, with
the save issue #1 above,
to take care of the DDLs for me, while I handle the DMLs myself.

Does anybody have experience doing something similar?
Any public example anywhere?
Otherwise, any advice on how best to do it?

Thanks, --DD

[1] https://github.com/lzlabs/pg_dumpbinary

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Elango Thiagarajan 2023-09-22 03:36:57 Migration of Oracle Vault to Postgres
Previous Message Tom Lane 2023-09-21 15:29:27 Re: whether I can add a new encoding?