Permissions on pg_trigger

[sergiojgm]

If a table is owned by a different user than Postgres is giving errors on relation pg_trigger when cresting index:

CONTEXT: SQL statement "CREATE TRIGGER zzzzdb_tuple_sync_for_27033053_using_27033068 BEFORE UPDATE ON "public"."art2" FOR EACH ROW EXECUTE PROCEDURE zdbupdatetrigger();UPDATE pg_trigger SET tgisinternal = true WHERE tgname = 'zzzzdb_tuple_sync_for_27033053_using_27033068';SELECT oid FROM pg_trigger WHERE tgname = 'zzzzdb_tuple_sync_for_27033053_using_27033068'"

problem seams to be on:
UPDATE pg_trigger SET tgisinternal = true WHERE tgname = 'zzzzdb_tuple_sync_for_27033053_using_27033068';

UPDATE pg_trigger SET tgisinternal = true WHERE tgname = 'zzzzdb_tuple_sync_for_27033053_using_27033072'
ERROR: permission denied for relation pg_trigger

altering permissions to pg_trigger raises that its a system catalog cannot be changed...

[eeeebbbbrrrr]

This one is interesting. Not exactly sure what I can do about this. ZDB's create index code runs as the user executing it, but it does need to touch pg_trigger to mark the trigger as internal (this is important for pg_dump/pg_restore reasons).

You can try creating the indexes with a user that has superuser privileges.

In the mean time I'll think on this a little bit.

[sergiojgm]

I try it. It always take the owner of the table not the current user.

Means that index owner must be changed and back again.

I don't know the impact on reindex

[eeeebbbbrrrr]

You've probably been through this part, but you'll need to grant select permissions for a few of ZDB's tables:

GRANT SELECT ON zdb_analyzers TO username;
GRANT SELECT ON zdb_char_filters TO username;
GRANT SELECT ON zdb_filters TO username;
GRANT SELECT ON zdb_mappings TO username;
GRANT SELECT ON zdb_tokenizers TO username;

where username is the user that owns the table and is issuing the CREATE INDEX statement.

I might be able to define zombodb's zdbbuild() function with SECURITY DEFINER. Assuming that would even work (I'll try in a minute) that would mean you'd have to CREATE EXTENSION as a superuser.

[sergiojgm]

Yes all zdb are as user of the table and the create index...

[eeeebbbbrrrr]

yeah, using SECURITY DEFINER seems to work, so I think you can just do this (as a superuser) after you've created the extension:

ALTER FUNCTION zdbbuild(internal, internal, internal) SECURITY DEFINER;

I'll think on if this is a good thing to do for ZDB in general... I think it is since it absolutely needs to touch pg_trigger.

[eeeebbbbrrrr]

I've re-written how ZomboDB creates the table triggers so that SUPERUSER privileges aren't necessary anymore. The TL;DR is rather than using SPI to UPDATE pg_trigger SET tgisinternal = true ... I create the trigger directly in C using Postgres' internal CreateTrigger() function.

I'm also granting all privileges to the various mapping-related tables so that any user can create customized mappings for the indexes they're creating.

I'm also going to grant SELECT privileges to the various zdb_index_stats tables, so all users can see index stats.

In total, this looks like:

GRANT ALL ON zdb_analyzers TO PUBLIC;
GRANT ALL ON zdb_char_filters TO PUBLIC;
GRANT ALL ON zdb_filters TO PUBLIC;
GRANT ALL ON zdb_mappings TO PUBLIC;
GRANT ALL ON zdb_tokenizers TO PUBLIC;
GRANT ALL ON zdb_normalizers TO PUBLIC;

GRANT SELECT ON zdb_index_stats TO PUBLIC;
GRANT SELECT ON zdb_index_stats_fast TO PUBLIC;

[eeeebbbbrrrr]

released