Re: rights for schema

On 12/31/2012 02:40 PM, Philipp Kraus wrote:
>
>> So the issue was that the user calling the function did not have access to the schema usermanagement. It is also possible the user did not have EXECUTE privileges on the function either. From your subsequent post I see you discovered SECURITY DEFINER. This is works as you found out. Just be aware that if the user that defined the function is a super user the function has their privileges and all that implies.
>
> At the moment I create a test database with 2 users, one of these users is a super user, but later IMHO it can be a security issue. The working user can not see or modify the function. Do you have got a tip for a good secure solution.
> My first idea is, that I create all database structure with an user, which is later not a super user, so I remove the super user role.

Security is a complex problem and I am still sorting out all the
possibilities just within Postgres itself, so take the following with
that in mind.

My general rules are.
1) Some set of the data need to be accessed.
2) Users need to be let in to see that data.
3) Security is matching 1 and 2 with as little privilege leakage as
possible.

I personally do something similar to what you propose, create an admin
role that has privileges between a superuser role and a regular role.
The admin creates the schema and regular roles are granted access as
needed. My needs are fairly simple and the databases not that complex,
nor are they world viewable. For cases that are not covered by the
previous, others on this list would have more appropriate suggestions.

>
> Thanks
>
> Phil
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com