Обсуждение: rights for schema

Hello,

can I set all rights to a schema and its content? I have different schema and in the public schema all users should be
doeverything (select, update, delete, call functions, etc). 
Also if I add a new user, the user should be get also the rights.
My other schemas are only access by the database user only.

How can I do this?

Phil

Am Sun, 30 Dec 2012 15:54:32 +0100
schrieb Philipp Kraus <philipp.kraus@flashpixx.de>:

> Hello,
>
> can I set all rights to a schema and its content? I have different
> schema and in the public schema all users should be do everything
> (select, update, delete, call functions, etc). Also if I add a new
> user, the user should be get also the rights. My other schemas are
> only access by the database user only.
>
> How can I do this?
>
> Phil

AFAIK you can't set all rights for all tables, views, functions etc.
inside a schema "recursively" with one command. Neither can you say
that, inside a specific schema, every newly created table or function
should automatically get a "default" access list.
(the "template1" database can only be used to do the latter for newly
created databases).

What you could do is to create a group ("role"), put all your users
in it and grant permissions to that role.

Remember, though, that newly created users have to be explicitly added
to that group by the admin and new tables have to be given access to
that role by the admin or the creating user!

bye,

-christian-

On 12/30/2012 04:06 PM, Christian Hammers wrote:
> Am Sun, 30 Dec 2012 15:54:32 +0100
> schrieb Philipp Kraus <philipp.kraus@flashpixx.de>:
>
>> Hello,
>>
>> can I set all rights to a schema and its content? I have different
>> schema and in the public schema all users should be do everything
>> (select, update, delete, call functions, etc). Also if I add a new
>> user, the user should be get also the rights. My other schemas are
>> only access by the database user only.
>>
>> How can I do this?
>>
>> Phil
>
> AFAIK you can't set all rights for all tables, views, functions etc.
> inside a schema "recursively" with one command. Neither can you say
> that, inside a specific schema, every newly created table or function
> should automatically get a "default" access list.
> (the "template1" database can only be used to do the latter for newly
> created databases).

Actually as of 9.0 that is not strictly true:
http://www.postgresql.org/docs/9.2/interactive/sql-alterdefaultprivileges.html




--
Adrian Klaver
adrian.klaver@gmail.com

Am 31.12.2012 um 02:11 schrieb Adrian Klaver:

> On 12/30/2012 04:06 PM, Christian Hammers wrote:
>> Am Sun, 30 Dec 2012 15:54:32 +0100
>> schrieb Philipp Kraus <philipp.kraus@flashpixx.de>:
>>
>>> Hello,
>>>
>>> can I set all rights to a schema and its content? I have different
>>> schema and in the public schema all users should be do everything
>>> (select, update, delete, call functions, etc). Also if I add a new
>>> user, the user should be get also the rights. My other schemas are
>>> only access by the database user only.
>>>
>>> How can I do this?
>>>
>>> Phil
>>
>> AFAIK you can't set all rights for all tables, views, functions etc.
>> inside a schema "recursively" with one command. Neither can you say
>> that, inside a specific schema, every newly created table or function
>> should automatically get a "default" access list.
>> (the "template1" database can only be used to do the latter for newly
>> created databases).
>
> Actually as of 9.0 that is not strictly true:
> http://www.postgresql.org/docs/9.2/interactive/sql-alterdefaultprivileges.html

Thanks, I use PG >= 9.0, so I have set the default privileges

Phil

Am 31.12.2012 um 02:11 schrieb Adrian Klaver:

> On 12/30/2012 04:06 PM, Christian Hammers wrote:
>> Am Sun, 30 Dec 2012 15:54:32 +0100
>> schrieb Philipp Kraus <philipp.kraus@flashpixx.de>:
>>
>>> Hello,
>>>
>>> can I set all rights to a schema and its content? I have different
>>> schema and in the public schema all users should be do everything
>>> (select, update, delete, call functions, etc). Also if I add a new
>>> user, the user should be get also the rights. My other schemas are
>>> only access by the database user only.
>>>
>>> How can I do this?
>>>
>>> Phil
>>
>> AFAIK you can't set all rights for all tables, views, functions etc.
>> inside a schema "recursively" with one command. Neither can you say
>> that, inside a specific schema, every newly created table or function
>> should automatically get a "default" access list.
>> (the "template1" database can only be used to do the latter for newly
>> created databases).
>
> Actually as of 9.0 that is not strictly true:
> http://www.postgresql.org/docs/9.2/interactive/sql-alterdefaultprivileges.html

I have create a view for access a table and rules for modifiy the based table. The table has got a trigger.
If I run an update on the view, I get an error "permission denied" for the trigger call. My user modifies
only the "view", so do the user also get access to the trigger (execution right)? IMHO the trigger is run
by postgres self, so the user don't need a right on the trigger.

Can you explain me the correct accessibility structure of PG?

Phil

On 12/31/2012 05:41 AM, Philipp Kraus wrote:
>
> Am 31.12.2012 um 02:11 schrieb Adrian Klaver:
>

>>
>> Actually as of 9.0 that is not strictly true:
>> http://www.postgresql.org/docs/9.2/interactive/sql-alterdefaultprivileges.html
>
> I have create a view for access a table and rules for modifiy the based table. The table has got a trigger.
> If I run an update on the view, I get an error "permission denied" for the trigger call. My user modifies
> only the "view", so do the user also get access to the trigger (execution right)? IMHO the trigger is run
> by postgres self, so the user don't need a right on the trigger.

Was the error for the trigger or the function the trigger called?

>
> Can you explain me the correct accessibility structure of PG?
>
> Phil
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/31/2012 09:02 AM, Philipp Kraus wrote:
>
> Am 31.12.2012 um 15:54 schrieb Adrian Klaver:
>
>> On 12/31/2012 05:41 AM, Philipp Kraus wrote:
>>>
>>> Am 31.12.2012 um 02:11 schrieb Adrian Klaver:
>>>
>>
>>>>
>>>> Actually as of 9.0 that is not strictly true:
>>>> http://www.postgresql.org/docs/9.2/interactive/sql-alterdefaultprivileges.html
>>>
>>> I have create a view for access a table and rules for modifiy the
>>> based table. The table has got a trigger.
>>> If I run an update on the view, I get an error "permission denied"
>>> for the trigger call. My user modifies
>>> only the "view", so do the user also get access to the trigger
>>> (execution right)? IMHO the trigger is run
>>> by postgres self, so the user don't need a right on the trigger.
>>
>> Was the error for the trigger or the function the trigger called?
>
> ERROR:  permission denied for schema usermanagement
>
> usermanagement ist ein schema and the base table calls a function within
> this schema, this
> checks if the user is super user with
>
> select current_setting('is_superuser') = 'on' into l_issuper;

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.



--
Adrian Klaver
adrian.klaver@gmail.com

Am 31.12.2012 um 22:18 schrieb Adrian Klaver:

> On 12/31/2012 09:02 AM, Philipp Kraus wrote:
>>
>> Am 31.12.2012 um 15:54 schrieb Adrian Klaver:
>>
>>> On 12/31/2012 05:41 AM, Philipp Kraus wrote:
>>>>
>>>> Am 31.12.2012 um 02:11 schrieb Adrian Klaver:
>>>>
>>>
>>>>>
>>>>> Actually as of 9.0 that is not strictly true:
>>>>> http://www.postgresql.org/docs/9.2/interactive/sql-alterdefaultprivileges.html
>>>>
>>>> I have create a view for access a table and rules for modifiy the
>>>> based table. The table has got a trigger.
>>>> If I run an update on the view, I get an error "permission denied"
>>>> for the trigger call. My user modifies
>>>> only the "view", so do the user also get access to the trigger
>>>> (execution right)? IMHO the trigger is run
>>>> by postgres self, so the user don't need a right on the trigger.
>>>
>>> Was the error for the trigger or the function the trigger called?
>>
>> ERROR:  permission denied for schema usermanagement
>>
>> usermanagement ist ein schema and the base table calls a function within
>> this schema, this
>> checks if the user is super user with
>>
>> select current_setting('is_superuser') = 'on' into l_issuper;
>
> So the issue was that the user calling the function did not have access to the schema usermanagement. It is also
possiblethe user did not have EXECUTE privileges on the function either. From your subsequent post I see you discovered
SECURITYDEFINER. This is works as you found out. Just be aware that if the user that defined the function is a super
userthe 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
securityissue. 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
superuser role.  

Thanks

Phil

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
possiblethe user did not have EXECUTE privileges on the function either. From your subsequent post I see you discovered
SECURITYDEFINER. This is works as you found out. Just be aware that if the user that defined the function is a super
userthe 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
securityissue. 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
superuser 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.klaver@gmail.com