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

On 06/11/2015 07:55 AM, Charles Clavadetscher wrote:
> 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.

Remember you can also use pg_hba.conf to restrict access:

http://www.postgresql.org/docs/9.4/interactive/auth-pg-hba-conf.html

>
> Regards
> Charles
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Donnerstag, 11. Juni 2015 16:28
> To: pgsql-general@postgresql.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@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com