Crunchy Blog

PostgreSQL, security

A PostgreSQL Row Level Security Primer + Creating Large Policies

February 12, 2018

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.

PostgreSQL, pgAdmin4, Docker, Containers

Easy PostgreSQL 10 and pgAdmin 4 Setup with Docker

February 05, 2018

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!

PostgreSQL, bloat, index, pgstattuple

Checking for PostgreSQL Bloat

September 25, 2017

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.

Analytics, PL/R, postgis

Using R Analytic Functions in PostGIS

April 25, 2017

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.

PostgreSQL, Analytics, PL/R, postgis

Preprocessing Data for Spatial Analysis with PostGIS and PL/R

April 18, 2017

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. 

PostgreSQL, Analytics, PL/R

Spatial Analytics with PostGIS, PL/R and R

April 11, 2017

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, Kubernetes, Docker, containers

PostgreSQL Operator for Kubernetes

March 28, 2017

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.

PostgreSQL

Announcing the PostgreSQL STIG

March 27, 2017

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.

Kubernetes

Deploying PostgreSQL Clusters using Kubernetes StatefulSets

February 26, 2017

This blog provides guidance on how to build a PostgreSQL cluster using the new Kubernetes feature - StatefulSet.  Using this StatefulSet capability provides a very simple, Kubernetes native, mechanism to make clustering decisions when deploying a PostgreSQL cluster.

Docker, containers

Easy PostgreSQL Cluster Recipe Using Docker 1.12 and Swarm

December 22, 2016

In this blog I’ll show you how to deploy a PostgreSQL cluster using the latest Docker 1.12 technology. Updates to Docker in their 1.12 release greatly simplify deploying a PostgreSQL cluster. IP addresses and hostnames used in this blog are just examples and not mandated.