I get this error my when i'm selecting from an mview i created.
select * from mview_age_stats
This mview definition is using an external schema in its definition with the external schema "ext". I tried everything i could find online and gave permission at schema, table and every other level
ALTER DEFAULT PRIVILEGES IN SCHEMA ext GRANT SELECT ON TABLES TO my_user;
GRANT USAGE ON SCHEMA ext to my_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ext TO my_user;
GRANT SELECT ON ALL TABLES IN SCHEMA ext TO my_user;
my_user is also in couple different groups, i gave the same permissions to each group and also more like this;
GRANT SELECT ON TABLE mview_age_stats TO GROUP read_only_group; GRANT SELECT ON TABLE mview_age_stats TO GROUP read_write_only;
None of these worked, however what I noticed is that is i have a statement in my mview definition for transferring the ownership to a superuser - which my company uses in order keep ownership of all tables. If i remove the ownership it magically works but i don't understand how moving the ownership would make a difference since i'm granting permission to my_user anyway
alter table mview_age_stats
owner to main_user;