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

On Fri, Sep 22, 2023 at 12:13 PM Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
> So my question is how I pass the password my tool already own, to the forked PSQL,
> w/o that password leaking. I could pass it on the command-line, but that would be leaking
> it to the `ps` command (and in various other places).
>

I think that forking a beast like psql will make you incurring into a
lot of security problems that are worst your "password leak".
One solution I could see, could be something like "a poor man captive
psql": write a per-user .pgpass file with the password for the
database (from your tool), write a .psqlrc configuration for the user
and let him to connect to the database. Once done, remove both files
and/or restore previous ones.

Besides, as far as I understand, you are approaching the problem with
a possible wrong design: schemas in PostgreSQL do not provide the
amount of isolation it seems you are trying to achieve with your
persona-to-role-to-schema wrapper.

> That's why I'm asking the community how best to the forked PSQL can connect w/o password prompting.

psql and lipq can exploit .pgpass for exactly that aim: not messing
around with passwords.
Again, I would discourage you to fork psql. Would you be able to
maintain the new upcoming versions in the future?

>> 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.
>
>
> pg_dumpbinary is a Perl script that uses pg_dump and pg_restore.

Yes, and in fact it does the pre-data and post-data sections, e.g.,
<https://github.com/lzlabs/pg_dumpbinary/blob/master/pg_dumpbinary#L305>.
However, I'm not sure you will be ble to mix and match a pg_dump
manually obtined schema (in plain text) with pg_restorebinary.
And it is not clear to me, still, the aim of this approach.

Luca