On Fri, Sep 22, 2023 at 12:45 PM Luca Ferrari <fluca1978@xxxxxxxxx> wrote:
On Fri, Sep 22, 2023 at 12:13 PM Dominique Devienne <ddevienne@xxxxxxxxx> 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".
I'm sorry, but this doesn't make sense to me.
I'm talking of replacing using my tool then psql, with using my tool that forks psql.
In both cases these are clients apps that connect to a particular DB, for a given user.
The point if about the best way to not expose the password, if a password is necessary.
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.
I already told you my app is LIBPQ aware, including about PGPASSWORD and PGSERVICE.
It's users who decide to use these mechanisms (which have plain-text passwords BTW...), not my tool.
The same way PSQL prompts for a password when not using these mechanism, so does my tool.
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.
I don't know where you are inferring that...
And it seems beside the point anyway, if it was true (and it isn't).
> 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.
You are confusing things. I use PGSERVICE and PGPASSWORD myself.
But other users don't. The tool must work either way. And besides, I already
mentioned we have other custom (encrypted store) ways to get passwords too.
So in that case, the password must be communicated to PSQL anyways, somehow.
And the somehow is exactly the point of my questions here.
Again, I would discourage you to fork psql. Would you be able to
maintain the new upcoming versions in the future?
PSQL is not willy-nilly changing its CLI options or the way it operates.
Plus I bundled PSQL in my package, which is standalone and cross-platforms.
And again, I really don't see what's the harm is forking PSQL versus running it directly,
albeit having to provide credentials again, exactly what I want to avoid.
>> 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.
Who says I'd use pg_restorebinary? My tool does custom backup / restore because:
1) it's a mix of a schema (a "project"), and pieces (subset) of another schema (meta-data about the project)
2) it's full of bytea values, some quite large.
3) the backup is not opaque, it's an SQLite DB with a 1-to-1 match in terms of tables and rows with the original schema.
(but not in terms of columns, the COPY BINARY bytes of the row as dumped as-is; I also use the same integer PK if any)
(and in some cases, I even [expose virtual columns][1] in SQLite for "interesting" (i.e. NKs) columns of the COPY BINARY bytes)
The DML parts using COPY BINARY are already working just fine, thank you.
And it yields a 1-file backup per "project" schema (+ meta-data) that you can easily introspect in any SQLite tool.
Most of the rows are opaque, but tables, rows, integer PKs, and even some text NKs and FKs (parent-child)
are visible from SQLite, making it possible to partially restore some subset, or get row stats about the "project", etc...
I even have views which know about the schema structure, to project even more insights about the backup content.
It's so convenient, I wonder why it doesn't exist already. I'm not the only SQLite + PostgreSQL dev, after all, right.
OTOH, I do tend to think out-of-the-box...
Now I want to add capturing the DDLs for the "project" schema at backup time,
so I don't have to pre-create the "project" schema using my own tool, before restoring it.
I want to capture the schema exactly as it was, recreate the schema using the captured DDLs,
and the use my tool as usual to check and possibly upgrade that schema, as necessary.
And instead of re-inventing the wheel (in my tool) to capture DDLs, reusing pg_dump
makes complete sense to me.
My users don't know SQL or PostgreSQL. They ask to backup a project "by name" to my tool,
they won't run pg_dump manually. They wouldn't even know what the schema name is. Or even
how to properly quote it (requires triple dquotes in CMD on Windows, how many people know that!),
much less what pg_dump options to use.
Anyways, I'm getting carried away I'm afraid...
That's not the kind of answers (or questions / comments) I was expecting, from this ML.
Hopefully all is not lost, and someone might answer the as-secure-as-possible password passing question.
Thanks, --DD
