On 08/13/2013 06:23 PM, Andrew Dunstan wrote:
>
> On 08/13/2013 12:09 PM, Merlin Moncure wrote:
>> On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing
>> <hannu@2ndquadrant.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.
In this case implementing required functionality does change behaviour
in quite substantial way.
If you earlier used views for granting limited read access to some views
you definitely did not want view users suddenly gain also write access to
underlying table.
You also probably did not GRANT only SELECT to your views as this was
the default anyway,
>> 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.
Superuser can easily disable or drop the trigger as well.
>
> cheers
>
> andrew
>
>
>
>
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ