Designing Postgres Security Model

Поиск
Список
Период
Сортировка
От dipti shah
Тема Designing Postgres Security Model
Дата
Msg-id d5b05a951003280253w65de0293p32409abdce079004@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
Дерево обсуждения
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tadipathri Raghu
Дата:
Сообщение: Why index occupy less amount of space than the table with same structure.
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: Why index occupy less amount of space than the table with same structure.