Hi,
Could anyone please suggest me how to deal with my following requirements. So far, I
have done following to meet my requirements:
I want users to
use only stored procedures to create, alter, delete tables in mydb
schema. ==> For this, I have revoked all permissions from
mydb schema, and stored procedures are defined with SECURITY
DEFINER in postgres user context. I have given execute permission to set
of users on these stored procedures to achieve my goal.
I want only privileged users to create table with foreign key
references. ==> This can be achieved using SET ROLE current user before executing create command but Postgresql doesn't allow running SET ROLE in SECURITY DEFINER function
context so I have created a my_sudo function which gets
invoked from my stored procedure. This sudo function creates a temporary
SECURITY DEFINER function and changes owner to the current user before
executing create table command.
Now, as sudo function runs actual create command as current user
context and he/she does not have permission on mydb schema(as revoked all permission to meet first requirement), I have to
grant the ALL permissions on mydb schema to current user temporary and
then restore his/her actual privileges back to make sure that users
actual permission doesn't change.
Problem: Could anyone tell me how to get the schema permissions list for current user and
restore it back once store procedure execution completed.
Please feel free to let me know if you have any
questions.
Thanks a lot,
Dipti
