Half the tests passed, the rest failed with cryptic foreign key errors. Nothing in the application code had changed. The problem lived in the database—hidden in roles and permissions no one had touched in months.
Integration testing with database roles is the line between trust and guesswork. Applications fail when roles are misconfigured, privileges are missing, or permissions behave differently in production. Code tests alone can’t catch that. You need to test against the actual roles your app will use when deployed.
Start with the fundamentals. Create database roles that match each real-world user type: application service accounts, read-only reporting accounts, admin roles. Tie each role to the exact permissions it will get in production. Avoid superuser privileges in testing—this masks access problems you’ll encounter later.
Automate role creation and teardown in your integration test pipeline. Migrations should apply schema changes, seed the data, and create roles as part of a repeatable setup. Test flows with the same credentials the application will use. This simulates the real access path and forces authentication and authorization to be part of the test feedback loop.