Re: "default deny" for roles

On 08/29/2012 01:25 AM, David Fetter wrote:
> Folks,
>
> There are situations where a "default deny" policy is the best fit.
>
> To that end, I have a modest proposal:
>
>      REVOKE PUBLIC FROM role;
>
> Thenceforth, the role in question would only have access to things it
> was specifically granted.

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?

Is that the idea? To restrict system catalog access?

You'd have to GRANT every function, every INFORMATION_SCHEMA and 
pg_catalog entry, etc. Is that really your intention?

All public gets by default is:

- CONNECT, TEMPORARY on databases
- USAGE on trusted PLs
- USAGE on schema
- EXECUTE on functions

as per http://www.postgresql.org/docs/9.1/static/sql-grant.html:

----
Depending on the type of object, the initial default privileges might 
include granting some privileges to PUBLIC. The default is no public 
access for tables, columns, schemas, and tablespaces; CONNECT privilege 
and TEMP table creation privilege for databases; EXECUTE privilege for 
functions; and USAGE privilege for languages. The object owner can of 
course revoke these privileges.
----

This *doesn't* mention the system catalogs, which it perhaps should, but 
otherwise makes it pretty clear that `public` doesn't get to do much.

--
Craig Ringer