Thread: 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
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.htmlI 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 modifiesonly the "view", so do the user also get access to the trigger (execution right)? IMHO the trigger is runby 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;
and checks some other datarows, which stores additional information about the user
The trigger calls a function "isAdministrator" within the schema.
Thanks
Phil
Am 31.12.2012 um 18:02 schrieb Philipp Kraus:
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.htmlI 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 modifiesonly the "view", so do the user also get access to the trigger (execution right)? IMHO the trigger is runby 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 usermanagementusermanagement ist ein schema and the base table calls a function within this schema, thischecks if the user is super user withselect current_setting('is_superuser') = 'on' into l_issuper;and checks some other datarows, which stores additional information about the userThe trigger calls a function "isAdministrator" within the schema.
I have changed the security option of the function from "security invoker" to "security definer" and it works :-)
Phil
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