On Fri, Sep 22, 2023 at 10:56 AM Luca Ferrari <fluca1978@xxxxxxxxx> wrote:
On Thu, Sep 21, 2023 at 7:46 PM Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
> 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';
I see. I don't want to mess with user's .pqsqlrc, but from
I can use PSQLRC to override the default location.
Or I can use `-f filename` or `-f -` to provide them independently of the .psqlrc,
with or without `--no-psqlrc`.
So that takes care of configuring PSQL, thanks.
> 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.
Well, simply the password for example. The user connects to PostgreSQL using our tool,
possibly prompting for the password, if PQconnectionNeedsPassword() returns true.
We translate project names and "persona" into actual ROLE and SCHEMA names, check
permissions, check schema versions, that sort of things. Then "fork" PSQL, which must
reconnect to the same DB for the same USER, but ideally w/o having PSQL prompting
for the password again. It should be seamless. Especially since we have alternate ways
to get the password from, beside prompting and PGSERVICE and PGPASSWORD.
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 guess I could create a temp file with the connection info, and set a PGSERVICE env-var
for PSQL, but then the password in in-clear in a file. Again, that's leaking the password, in a way.
Another alternative would be to read what PSQL outputs, and look for it asking for a password,
and provide it, but that seems brittle, requires `pipe` (not cross-platform).
That's why I'm asking the community how best to the forked PSQL can connect w/o password prompting.
> 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?
Not really no. It's actually to make the backup more introspectable in fact.
But that's beside the point, no?
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.
