Can Postgres restrict usage of the SET and SHOW commands?

Question

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.

Answer 1

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.