[I think the root cause of this problem is me not understanding permissions and privileges...]
So, to set the stage, the set up I have is a DB, call it MyDb.
I have two users, spu1 and u1, spu1 is a superuser and u1 a 'regular' user. The owner of MyDb is spu1. I think I should also say that u1 has create databases & create role privileges as inherited from a group role.
I have a schema sch1, which is a user defined schema.
Within this schema I have a table, call it tbl1, and a materialised view, call it mvw1.
The tbl1's owner is the spu1, the mvw1's owner is u1.
In the current set up, as described above, I cannot refresh mvw1 as either u1 or spu1. I simply get the below fun error (which I have googled extensively but not found anything which quite solves for my setup..).
ERROR: permission denied for relation tbl1 ********** Error ********** ERROR: permission denied for relation tbl1 SQL state: 42501
I have found out that
- Changing the owner of mvw1 to the spu1, allows me to refresh as spu1.
- Running the below allows me to refresh mvw1 as u1.
I'm trying to find out what the missing permission(s) (ideally the minimum required) I need to grant the regular user, u1, so that I can refresh this view when logged in as them.
The first option, whilst nice to know, doesn't solve my problem. The second option seems like I'm in effect granting superuser permissions to a non-superuser, or rather granting greater privileges than I need to.
If anyone can explain to me what exactly is going on here (or point out what info I've missed out in the description of my problem needed to solve it), and let me know if my second option is in fact the way to go or of a better alternative?