Re: "default deny" for roles

On 08/28/2012 10:42 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.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.
>
>             


Yeah, what I've done in the past is revoke public privs from the catalog 
tables and the information schema, and granted them to a pseudo-public 
role. This has left intact the public privs of things like int4pl(). 
This works quite well for hiding schema details from a non-member of the 
pseudo-public role, which was the aim. But if you want a user truly only 
able to use some specified functions, say, maybe you would revoke the 
lot. That's a fairly paranoid security model, but not beyond imagining.

(None of this is to say I think David's suggestion is a good one.)

cheers

andrew