Postgres: set_config (). current_setting () private / reliable stack for application variables?
In my application, I have triggers that need access to things like user ID. I save this information with
set_config('PRIVATE.'|'user_id', '221', false)
then when I do operations that alter the database, triggers can do:
user_id = current_setting('PRIVATE.user_id');
it works fine. My database activities are mostly from python, psycopg2, as soon as I get a connection, I will do set_config () as my first operation and then go about my business on the database. Is this a good practice, or could data leak from one session to another? I did things like this with SD and GD variables in plpython, but this language was too heavy for what I was trying to do, so I had to switch to plpgsql.
source to share
While that's not exactly what they are for, you can use GUCs as session variables.
They can also be associated with transactions, with an equivalent SET LOCAL
or set_config
.
As long as you do not allow the user to run arbitrary SQL, they are a reasonable choice, and session local GUCs are not shared with other sessions. They are not intended for secure local storage, but they are convenient places to store things like the current user app if you are not using SET ROLE
or SET SESSION AUTHORIZATION
for that.
Remember the user can define them via environment variables if you allow them to run a client based on libpq
eg.
$ PGOPTIONS="-c myapp.user_id=fred" psql -c "SHOW myapp.user_id;"
myapp.user_id
---------------
fred
(1 row)
Also, on older versions of PostgreSQL, you had to declare the namespace in postgresql.conf
before you could use it.
source to share