Re: pie-in-sky idea: 'sensitive' function parameters

On 05/29/20 14:51, Robert Haas wrote:
> stuff that works today. We have options to log queries before parsing
> them. You can't redact sensitive details at that point because you
> don't know whether the query contains any such details, or where they
> are located. You can't postpone logging the query without changing the
> behavior of the system in user-visible ways. What if parsing gets
> stuck in an infinite loop and you want to look at the log to see which
> query got stuck? What if you want to look at the timestamps on the log

A possible step in the direction of good-enough would be to have
'sensitive' flags only in the parameter-bind message of the extended
protocol.

The query you send down has only placeholders. The sensitive stuff
comes in the bind message. Recognizing a 'sensitive' bit for a parameter
in a bind message ought to be entirely feasible. If parsing the query
failed and got logged because of the failure, the log record just
shows the placeholders.

Again, to protect from the other kind of misuse (a malicious query
hiding values from the log), there should be a check after parsing/
analysis that the values sent with 'sensitive' flags in the bind
message are exactly the ones that were so declared.

A stored function/procedure might have a way to declare some of its
parameters that way, with some default role controlling permission
to declare such a function.

Maybe you could even declare such parameters directly in the syntax
of preparing a query (with, again, control over who gets to prepare
such a query). There again, the correspondence of those declarations
to the bind message can't be verified until after parsing/analysis,
but meanwhile the sensitive values are clearly flagged in the bind
message, and any logging on a parse failure is still able to avoid
logging them.

Regards,
-Chap