Is it possible to restrict a role from being able to set arbitrary session variables, or from being able to view them?

select current_setting('x');

show x;

Even after I've switched roles to a one with lesser permissions, I'm able to run both of the above and retrieve a value I might want hidden from that role. I think that it might be possible to lock down the first statement (that's a built-in function, is it not?), but I can't find anything in the documentation about the latter statement concerning grants or permissions.

  • If you want session specific "variables", you could use a temp table (with on commit preserve rows) Mar 31 '20 at 7:18

There are no session parameters in PostgreSQL; SET changes the value of a global parameter for the current session. If you use it to set a custom parameter (with a name containing .) that does not yet exist, a so-called placeholder parameter is created. There is no way to restrict permissions to view such a placeholder parameter.

What you can do is write a module in C that creates a real parameter. That module has to have a _PG_init function that calls DefineCustomStringVariable (if it is a string you want). That module would then be added to shared_preload_libraries, so that the parameter is defined when the server starts.

Such a parameter can be defined as GUC_SUPERUSER_ONLY, which means that only a superuser (or a member of the pg_read_all_settings role) can see its value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.