I have just noticed that "SET ROLE" doesn't work from security definer function. I don;t know why but it clearly gives the error that SET role doesn;t work in security definer context.
Basically, I am trying to write a store procedure which creates a table asked by user along with other associated logging tables and event tables automatically. I want to make sure that when users use my stored procedure to create table, they should be allowed only if they have permission to do so.
If I create function in postgres user with Security Definer enabled, it will allow to create any table with any foreign references etc...So I am setting role to current_user in my function and then creating a table to make sure that user has the appropriate privilege.
Since, SET Role is failing in security definer context, I am helpless now. Could anyone suggest any workaround or solution to this issue. Is this is known issue? Anybody already encountered it?
