Postgres: is set_config(). current_setting() a private/robust stack for application variables?

Question

In my application I have triggers that need access to things like user id. I am storing that information with

set_config('PRIVATE.'|'user_id', '221', false)

then, while I am doing operations that modify the database, triggers may do:

user_id = current_setting('PRIVATE.user_id');

it seems to work great. My database actions are mostly from python, psycopg2, once I get a connection I'll do the set_config() as my first operation, then go about my database business. Is this practice a good one or could data leak from one session to another? I was doing this sort of thing with the SD and GD variables in plpython, but that language proved too heavy for what I was trying to do so I had to shift to plpgsql.

Answer 1

While it's not really what they're designed for, you can use GUCs as session variables.

They can also be transaction scoped, with SET LOCAL or the set_config equivalent.

So long as you don't allow the user to run arbitrary SQL they're a reasonable choice, and session-local GUCs aren't shared with other sessions. They're not designed for secure session-local storage but they're handy places to stash things like an application's "current user" if you're not using SET ROLE or SET SESSION AUTHORIZATION for that.

Do be aware that the user can define them via environment variables if you let them run a libpq based client, e.g.

$ PGOPTIONS="-c myapp.user_id=fred" psql -c "SHOW myapp.user_id;"
 myapp.user_id 
---------------
 fred
(1 row)

Also, on older PostgreSQL versions you had to declare the namespace in postgresql.conf before you could use it.