Thread: "default deny" for roles
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. What say? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> There are situations where a "default deny" policy is the best fit.
> To that end, I have a modest proposal:
> REVOKE PUBLIC FROM role;
Neither possible nor sensible. PUBLIC means everybody, and is
implemented in a way that doesn't allow any other meaning.
We pretty much have "default deny" at the other end anyway, in that most
types of objects start out without any permissions granted to PUBLIC.
So I don't think you've made an adequate (or indeed any) case for
needing this, even if it were redesigned into something less screwy.
regards, tom lane
David,
* David Fetter (david@fetter.org) wrote:
> There are situations where a "default deny" policy is the best fit.
That's certainly true. It's also what we *have*. The only places where
we aren't "default deny" are places where things have been granted to
"public".
Feel free to revoke "public" from the objects in your environment.
Thanks,
Stephen
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
On 08/28/2012 09:09 PM, Craig Ringer wrote: > 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? No. Try it and see. You can do a lot without having any access rights at all to the catalog tables. cheers andrew
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.
regards, tom lane
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