Blog | Data Data

Demystifying Schemas & search_path through Examples

March 02, 2018

PostgreSQL security

On March 1, 2018, the PostgreSQL community released version 10.3 and other supported versions of PostgreSQL.  The release centered around a disclosed security vulnerability designated CVE-2018-1058, which is related to how a user can accidentally or maliciously "create like-named objects in different schemas that can change the behavior of other users' queries."

The PostgreSQL community released a guide around what exactly CVE-2018-1058 is and how to protect your databases. However, we thought it would also be helpful to look into what schemas are in PostgreSQL, how they are used under normal operations, and how to investigate your schemas to look for and eliminate suspicious functions.

Range Types & Recursion: How to Search Availability with PostgreSQL

February 22, 2018

PostgreSQL

One of the many reasons that PostgreSQL is fun to develop with is its robust collection of data types, such as the range type. Range types were introduced in PostgreSQL 9.2 with out-of-the-box support for numeric (integers, numerics) and temporal ranges (dates, timestamps), with infrastructure in place to create ranges of other data types (e.g. inet/cidr type ranges). Range data is found in many applications, from science to finance, and being able to efficiently compare ranges in PostgreSQL can take the onus off of applications workloads.

A PostgreSQL Row Level Security Primer + Creating Large Policies

February 12, 2018

PostgreSQL security

Row Level Security, aka "RLS," allows a database administrator to define if a user should be able to view or manipulate specific rows of data within a table according to a policy. Introduced in PostgreSQL 9.5, row level security added another layer of security for PostgreSQL users who have additional security and compliance considerations for their applications.

At Crunchy Data, we care a lot about data security and supporting PostgreSQL. When we discovered an issue with creating a large row level security policy, we worked to find a workaround that provided a secure outcome but avoided creating a custom fork of PostgreSQL.  As of the publication of this post, the Crunchy Data team is working to address the issue with a patch that will be submitted for inclusion upstream.

To understand the issue and how our team found a solution, I'll first give a quick primer of how row level security works in PostgreSQL.

Easy PostgreSQL 10 and pgAdmin 4 Setup with Docker

February 05, 2018

PostgreSQL pgAdmin4 Docker Containers

The open source software ecosystem around PostgreSQL is as robust as the database itself, but sometimes it can be hard for people new to PostgreSQL, and even some seasoned veterans, to get all of the software dependencies for their development environment setup on their computers.

At Crunchy Data, we obsess  on making things as easy as possible for people to get software up and running, regardless of the technology. While I was trying to find things to experiment with using Docker, one of our engineers gave me a recipe to setup PostgreSQL 10 and pgAdmin 4 easily and quickly.  It was so simple, I thought it would be fun to share it!

Checking for PostgreSQL Bloat

September 25, 2017

PostgreSQL bloat index pgstattuple

Crunchy Data is pleased to announce the welcome addition of Keith Fiske to our team.  Keith is a well known expert on the topic of bloat in PostgreSQL, and Crunchy Data felt there was no better way to announce his arrival that to share a post from Keith on the subject.

Using R Analytic Functions in PostGIS

April 25, 2017

postgis PL/R Analytics

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 postgis PL/R Analytics

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.

PostgreSQL Operator for Kubernetes

Crunchy Data is pleased to announce an initial implemention of a PostgreSQL Operator for Kubernetes to build on our work with PostgreSQL Containers.  This initial implementation provides a user with the ability to perform certain PostgreSQL functions including creating PostgreSQL clusters, performing database backup and restores and viewing persistent volume claims.

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.