Viewing data at the intersection between roles
Presented by:
Lloyd Albin
I have been a PostgreSQL administrator since 2002 and have worked for Fred Hutchinson Cancer Research Center since 2008. Within Fred Hutch, I work for the largest group called SCHARP, Statistical Center for HIV/AIDS Research and Prevention.
In 2010 I started SeaPUG, Seattle Postgres Users Group, at the request of Josh Drake. I present at least half the presentations there every year. I started the PostgreSQL track at LinuxFest Northwest in 2014 after my GIS presentation in 2013 was standing room only. In 2017 I got a booth at the SeaGL, Seattle GNU Linux, conference with the idea of having a booth there in 2018 along with also doing a PostgreSQL presentation at the conference. In 2017 I started presenting at various PostgresConf Conferences.
I have also discovered several PostgreSQL bugs which have been fixed, some of them affected every version of PostgreSQL.
Bug numbers:
- 7553 - Variant of the what alias to use after a rename bug in views - Fixed in 9.3
- 8173 - Inserting heap tuples in bulk in COPY patch return wrong line on failure 999 out of 1000 times. - Fixed in 9.2.5 & 9.3
- 8257 - Multi-Core Restore fails when containing index comments - Fixed in 8.4, 9.0, 9.1, 9.2, 9.3
- 8291 - postgres_fdw does not re-read USER MAPING after change - Updated Docs in 9.3
- 8545 - pg_dump does not backup database level globals - Fixed in 11
- 14147 - Restoration of Materialized View of Foreign Data Tables fails - Fixed in 10
- 15182 - Major Bug - Affecting all versions of PostgreSQL. Partially fixed in 11 and fully fixed in 12
- 15316 - Creation of check constraint functions that rely on data loaded alphabetically after your primary data will fail to restore properly.
I host the Seattle Postgres Users Group where I give many of the presentations. I have many of the presentations posted at Lloyd's Presentations
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. Most people would create a third group and make the two groups, members of this new group. The extra group was not wanted for various reasons but PostgreSQL by default does not support tying the permissions together for more than one role/group.
In our use case, we want the user to SELECT on a table and return an error if the user had no rights or partial rights, aka does not belong to all of the required groups, instead of an empty table. This is because, if you have a table containing adverse events, it could 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.
I worked up several ways to be able to perform this action, but as always, there are pro's and con's to each method. In this presentation we will go over each method along with how the method affects the query performance.
- Date:
- 2018 October 16 11:10 PDT
- Duration:
- 50 min
- Room:
- Market
- Conference:
- Silicon Valley
- Language:
- Track:
- Dev
- Difficulty:
- Medium