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' > > And then when you reuse that connection for the next web page you > would do something like that again: > > SET SESSION ON BEHALF OF "anonymous from 184.108.40.206"
I may have muddled my presentation by starting it with the more-speculative ideas about building support into client-side drivers. At present, none of that exists, and in the simple case where the application code has access to the driver for sending arbitrary SQL, then yes, you just have it send SET SESSION ON BEHALF OF 'joe user' or SET SESSION ON BEHALF OF "anonymous from 220.127.116.11" or something equivalent but spelled differently, like SET appident.user TO 'joe user' or SET appident.user TO 'anonymous'; SET appident.inet_addr TO '18.104.22.168' Your spelling is nicer, but requires teaching the parser new syntax and touching lots of places in PostgreSQL core, probably a non-starter. In contrast, writing an 'appident' extension that defines some new GUCs would be trivial and well-encapsulated. (What I'd think ideal would be an extension that defines _whatever new GUCs make sense for your application_ so if it would serve your purpose to have appident.locale you just make it so. I wonder if an extension can define one GUC that you set in the config file to a list of other GUCs it should also define within its own namespace, or if that would run afoul of the order initialization happens in.) The other bit of my proposal was to prevent Mallory from spoofing his appident info by managing to inject some SQL through your app like "21' && sET_/**/cONfiG('appident.user', 'alice', fA/**/lsE)". That's where the appident.cookie() function comes in. You just query it once at session establishment and remember the cookie. That allows your code to say: SET SESSION ON BEHALF OF 'joe user' BECAUSE I HAVE :cookie AND I SAY SO; and Mallory can't inject that because he doesn't have :cookie and the appident.cookie() function only succeeds the first time. Without any new syntax, that could be spelled either: SELECT appident.set('user', 'joe user', is_local => false, cookie => :cookie) (where the cookie is simply verified by the SQL-callable function), or SET appident.user TO :cookie || 'joe user' (where it's verified by the check hook on the GUC, then stripped off). That's pretty much the total extent of what I would propose the extension to PostgreSQL proper would do. Just let you define some new GUCs with meaning to your application, and not interpret them or use them for anything, but provide a bit of protection so your code controls them and arbitrary SQL queries can't. The more science-fiction, client-side ideas I proposed were just ruminations on what might be useful to application code sitting on top of a taller stack of third-party code that might get in the way of just sending your arbitrary SET command ahead of your query. > and so forth. You wouldn't have to worry about threads since this is > being handled much like SET ROLE only instead of changing the role > under which your database handle is operating, it only changes an > opaque value that you can then use for whatever purpose. I would > not expect the database permissions logic to use the value in any > way, but just to preserve it for you to query in logging or from a stored > procedure or similar. As long as your jdbc driver or similar does not > prevent you from running non-standard sql statements, you should > be able to pass this down the line without any of the third party > software in the middle messing 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. 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. > If this feature were implemented, I'd probably use it. I might also be > willing to write this with you in the unlikely event that it gets community > approval. I might just go ahead and try writing the extension part. Isn't that the beauty of extensions? Approval? We don't need no stinkin' approval. :) But critiques and better ideas are never unwelcome. :) -Chap -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers