Re: How to create read-only view on 9.3

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