Viewing data at the intersection between roles

We ran across a use case where we needed to restrict people's access to the data by requiring them to belong to 2 or more groups. PostgreSQL by default does not support this. I have worked up several way to be able to perform this action, but as always, there are pro's and con's to each method. In our use case, we wanted the user to SELECT on a table and return an error if the user did not have the correct rights instead of an empty table. Ideally, a user without the correct rights would see an error even if there was no data in the table. This is because, if you have a table containing adverse events, it could jeopardize human safety if users thought there were no adverse events, when, in fact, it's just that they didn't have permission to see the adverse events.

This is a presentation of the blog post that I previously wrote.

The ZIP file contains all the SQL used in the presentation.

The PowerPoint presentation contains notes and links to docs that are not contained in the PDF version.