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
