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

On Fri, Sep 22, 2023 at 12:45 PM Luca Ferrari <fluca1978(at)gmail(dot)com> wrote:

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

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

[1]:
https://sqlite.org/forum/forumpost/c819a1e0fbca1f07dac18cacccb7676b15968a29c0dbc5f9c50a26406e243db6