Re: Get the list of permissions/privileges on schema - Mailing list pgsql-general

Okay..then could you please suggest me what could be the correct way? 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 to these stored procedures to achieve my goal.

I want only allowed users to create table with foreign key references. ==> This can be achieve using SET ROLE current user but Postgresql doesn't allow 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 will not have permission on mydb schema, 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.

Hence, I am asking how can I store the schema permissions list and restore it back once store procedure execution completed.

Please let me know where I am going wrong here? I am trying to get my things done out of what PostGreSQL supports.

It would be really nice if anyone could help me to achieve my requirements.

Please feel free to let me know if you have any questions.

Thanks a lot,
Dipti