rights for schema

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-

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

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.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;

and checks some other datarows, which stores additional information about the user
The 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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.

Thanks

Phil

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general