Re: How to create read-only view on 9.3

On 08/13/2013 03:25 PM, Merlin Moncure wrote:
> On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata
> <katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:
>> Hi Szymon,
>>
>> Thank you for response.
>>
>>
>>>> Could you show an example?
>> I do below things on one server.
>> The path to database cluster and port are
>> different with each other.
>>
>> [9.2.4]
>> initdb --no-locale -E UTF8
>> pg_ctl start
>> createdb testdb
>> psql testdb -c "create table tbl(i int)"
>> psql testdb -c "insert into tbl values (generate_series(1,10))"
>> psql testdb -c "create view v as select * from tbl"
>>
>> [9.3beta2]
>> pg_dump -p <port of 9.2.4> testdb > /tmp/92dmp.dmp
>> initdb --no-locale -E UTF8
>> pg_ctl start
>> createdb testdb
>> psql testdb -f /tmp/92dmp.dmp
>>
>>
>> After all, the view v became updatable view.
> 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 ?

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ