search

sraoss / pg_ivm

IVM (Incremental View Maintenance) implementation as a PostgreSQL extension
Other
862 stars 24 forks source link

Additional GRANT is required #25

Closed ivan closed 1 year ago

ivan commented 1 year ago

Thank you very much for publishing this, as I was unable to use Materialize while pg_ivm worked fine (after reducing my transaction isolation level).

I noticed that additional GRANTs are required to delete any table when using pg_ivm (even unrelated tables, without a materialized view), but this is not mentioned in the README:

> DROP TABLE temp_captures_made_within_1_2_days_of_upload;
ERROR:  permission denied for table pg_ivm_immv
CONTEXT:  SQL statement "DELETE FROM pg_catalog.pg_ivm_immv
        USING pg_catalog.pg_event_trigger_dropped_objects() AS events
        WHERE immvrelid = events.objid AND
              events.classid = pg_class_oid AND events.objsubid = 0"
PL/pgSQL function pg_ivm_sql_drop_trigger_func() line 10 at SQL statement

This seems to fix that, but it was not obvious to me:

GRANT ALL ON TABLE pg_catalog.pg_ivm_immv TO archive;

If this is not a bug, maybe the GRANT should be mentioned in the README?

I was using PostgreSQL 14.5 and pg_ivm 1.2 on NixOS.

yugo-n commented 1 year ago

Thank you for reporting it!

Well, it would be a bug because we should not grant all privilege of pg_ivm_immv to public.