Re: Monitoring roles patch

Mark Dilger <hornschnorter(at)gmail(dot)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.

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. 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