Re: How to create read-only view on 9.3

On 08/13/2013 12:09 PM, Merlin Moncure wrote:
> On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>> On 08/13/2013 03:25 PM, Merlin Moncure wrote:
>>> I chatted about this on IRC for a bit. Apparently, updatability of
>>> views is a mandatory feature in the sql standard and by relying on the
>>> read-only-ness you were relying on non-standard behavior essentially.
>>> I admit this is a pretty big pain (and I'm a real stickler for
>>> backwards compatibility) but it's pretty hard to argue with the
>>> standard. Workarounds are to revoke various privileges.
>> Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...;
>> when dumping views from older postgreSQL versions ?
> I thought so initially until I learned that views are expressly
> read-write per the standard; we're not changing behavior but
> implementing required functionality. So (at the least) I don't think
> it's fair to expect users who don't care about this point to have to
> go re-GRANT the appropriate privs -- so if you did that I think it
> would have to be an optional switch to pg_dump. That said, it's
> pretty much a given this is going to burn some people and given the
> potential security considerations maybe some action is warranted.
> Personally, I'd be satisfied with a dump time warning though or
> perhaps a strongly worded note in the documentation?
>
>

In any case, using permissions is a somewhat leaky bandaid, since
superusers have overriding access privileges anyway. A better way to do
what the OP wants might be to have a view trigger that raises an exception.

cheers

andrew