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

On Thu, Sep 21, 2023 at 7:46 PM Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
>
> 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).
>

Isn't .psqlrc a possibile approach?

% cat .psqlrc
\echo "Loading configuration"
set search_path to 'luca';

> So how would I:
> 1) "pass secrets" somehow, so they don't leak to the PS command for example. stdin?

I don't get what you mean by secrets, but I guess you have to play
with a wrapper and pass or tools like that.

> 2) configure the forked PSQL in terms of SET ROLE, SET SEARCH_PATH, and possible other \commands?

I would use .psqlrc for every user.

>
> 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.

Again, I don't understand the aim, seems you would obfuscate what you
are doing to others for, ehm, commercial reasons?
However, pg_dump can dump the only schema (--schema-only and friends),
as well as data only. I'm not sure pg_dumpbinary can (is it a
purpose?).
I would not mix and macth the two tools however.

Luca