3 ms·

I was debugging Postgres permissions the other day and was hoping for a tool where you can just diff the permissions of a table between databases and see why in

by dewey 5y ago

I was debugging Postgres permissions the other day and was hoping for a tool where you can just diff the permissions of a table between databases and see why in one database a certain user has permissions (to insert, execute,...) and in the other one it doesn't. That might be a naive ask and maybe that's not even possible but I'm wondering if such a thing exists?

gregplaysguitar 5y ago
Check out migra[1] - it covers most of that. One notable omission is column-level grants though - as far as I know changes to those aren’t detected [1] https://github.com/djrobstep/migra https://github.com/djrobstep/migra
- dewey 5y ago
  This looks interesting, thanks for sharing! I'll give it a try. Do you by any chance know if it detects different search paths too between roles? (That was my actual issue in the end while debugging why role A could execute a function and role B couldn't).
- e12e 5y ago
  Thanks for mentioning migra. Unfortunately it looks like it's only for identical databases - ie using public schema - so it can't diff dba.user1_schema.table1 vs dbb.user2_schema.table1? I don't have any production DBs that use public/not-instance/user-specific schemas :/
  - michelpp 5y ago
    I am not very familiar with this tool, and the documentation doesn't mention this use case but it does look like you can pass specific schema(s) or exclude them: https://github.com/djrobstep/migra/blob/master/migra/migra.py#L15 https://github.com/djrobstep/migra/blob/master/migra/migra.p... It looks like the schema do have to have the same name between the databases, which I think is sensible but again I could be wrong. This is a darker corner of the postgres tooling world that could definitely use some light, so good on the migra devs to trying this out. If you do get it working, consider sending them a PR with some documentation on your use case!

by dewey 5y ago

gregplaysguitar 5y ago

Check out migra[1] - it covers most of that. One notable omission is column-level grants though - as far as I know changes to those aren’t detected [1] https://github.com/djrobstep/migra https://github.com/djrobstep/migra

dewey 5y ago
This looks interesting, thanks for sharing! I'll give it a try. Do you by any chance know if it detects different search paths too between roles? (That was my actual issue in the end while debugging why role A could execute a function and role B couldn't).
e12e 5y ago
Thanks for mentioning migra. Unfortunately it looks like it's only for identical databases - ie using public schema - so it can't diff dba.user1_schema.table1 vs dbb.user2_schema.table1? I don't have any production DBs that use public/not-instance/user-specific schemas :/
- michelpp 5y ago
  I am not very familiar with this tool, and the documentation doesn't mention this use case but it does look like you can pass specific schema(s) or exclude them: https://github.com/djrobstep/migra/blob/master/migra/migra.py#L15 https://github.com/djrobstep/migra/blob/master/migra/migra.p... It looks like the schema do have to have the same name between the databases, which I think is sensible but again I could be wrong. This is a darker corner of the postgres tooling world that could definitely use some light, so good on the migra devs to trying this out. If you do get it working, consider sending them a PR with some documentation on your use case!