Re: Remove default privilege from DB

On Fri, Feb 16, 2018 at 8:59 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Tom,
>
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > > * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> > >> ​Not sure if this is what you mean but there is no concept of
> "negative
> > >> state" in the permissions system. Everything starts out with no
> > >> permissions. Grant adds permissions and revoke un-adds granted
> > >> permissions.​ Revoking something that doesn't exist is either a
> no-op or a
> > >> warning depending on the context - either way its doesn't setup a
> > >> "forbidden" state for the permission.
> >
> > > This isn't entirely correct. Functions are the classic example where
> > > EXECUTE to PUBLIC is part of the default and the "negative" state of
> > > having a function where EXECUTE is REVOKE'd from PUBLIC is entirely
> > > reasonable and even common.
> >
> > FWIW, I thought David's description was fine. The fact that the initial
> > state of an object typically includes some positive grants doesn't change
> > the fact that there's no such thing as a negative grant. In particular,
> > if there is a GRANT TO PUBLIC, no amount of revoking that privilege from
> > individual users will have any effect, because the public grant is still
> > there.
>
> What I was particularly picking up on was the comment that "Everything
> starts out with no permissions" which implied, at least to me, the idea
> that no one has any rights on an object until those rights are GRANT'd,
> which isn't correct, as I described.
>

I think its more helpful to say "there are no inherent permissions" and
have someone ask "then why can I execute this function" and respond
"because the system places every user in to a group called PUBLIC which
(like all roles/groups) has a set of granted inheritable permissions; and
by default it gets ... on all newly created ...;". And, no, you
unfortunately cannot remove a user from the PUBLIC group. You can revoke
the permissions granted to PUBLIC by executing using REVOKE ... FROM PUBLIC.

Basically, you force the learner to confront/incorporate how the
default/PUBLIC privilege defaults system is configured to enhance the
usability of a "deny all first" based permission system. Starting from a
point of "users have these defaults" obscures that fact and leads to
questions like this - which as I said I had myself way back when. Starting
from the deny-all beginning was an "ah-ha! moment for me".

David J.