Blog | Crunchy Data

Joe Conway

Recent Posts

PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 2

June 27, 2019

PostgreSQL security

In Part 1 of this blog, we covered quite a bit of information with respect to how a PostgreSQL database is initially configured by default from the perspective of discretionary access control. We also saw how to inspect those default behaviors using the crunchy_check_access extension. In Part 2, we will explore the situation identified in CVE-2018-1058 and discuss how to protect yourself.

CVE-2018-1058: Explained and Exploited

Finally we have come to the Pièce De Résistance!

CVE-2018-1058 describes how a user can create objects, named the same as objects in different schemas which can change the behavior of other users' queries, potentially causing unexpected or malicious behavior. This is also known as a trojan-horse attack. In order to fully understand how this situation may arise, we will first review some fundamental PostgreSQL concepts.

PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 1

June 25, 2019

PostgreSQL security

Recently I gave a "deep dive" talk on the topic of PostgreSQL security, and I wanted to capture one part of that content into a blog since this format is both better for making that content stand on its own and for expanding on it a bit.

Specifically, in this two-part blog, we will cover a PostgreSQL extension that I wrote called crunchy_check_access -- the motivation behind it and what it does -- and then use that extension to probe and understand the consequences of the default, out of the box, security of PostgreSQL. We will also examine the implications of choices we make when setting up "users" and "groups". To cap it off, we will explore the situation identified in CVE-2018-1058 and discuss how to protect yourself.

Warning: this two-part blog is a wall of text (and SQL). It would probably be more suited as a chapter in a long book. I encourage you to stick with it though, as I believe even those with a great deal of PostgreSQL experience will find the content very enlightening. At a minimum it will probably remind you of things you once learned but have since forgotten.

PostgreSQL Deep Dive: How Your Data Model Affects Storage

February 13, 2019

PostgreSQL

I want to take a few minutes for a deep dive into the effect your data model has on storage density when using PostgreSQL. When this topic came up with a customer, I explained my thoughts on the matter, but I realized at the time that I had never done a reasonably careful apples-to-apples test to see just exactly what the effect is, at least for a model sample size of one. So here it is.

Using R Analytic Functions in PostGIS

April 25, 2017

Analytics PL/R postgis

This is the third and final post of the series intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language. The information below provides sample use of R Functions against the NDVI dataset.

Preprocessing Data for Spatial Analysis with PostGIS and PL/R

April 18, 2017

PostgreSQL Analytics PL/R postgis

This is the second in a series of posts intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language.  This post builds on the example introduced in the initial post by demonstrating the steps associated with preprocessing the Normalized Difference Vegetation Index (NDVI) satellite raster data in preparation for spatial analytics. 

Spatial Analytics with PostGIS, PL/R and R

April 11, 2017

PostgreSQL Analytics PL/R

This is the first in a series of posts intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language.  When further combined with PostGIS, the geospatial extender for PostgreSQL, users can perform powerful spatial analytics within the PostgreSQL database.  This initial post introduces PL/R and R, provides set up instructions for following the Spatial Analytics example to be used in this series of posts, and provides introductory instruction on Geocoding with PostGIS, R and PL/R.

Announcing the PostgreSQL STIG

March 27, 2017

PostgreSQL

Crunchy Data recently announced the publication of the PostgreSQL Security Technical Implementation Guide (STIG) by the United States Defense Information Systems Agency (DISA), making PostgreSQL the first open source database to provide a published STIG.