Re: psql: add \si, \sm, \st and \sr functions to show CREATE commands for indexes, matviews, triggers and tables

On 18.08.2020 17:25, Tom Lane wrote:
> a(dot)pervushina(at)postgrespro(dot)ru writes:
>> [ si_st_sm_sr_v2.patch ]
> I hadn't particularly noticed this thread before, but I happened to
> look through this patch, and I've got to say that this proposed feature
> seems like an absolute disaster from a maintenance standpoint. There
> will be no value in an \st command that is only 90% accurate; the produced
> DDL has to be 100% correct. This means that, if we accept this feature,
> psql will have to know everything pg_dump knows about how to construct the
> DDL describing tables, indexes, views, etc. That is a lot of code, and
> it's messy, and it changes nontrivially on a very regular basis. I can't
> accept that we want another copy in psql --- especially one that looks
> nothing like what pg_dump has.
>
> There've been repeated discussions about somehow extracting pg_dump's
> knowledge into a library that would also be available to other client
> programs (see e.g. the concurrent thread at [1]). That's quite a tall
> order, which is why it's not happened yet. But I think we really need
> to have something like that before we can accept this feature for psql.
>
> BTW, as an example of why this is far more difficult than it might
> seem at first glance, this patch doesn't even begin to meet the
> expectation stated at the top of describe.c:
>
> * Support for the various \d ("describe") commands. Note that the current
> * expectation is that all functions in this file will succeed when working
> * with servers of versions 7.4 and up. It's okay to omit irrelevant
> * information for an old server, but not to fail outright.
>
> It might be okay for this to cut off at 8.0 or so, as I think pg_dump
> does, but not to just fail on older servers.
>
> Another angle, which I'm not even sure how we want to think about it, is
> security. It will not do for "\et" to allow some attacker to replace
> function calls appearing in the table's CHECK constraints, for instance.
> So this means you've got to be very aware of CVE-2018-1058-style attacks.
> Our answer to that for pg_dump has partially depended on restricting the
> search_path used at both dump and restore time ... but I don't think \et
> gets to override the search path that the psql user is using. I'm not
> sure what that means in practice but it certainly requires some thought
> before we add the feature, not after.
>
> Anyway, I can see the attraction of having psql commands like these,
> but "write a bunch of new code that we'll have to maintain" does not
> seem like a desirable way to get them.
>
> regards, tom lane
>
> [1] https://www.postgresql.org/message-id/flat/9df8a3d3-13d2-116d-26ab-6a273c1ed38c%402ndquadrant.com
>
>

Since there has been no activity on this thread since before the CF and
no response from the author I have marked this "returned with feedback".

Alexandra, feel free to resubmit it to the next commitfest, when you
have time to address the issues raised in the review.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company