Thread: grant everything on everything and then revoke

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.

But I read:

http://www.postgresql.org/docs/8.3/static/sql-grant.html
The SQL standard does not support setting the privileges on more
than one object per command.

This is going to make maintenance and development a PITA every time I
add a new table, sequence, schema...

Defining a role/group with all grant access and then assigning that
group to all users is going to make this a bit less painful, but
still every time I'm going to add something to the DB I'll have to
remember to modify the group privileges.

Even when things will evolve, all users should be able to do
everything to most object with a few exception so it is easier to
revoke than to grant.

Any advice even with completely different approach?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Mar 3, 2009, at 4:35 AM, Ivan Sergio Borgonovo wrote:

> But I read:
>
> http://www.postgresql.org/docs/8.3/static/sql-grant.html
> The SQL standard does not support setting the privileges on more
> than one object per command.
>
> This is going to make maintenance and development a PITA every time I
> add a new table, sequence, schema...

There is some pl/pgsql code here grant on more than one object at a
time:

http://pgedit.com/tip/postgresql/access_control_functions





John DeSoi, Ph.D.

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.

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.

This looks really neat for the beginning and it doesn't even look as
an hack ;)
What if I had to differentiate privileges of each user?
Will revoking privileges on each users work?

I didn't understand how

CREATE SCHEMA schemaname AUTHORIZATION username;

AUTHORIZATION really works and maybe it could be another way to
approach the problem.

Thanks to everybody.

Even the pointer to the functions was interesting.
The acl_admin.grant_on_all seems what my initial quest was looking
for, but the ROLE trick seems much more straight forward currently.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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