Re: idea: custom log_line_prefix components besides application_name

On 10 May 2017 10:44 am, "Chapman Flack" <chap(at)anastigmatix(dot)net> wrote:

On 05/09/17 18:48, Mark Dilger wrote:

> I don't have any positive expectation that the postgres community will go
> along with any of this, but just from my point of view, the cleaner way to
> do what you are proposing is something like setting a session variable.
>
> In your middle tier java application or whatever, you'd run something like
>
> SET SESSION ON BEHALF OF 'joe user'

No need to do anything they custom and specific. No need for new syntax
either.

SET myapp.appuser = 'joe'

Or use SET LOCAL for xact scoped.

The other bit of my proposal was to prevent Mallory from spoofing
his appident info by managing to inject some SQL through your app

If your attacker gets that far you're kind of screwed anyway.

But that's where something like 'secure variables' or package variables
come in. See the mailing list discussion on that topic a couple of months
ago.

SET SESSION ON BEHALF OF 'joe user' BECAUSE I HAVE :cookie AND I SAY SO;

I do want something similar to this for SET SESSION AUTHORIZATION.

But for most things a secure variable model with a setter function should
work better.

Without any new syntax

Much, much more chance of this.

with it.

It's those more complex architectures I was thinking of with the client-
side ideas, where your code may be at the top of such a tall stack of
persistence/ORM/whatever layers that you're not sure you can just emit
an arbitrary SET command and have it come out in front of the right query
generated by the lower layers.

Surely in that case you have the same problem with something based on new
syntax?

That's where it might be handy to have a
way to associate the info with the current thread or current request
in a way that doesn't need any support in third party layers in the middle,
but can be retrieved by the driver (or a thin wrapper around it, down
at the bottom of the stack) and turned into the proper SET commands. That's
really a separable, less immediate, future-work idea.

I don't see how postgres can do anything about this. PgJDBC maybe. But
probably not.

The main part I would like is a generic mechs ism to inject the value of a
GUC into the logs.

For csvlog, it'd be a list of GUC names, each a to be emitted as a
separate field if set, or empty field if unset.

For normal log, it'd be available in log_line_prefix as something like

%(myapp.user)g

... or whatever.

I can see this being plenty useful for all sorts of work, and nicely
flexible.