On Tue, 03 Mar 2009 09:29:17 -0800
John R Pierce <pierce@hogranch.com> wrote:
> Ivan Sergio Borgonovo wrote:
> > I'd like to have different users mainly to have a different
> > search schema path.
> > Things may evolve so this is not going to be the only reason to
> > have more than one user.
> >
> > But I'm faced with the problem of granting the same access of the
> > owner of the db to the other users.
> >
> have the database owned by a 'ROLE and make your users members of
> that ROLE.
OK I did it... at least I think I did it.
I created a DB.
The owner of this DB is a "role".
createdb --encoding=UNICODE --owner=main_role db_test
I restored a DB on it.
I created new roles belonging to the former role.
create role sub_role with
login
in role main_role
encrypted password 'XXXX';
I accessed the DB from the new roles (sub_role).
**Everything worked fine (including UPDATE, INSERT etc...).**
It seems that all object in public grant all permissions to
everybody.
But then...
I created schemas owned by that role with same name as the role.
create schema authorization sub_role;
I moved some tables from public to that schema.
alter table sometable set schema sub_role;
I get permissions errors:
permission denied for schema sub_role CONTEXT: SQL statement
"UPDATE ONLY ....
1) I need to check if ownership of every piece is the one I thought I
set.
2) I'd like to know if this role nesting actually works with schema.
AUTHORIZATION should give ownership of the new schema to the user.
Doesn't ownership comes with all GRANT PRIVILEGES?
Can it be transferred this way?
I tried to make it works creating schemas with just:
create schema sub_role;
but the sub_role can't even SELECT.
I'd like to avoid to use:
http://pgedit.com/tip/postgresql/access_control_functions
since it add complexity and dependences.
Furthermore there are several objects I've to GRANT privileges on:
tables, sequences, functions...
Is the above script going to take care of all the objects?
Should I grant privileges to all the objects or does giving
privileges on some implicitly grant on others?
The simpler solution the better.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it