Re: How to create read-only view on 9.3

Hi,

Could anyone tell me how to create read-only view on
PostgreSQL 9.3 ?

I've been testing updatable views and noticed that
all simple views are updatable.

When I use pg_dump for upgrading from PostgreSQL 9.2
to PostgreSQL 9.3 and if the databse has views,
all views are updatable on the restored database.

I want to make these views read-only like PostgreSQL9.2.
How can I do this? Should I make access control on users ?
(Sorry, I couldn't find any explanations on document.)

regards,
--------------------
NTT Software Corporation
Tomonari Katsumata

On 13 August 2013 11:43, Tomonari Katsumata <
katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:

> Hi,
>
> Could anyone tell me how to create read-only view on
> PostgreSQL 9.3 ?
>
> I've been testing updatable views and noticed that
> all simple views are updatable.
>
> When I use pg_dump for upgrading from PostgreSQL 9.2
> to PostgreSQL 9.3 and if the databse has views,
> all views are updatable on the restored database.
>
> I want to make these views read-only like PostgreSQL9.2.
> How can I do this? Should I make access control on users ?
> (Sorry, I couldn't find any explanations on document.)
>
> regards,
> --------------------
> NTT Software Corporation
> Tomonari Katsumata
>
>
>
> Could you show an example?

Szymon

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.

-------
$ psql testdb
psql (9.3beta2)
Type "help" for help.

testdb=# select * from v;
i
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

testdb=# insert into v values (11);
INSERT 0 1
testdb=# select * from v;
i
----
1
2
3
4
5
6
7
8
9
10
11
(11 rows)

regards,
--------------------
NTT Software Corporation
Tomonari Katsumata

(2013/08/13 19:16), Szymon Guz wrote:
> On 13 August 2013 11:43, Tomonari Katsumata <
> katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:
>
>> Hi,
>>
>> Could anyone tell me how to create read-only view on
>> PostgreSQL 9.3 ?
>>
>> I've been testing updatable views and noticed that
>> all simple views are updatable.
>>
>> When I use pg_dump for upgrading from PostgreSQL 9.2
>> to PostgreSQL 9.3 and if the databse has views,
>> all views are updatable on the restored database.
>>
>> I want to make these views read-only like PostgreSQL9.2.
>> How can I do this? Should I make access control on users ?
>> (Sorry, I couldn't find any explanations on document.)
>>
>> regards,
>> --------------------
>> NTT Software Corporation
>> Tomonari Katsumata
>>
>>
>>
>> Could you show an example?
>
> Szymon
>

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.

merlin

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Ü

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?

merlin

merlin

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

All,

> 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.

I think it would be better to supply a script which revoked write
permissions from all views from all users, and distribute it with
PostgreSQL. I think that's doable as a DO $$ script.

If I wrote something like that, where would we drop it?

The fact that it won't revoke permissions from superusers isn't a real
problem, IMNSHO. If anyone is relying on superusers not being able to
do something, they're in for pain in several other areas.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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(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.
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Ü

* Hannu Krosing (hannu(at)2ndQuadrant(dot)com) wrote:
> 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,

I'm not really convinced that we should be catering to this argument of
"well, I knew it was gonna end up being read-only anyway, so I just
GRANT'd ALL"- consider that rules can make view writable, even in
existing releases.

Thanks,

Stephen

Hannu Krosing <hannu(at)2ndQuadrant(dot)com> writes:
> 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.

Unless you'd explicitly granted those users insert/update/delete privilege
on the view, they wouldn't suddenly be able to do something new in 9.3,
because no such privileges are granted by default. If you had granted
such privileges, you don't have much of a leg to stand on for complaining
that now they can do it.

I think this whole thread is nonsense. We expended a good deal of sweat
in 9.3 to add a feature that's *required by SQL standard*, and now people
are acting like we should turn it off. I do not believe that there are
many users for which this will be a problem; and we shouldn't let one
complaint drive us to do something silly.

In fact, I'm not sure there are *any* users for which this is a problem.
AFAICS there are two cases:

1. The view in question is owned by you. Then you have insert etc
privileges on it by default, and so 9.3 will let you insert into it
by default. But the view grants you no capability that you didn't have
anyway, just by inserting directly into the underlying table.

2. The view in question is not owned by you. Then you don't have insert
(or any other) privilege on it by default.

There's no "security hole" here; if someone can do something that
they couldn't do before, it's because you explicitly granted them
privileges to do so. I don't think you have a lot of room to complain
if those privileges now do what the SQL standard says they should do.

regards, tom lane

On 08/13/2013 01:33 PM, Hannu Krosing wrote:

>>
>> 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.

That's true, but it requires positive action to do so. Thus the trigger
can give you some protection in cases of stupidity, if not cases of malice.

cheers

andrew

On Tue, Aug 13, 2013 at 1:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> There's no "security hole" here; if someone can do something that
> they couldn't do before, it's because you explicitly granted them
> privileges to do so.

This point is completely bogus. Very, very few applications I've run
across in the entirety of my career use database enforced security at
all; it's generally done at the application level with the application
role as owner (or perhaps even superuser). You can call people names
or whatever for doing that but the point is it's common usage and
people *will* be affected.

> I don't think you have a lot of room to complain
> if those privileges now do what the SQL standard says they should do.

This point is completely correct and makes the previous argument moot.
This is not a 'security hole' but an 'obfuscation hole' so automatic
correction is not warranted. With the options on the table, I'd
prefer doing nothing or perhaps more strongly worded note in the docs
and possibly the release notes with a slight preference on doing
nothing.

merlin

On Tue, Aug 13, 2013 at 10:24:32AM -0700, Josh Berkus wrote:
> All,
>
> > 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.
>
> I think it would be better to supply a script which revoked write
> permissions from all views from all users, and distribute it with
> PostgreSQL. I think that's doable as a DO $$ script.
>
> If I wrote something like that, where would we drop it?
>
> The fact that it won't revoke permissions from superusers isn't a real
> problem, IMNSHO. If anyone is relying on superusers not being able to
> do something, they're in for pain in several other areas.
>

Something like this?

DO LANGUAGE plpgsql
$$
DECLARE v TEXT;
BEGIN
FOR v IN SELECT pg_catalog.quote_ident(schemaname) || '.' || pg_catalog.quote_ident(viewname)
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
EXECUTE 'REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ' || v || ' FROM PUBLIC';
END LOOP;
END;
$$;

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 08/13/2013 11:18 AM, Tom Lane wrote:
> Hannu Krosing <hannu(at)2ndQuadrant(dot)com> writes:
>> 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.
>
> Unless you'd explicitly granted those users insert/update/delete privilege
> on the view, they wouldn't suddenly be able to do something new in 9.3,
> because no such privileges are granted by default. If you had granted
> such privileges, you don't have much of a leg to stand on for complaining
> that now they can do it.

Ah, ok. I hadn't gotten to the testing phase yet.

I think we should have a script available for revoking all write privs
on all views and link it from somewhere (the release notes?), but I
don't see any need to change anything in the release.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Hi,

(2013/08/14 5:24), Josh Berkus wrote:
> On 08/13/2013 11:18 AM, Tom Lane wrote:
>> Hannu Krosing <hannu(at)2ndQuadrant(dot)com> writes:
>>> 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.
>>
>> Unless you'd explicitly granted those users insert/update/delete
privilege
>> on the view, they wouldn't suddenly be able to do something new in 9.3,
>> because no such privileges are granted by default. If you had granted
>> such privileges, you don't have much of a leg to stand on for
complaining
>> that now they can do it.
>
> Ah, ok. I hadn't gotten to the testing phase yet.
>
> I think we should have a script available for revoking all write privs
> on all views and link it from somewhere (the release notes?), but I
> don't see any need to change anything in the release.
>
Yes, I was not thinking about changing current 9.3 behavior.
So I think it's enough to know the impact and how to avoid that
on the release notes.

thanks a lot!

regards,
-------------------
NTT Software Corporation
Tomonari Katsumata