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?
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).
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 :/
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:
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!
Which {SQL,} databases do this as a native, online database feature; so you don't have to pull backups to sqldiff?
E.g. django-reversion and VDM do versioned domain model on top of SQL, but not with native temporal database features.
Many apps probably could or should be written as mostly-append-only - not necessarily blocking until the previous record is available to hash – but very few apps are written that way, so run sqldiff offline.
There is Dolt, which started out as git for tables but is turning into a MySQL compatible database with good versioning. (Including branches and merges.)
We actually built a tool exactly for that: https://www.datafold.com/data-diff
The core use case is helping data engineers building analytical pipelines test the changes to their code prior to deploying, but can also work for any query. You can diff tables or just SQL queries directly.
I don’t get the XKCD reference either. It’s the opposite. There are many ways to diff database schemas but, as I understand the doc, this utility actually compares the data stored in a database with the data stored in another database having the same schema. I’m not aware of any other “built-in” tools for comparing the content of databases.
