Re: Prevent roles not having admin option from granting themselves to other roles

Hello

Well I was thinking a litte more on this. Basically I think that it could
end up with a small set of "rules":

- Organize privileges in groups (nologin, inherit) is a way that
independently of how many levels of indirection you have each definite scope
or application has a single group to "set role" into.
- Users have noinherit, thus forcing applications they use to log into the
database to perform a set role to the group designed for that application.
- Users have individually CONNECT privilege to the database(s) they need to
connect to.
- Users have no other privileges granted directly (apart from connect to
database) to avoid switching between group and user.

Well at least where I am it is getting late and I guess I can use some fresh
air.

Regards
Charles

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Charles
Clavadetscher
Sent: Donnerstag, 11. Juni 2015 16:28
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Prevent roles not having admin option from granting
themselves to other roles

Hello Adrian

Thank you very much for your response. As a matter of fact I already had a
look into the NOINHERIT attribute and its consequences. This partially
solves the problem but makes the usage of user accounts in applications more
complex.

If I wanted to create a group for accessing the database, i.e. grant CONNECT
to the group and grant the group to the users that should be able to access
that specific database, then users having the NOINHERIT attribute will not
be able to connect. So I would end up granting CONNECT to each individual
user. The approach would help in terms of disabling the ability for a role
granting itself to another to pass on the CONNECT privilege (for the same
reason that it does not work with groups). But legitimate users in the
database would not profit from the grouping ability or, better said, the
application would need to switch between containing roles in order to
perform their tasks. This requires the application to know in which roles to
"set" into in order to access specific objects.

Maybe I still don't see the point, but this approach seems to me to be more
complicated (and error prone), while it would be much simpler to only allow
superusers to grant roles to roles.

My original question was also about the reason why ordinary users should be
able to grant all their privileges to others granting them their role. This
sounds more of less like giving out your password. The latter we may not be
able to prevent, but the first could have a technical solution.

Regards
Charles

>See here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html
>
>In particular the section on INHERIT and in NOTES this:
>
>"The INHERIT attribute is the default for reasons of backwards
>compatibility: in prior releases of PostgreSQL, users always had access
>to
all privileges of groups they were members of. However, NOINHERIT provides a
closer match to the semantics specified in the SQL standard."
>
>Also see here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html
>
>for what SET ROLE does under INHERIT versus NOINHERIT

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