View owner not tracked

[ruslantalpa]

@djrobstep thank you for migra

Although this might be a more general issue (relations owners not being tracked), i noticed this when diffing two views that had a specific owner set. The resulting DDL is of a drop view/create view however the "correct" owner is not restored.

For an idea of why this might be useful (specific view owner), when working with RLS and Views, in order for an RLS policy to kick in when going through a view, the view must be owned by a non super user role. This is useful in setups with PostgREST and PostGraphile.

Thank you

[tysonclugg]

@ruslantalpa Just checking, did you use the --with-privileges argument? I understand you're talking about ownership which is different to privileges, but I thought it prudent to check.

[ruslantalpa]

@tysonclugg sorry for late reply, i did not use --with-privileges since that is about something else. I ended up using migra without privileges and having those in a separate file that "resets" everything after each transaction.

[karolzlot]

@ruslantalpa Alternatively you may take a look at pgAdmin Schema Diff described here

(Although from my experience Migra gives usually better result)

[benjamin-kirkbride]

So does --with-privileges fix this ?

[bmillwood]

In my experience no, migra-3.0.1658662267 is not picking up on ownership changes, even when I use --with-privileges.

[loekj]

Seems that this is a pretty deciding factor between pgAdmin Schema Diff and Migra, is this scheduled for an upcoming release?

[karolzlot]

@loekj Yes, that's true, although pgAdmin schema diff has its own issues: https://github.com/djrobstep/migra/issues/189#issuecomment-947850713

I hope the decision is to improve Migra, not replace its engine with subpar engine from pgAdmin.

[KrisBraun]

I am seeing two critical (from a security perspective) issues where migra misses view privileges (even with the --with-privileges flag):

1. Views with WITH ( security_invoker = TRUE) generate migrations without this flag. This makes it too easy to generate a migration to update a view that ends up dropping and recreating the view with open access.
2. GRANT and REVOKE on views are not generated.