Blog

PostgresConf Silicon Valley

I will be presenting on Viewing data at the intersection between roles at the PostgresConf Silicon Valley which is being held October 15 - 16, 2018 at the Hilton San Jose. The full schedule of the conference is available online.

How to restore a database with check constraints

I have found an edge case where pg_dump and pg_restore fails to restore databases with check constraints that points to data in another table that is restored after the table containing the check constraint. This has been tested and found to be a problem on PostgreSQL 9.5.2, 10.3 and 10.4.

In this how to, we will go over the following information:

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.

Canceling authentication due to timeout aka Denial of Service Attack

UPDATED 07/30/2018

After the PostgreSQL security team looked at this issue, it was released to the Postgresql Hackers List on 7/19/2018. I would like to thank Michael Paquier who has since been writing patches to fix these issues. Michael has written back patches for all current versions of Postgresql, this means back to Postgresql 9.3.

Link to the Bug Report and Hackers followup:

Subscribe to Blog