Re: Monitoring roles patch - Mailing list pgsql-hackers

> On Mar 28, 2017, at 11:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Mark Dilger <hornschnorter@gmail.com> writes:
>> After a bit of introspection, I think what is really bothering me is not the
>> inability to revoke permissions, since as you say I can choose to not assign
>> the role to anybody.  What bothers me is that this feature implicitly redefines
>> what is meant by the keyword PUBLIC.  If I go around the database
>> revoking privileges on everything from PUBLIC, I should end up with
>> a database that is inaccessible to anyone but superuser, right?
>
> Ummm ... not if you've granted specific permissions to some users.
> If you did "GRANT SELECT ON TABLE x TO joe", no amount of public-privilege
> revocation makes that go away.  This isn't so much different.

I think it is pretty different.  It is not unreasonable to lock down a freshly installed
database by revoking privileges on various objects from PUBLIC prior to creating
any users.  Before the proposed change, that would have different consequences
than after the proposed change.

> It's fair to object that the problem with this approach is that the
> permissions available to these special roles aren't visible in the
> privilege-related system catalog columns.  But we've been around on that
> discussion and agreed that it's impractical to have a separate GRANT bit
> for every little bit of privilege that someone might want.  So the plan is
> to have these special roles that are known at the C-code level, and then
> granting one of these roles to user X effectively grants user X certain
> fine-grained privileges.

I'm still struggling to understand the motivation for hardcoded permissions
checks.

I don't see anything wrong with adding roles in pg_authid.h with a #define'd
Oid.  That's actually pretty helpful for anyone writing code against the database,
as they don't have to look up the Oid of the role.

But why not then grant privileges to that role in information_schema.sql?
That would avoid all the special case logic in the code, and would mean that
people could add and remove privileges from the role to suit their own
security policies.  For somebody configuring security policies on a database
prior to creating any users, these changes could be made at that time.

No need to write up an explanation.  I'm guessing you have a link to a
discussion where this was all discussed to death.

> But you can't see anything except the role grant
> in the catalogs.  You just have to read the documentation to find out what
> that really means.
>
>> But after this proposed
>> change, IIUC, there would still be a bit of access available to this/these
>> proposed non-superuser role(s) which have hardcoded permissions.
>
> Right, but they are roles not users, ie they do not have the LOGIN bit.
> So the only way to use them is via GRANT.
>
> I think there is a fair question about how to document this clearly, but
> the design seems reasonable.
>
>             regards, tom lane