Re: "default deny" for roles

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 08/28/2012 09:09 PM, Craig Ringer wrote:
>> Wouldn't that render the user utterly unable to do anything until you
>> added a bunch of GRANTs on the system catalogs for that user or a
>> group they're a member of?

> Try it and see. You can do a lot without having any access rights at all
> to the catalog tables.

Craig's got a really good point though: if we had the ability to "revoke
public", it would mean that something as simple as "SELECT 2+2" would
stop working. Or at least it ought to, since execute permissions on
int4pl() are granted to PUBLIC, and the proposal is for the role to not
have such permissions.

While you can in fact do a lot without any explicit catalog access,
I doubt that anyone will get far without the ability to use "+", "=",
"count()", etc. So that sounds like a killer argument from here.

The only way you would end up with a usable database is if you somehow
said "well, I didn't really mean that for built-in objects" ... but at
that point I think you have to stop asking to change the behavior of the
PUBLIC role. Instead make your own user-defined role that includes all
your users except for the locked-down roles, and grant permissions on
your non-system objects to that role not PUBLIC.

regards, tom lane