Re: [v9.2] Fix Leaky View Problem

On Sun, Sep 25, 2011 at 11:22:56PM -0400, Robert Haas wrote:
> On Sun, Sep 25, 2011 at 10:38 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > On Sun, Sep 25, 2011 at 11:22:03AM -0500, Kevin Grittner wrote:
> >> Robert Haas ?09/25/11 10:58 AM >>>
> >>
> >> > I'm not sure we've been 100% consistent about that, since we
> >> > previously made CREATE OR REPLACE LANGUAGE not replace the owner
> >> > with the current user.
> >>
> >> I think we've been consistent in *not* changing security on an
> >> object when it is replaced.
> >
> >> [CREATE OR REPLACE FUNCTION does not change proowner or proacl]
> >
> > Good point. ?C-O-R VIEW also preserves column default values. ?I believe we are
> > consistent to the extent that everything possible to specify in each C-O-R
> > statement gets replaced outright. ?The preserved characteristics *require*
> > commands like GRANT, COMMENT and ALTER VIEW to set in the first place.
> >
> > The analogue I had in mind is SECURITY DEFINER, which C-O-R FUNCTION reverts to
> > SECURITY INVOKER if it's not specified each time. ?That default is safe, though,
> > while the proposed default of security_barrier=false is unsafe.
>
> Even though I normally take the opposite position, I still like the
> idea of dedicated syntax for this feature. Not knowing what view
> options we might end up with in the future, I hate having to decide on
> what the general behavior ought to be. But it would be easy to decide
> that CREATE SECURITY VIEW followed by CREATE OR REPLACE VIEW leaves
> the security flag set; it would be consistent with what we're doing
> with owner and acl information and wouldn't back us into any
> unpleasant decisions down the road.

I prefer the previous UI (WITH (security_barrier=...)) to this proposal, albeit
for diffuse reasons. Both kinds of views can have the consequence of granting
new access to data. One kind leaks tuples to untrustworthy code whenever it's
convenient for performance, and the other does not. A "non-security view" would
not mimic either of these objects; it would be a mere subquery macro. Using
WITH (...) syntax attached to the CREATE VIEW command better evokes the
similarity between the alternatives we're actually offering. I also find it
mildly odd letting CREATE OR REPLACE VIEW update an object originating with
CREATE SECURITY VIEW.

Unqualified CREATE VIEW will retain no redeeming value apart from backward
compatibility; new applications with any concern for database-level security
should use only security_barrier=true and mark functions LEAKPROOF as needed.

nm