Thread: 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.tomonari@po.ntts.co.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.tomonari@po.ntts.co.jp> wrote:>>>Hi,>>>> Could anyone tell me how to create read-only view on>> PostgreSQL 9.3 ?>>>> I've been testing updatableviews 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 makethese views read-only like PostgreSQL9.2.>> How can I do this? Should I make access control on users ?>> (Sorry, I couldn'tfind 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.tomonari@po.ntts.co.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.tomonari@po.ntts.co.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@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. 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@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. 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@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Ü
* Hannu Krosing (hannu@2ndQuadrant.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@2ndQuadrant.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@sss.pgh.pa.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.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@2ndQuadrant.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@2ndQuadrant.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 grantedby 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 scriptavailable for revoking all write privs> on all views and link it from somewhere (the release notes?), but I> don'tsee 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