Archive for August 2019

Cybertec Logo Nameonly 01

 A database such as PostgreSQL is not just there to store data – it is also a tool to protect data. Your data must not be lost and it must not be seen by people who are unauthorized or hostile. The main goal is therefore to protect data at any cost and ensure that nothing is ever lost, leaked or compromised. As we have seen in the past more often than not a leak can easily ruin the reputation of a company or even lead to its destruction. This is true in all sectors including but not limited to finance, medical services, IT, and so on.

 

Protecting data at various levels

If you are using PostgreSQL you can protect data at various levels. The goal is to develop a comprehensive security concepts which protects from all kinds of attacks. The following aspects have to be taken into account:

  • Network security

  • Transport encryption (SSL, etc.)

  • Database level permissions

  • Data masking and obfuscation

  • Data-At-Rest Encryption (PostgreSQL TDE)

The following overview shows how to implement a sound policy at every level.

 

Ensuring network security

The first line of defense is always the network. The golden rule is: Only listen on network connections you really need and which offer a small attack surface. Fortunately, PostgreSQL has all the means to ensure security at this level.

The first thing to do is to configure the “listen_addresses” parameter in postgresql.conf. It tells PostgreSQL which bind addresses you want to use. The rule is: If you don’t have to listen on certain IPs don’t do it. To ensure security only use bind addresses which are really in use.

The second line of defense is pg_hba.conf. This config file will tell PostgreSQL how to authenticate which network segment. pg_hba.conf will be familiar to most readers so I will skip the details in this post.

However, let us assume that an attacker somehow manages to reach your database and launch a brute force attack to figure out your passwords. One way to defend against such an attack is to use the “auth_delay” extension which is part of the PostgreSQL contrib package. What is the general idea behind this extension? If an attacker launches a brute force attack auth_delay will wait some time before returning an error. This simple method will already greatly reduce your risk. Here is how it works:

# postgresql.conf

shared_preload_libraries = 'auth_delay'

auth_delay.milliseconds = '500'

Just add those lines to postgresql.conf and the module will take care of the rest.

 

Implementing transport encryption (SSL, etc.)

Once we have secured bind addresses and reduced the risk of a brute force attack it is important to protect your lines of communication. The way to do that is to use SSL. PostgreSQL supports various levels of SSL. The following levels are supported:

  • disable: I don't care about security, and I don't want to pay the overhead of encryption

  • allow: I don't care about security, but I will pay the overhead of encryption if the server insists on it.

  • prefer: I don't care about encryption, but I wish to pay the overhead of encryption if the server supports it.

  • require: I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want.

  • verify-ca: I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust.

  • verify-full: I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it's the one I specify.

Depending on your performance and security requirements you can decide which level is best for you. Performance-wise SSL encryption does not come for free but if your security requirements are high it is worth paying the price.

 

Configuring database level permissions

Once you have taken care of network security, pg_hba.conf, authentication, as well as transport encryption it is time to take a look at what you can actually do inside the database.

The following layers of security are important:

  • schema: Make sure that only trusted people can access a schema

  • table: Ensure that only relevant people have access to a specific table

  • column: Restrict access to columns for specific users (to protect credit card data and alike)

  • row-level-security (RLS): Remove rows from the scope of a user

RLS (row-level-security) is especially important and promising because it allows people to only access specific rows in a table which greatly increases your ability to protect data in a very fain grained way. The important thing to keep in mind is: RLS is powerful but it requires proper testing. Some stuff is quite tricky and requires a fair amount of expertise as shown in my blog post about the topic (https://www.cybertec-postgresql.com/en/postgresql-row-level-security-views-and-a-lot-of-magic/). If you need assistance with RLS feel free to get in touch with us. We are pleased to help.

 

Data masking and obfuscation

Studies have shown that many attacks come from within. Consider the following scenario: You are running a large online shop. Your production database is secure and nothing happens. However, your development team has to test new stuff and needs data to ensure high quality standards. What are you going to do? Do you really want to give all your developers a full copy of all your data? The trouble is: If there is no proper test data your applications will be buggy – if you hand over all your data to developers you might face issues on the legal side of you are running the risk of giving data to people you cannot fully trust under all circumstances.

The solution to the problem is Cybertec Data Masking (https://www.cybertec-postgresql.com/en/products/data-masking-for-postgresql/). Our product will allow you to define an obfuscation model and give developers access to an obfuscated dump which can be used safely. The advantages are that the data given to developers has the same properties as the live data but does not contain personal or business critical data which should not be seen by ordinary developers.

Cybertec Data Masking provides some addons and extensions to PostgreSQL and helps you to obfuscate data in the most simple and elegant way possible. Get in touch with our sales team to find out more.

 

Enabling Data-At-Rest Encryption (PostgreSQL TDE)

Once you have secured your database using the steps outlined above you might still be faced with additional risks. What if your disks are compromised? PostgreSQL TDE (“Transparent Data Encryption”) will be the solution for you.

PostgreSQL TDE is a PostgreSQL distribution by Cybertec which automatically encrypts data on disk. All data files are safely encrypted and unless you know the key there is no way to launch your server. PostgreSQL TDE is especially useful if you are dealing with medical records, customer and financial data, which requires even more protection and security. PostgreSQL TDE is the ultimate solution to most high end security demands.

How does it work?

PostgreSQL will cypher every block as it is written to disk and decrypt data as it is read from your storage devices. Using cutting edge hardware acceleration TDE ensures superior performance and total transparency. PostgreSQL TDE can integrate into professional key stores and there is no need to store the key on the same server as the data.

 

If you are looking for PostrgreSQL TDE for PostgreSQL 11 or maybe even PostgreSQL 12 get in touch with our team here at Cybertec to find out more and to learn about this wonderful product:
https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/

(Author: Hans-Juergen Schoenig)

 

Hans-Jürgen Schönig     August 28, 2019

Timescale Sponsor Highlight Blog for PostgresConf Silicon Valley 2019

 As part of the countdown to PostgresConf Silicon Valley, learn more about featured Partner Sponsor Timescale, including their commitment to partnering with and contributing to the Postgres community.

 

Tell us about your commitment to the PostgreSQL Community.

As some background, TimescaleDB is built on top of PostgreSQL. By using PostgreSQL as a foundation, we’re able to give users scalable SQL for time-series data. 

We’re committed to increasing the usability of PostgreSQL and its compatibility with other products in the data ecosystem, which is why we’ve created software that helps others leverage the power of PostgreSQL. In addition to TimescaleDB, we’ve created many open source tools, such as pg_prometheus, a PostgreSQL extension for data from the popular monitoring system Prometheus; the prometheus-postgresql-adapter, which allows users to use PostgreSQL as a long term store for time-series metrics from Prometheus; the TimescaleDB/Postgres editor for Grafana, which is a visual query editor for the PostgreSQL datasource in the visualization tool Grafana; and a PostgreSQL output plugin for Telegraf, a machine monitoring tool.

Understanding how the community is using Postgres helps bring more awareness to the community's diversity and needs, which is why we recently created the State of Postgres survey. If you’re a Postgres user, please contribute to the community and take the survey!

 

What is the best thing about working with the Postgres community?

We believe that the Postgres community is rapidly growing. Everyone’s eager to get involved, willing to listen and learn, and most importantly, willing to collaborate. 

We’ve seen countless examples in the Timescale Community Slack of users jumping in to help each other solve problems or to offer interesting points of view.

The community has also proved to be a great source of talent for Timescale, as we’ve hired many engineers who were PostgreSQL users and active community members. If you’re interested in working at Timescale, see our careers page!

 

Why is Postgres an ideal foundation to build a database?

Postgres was the ideal foundation for us to build TimescaleDB because it’s open source, SQL based, and has an active community. However, the main advantage was the fact that we could leverage its 30 years of reliability and stability that was already in existence. Additionally, Postgres has a robust ecosystem and many tools/extensions available.  For these reasons, Postgres allowed us to stand on the shoulders of giants, despite being a relatively young company.

 

Tell us why you believe people should attend PostgresConf Silicon Valley 2019 in San Jose.

PostgresConf SV is a great place to learn from people using Postgres in production! You get to meet members of the community face to face and form relationships that can lead to future collaboration. 

Timescale is also giving two great talks about our work with Postgres which we encourage you to come and check out: Advanced Compression in TimescaleDB with Hybrid Row/Culmnar Storage  (Thursday, September 19 from 4:20pm - 5:10pm) and Creating Continually Up to Date Materialized Aggregates  (Thursday, September 19 from 12pm - 12:50pm).

See you at PostgresConf Silicon Valley 2019!

Amanda Nystrom     August 27, 2019

 

It is late August, 2019. This is the time where we are usually prepping for the very busy fall season and not much else. However, this is the Year of Postgres and everyone is driving 200MPH down the ecosystem highway (321.8688/KPH). We are going to kick off this newsletter with some exciting information about the community.

Events

PostgresConf has launched Digital Events! The goal of Digital Events is to open our education platform year round to all members of the community. Our first series of events will be held with our ecosystem partner YugabyteDB and their “Distributed SQL Webinar Series.” This is a series of free-to-attend Webinars exploring Distributed SQL from leaders in the field.

 

PostgreConf Silicon Valley tickets are going at a brisk pace and half day trainings are almost sold out. Register today to reserve your seat before prices go up on September 1st!

 

Right after Silicon Valley, PostgresConf South Africa is kicking off. This conference has grown by leaps and bounds over the last two years. We highly recommend attending for anyone who can!

 

PGConf.IN (India) has announced that their conference will be held in February 2020!

Meetups

We have seen the launch of three new meetups this month:

  • Los Angeles Postgres The first meetup is planned for late October or early November as we continue to build the Silicon Beach community.
  • Toronto Postgres Similar to Los Angeles, the first meetup is planned for late October or early November.
  • Charm City Postgres This meetup was formed by long time community member Robert Treat.

 

Several other meetups are growing quickly: 

 

Interested in speaking or hosting a meetup? Contact us and we’ll connect you with the right people! 

Learn

Here is a short, great introduction tutorial on running PostgreSQL in Docker by Igal Sapir, Los Angeles Postgres organizer. Everybody has 13 minutes.

 

Shawn Wang from our friends at High Go has provided an insightful write-up on AES Performance.

Ecosystem

TimescaleDB is running a “State of Postgres” survey. Please take five minutes and help them out! They have also announced a new Distributed Timeseries product.

 

VMWare has just acquired Greenplum and PostgreSQL supporting company Pivotal.

Postgresql.org

PostgreSQL versions 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 are now out in the wild and addressing several important security concerns and bug fixes.

 

---

 

Have news you’d like included in future newsletters? Contact us.

Joshua D. Drake     August 23, 2019

Congratulations

Henrietta Dombrovskaya, Diversity, Equity, and Inclusion member for PostgresConf as well as Chicago Postgresql organizer nominated for Technologist of the Year!

 

Career Opportunity

A large, well known media company is seeking a Senior Level PostgreSQL Engineer and Architect. This is an on-site engagement, however the company is known to be lifestyle friendly with reasonable working hours, good pay, and benefits. Specific talents requested are the ability to mentor. The location is Seattle, WA. If you are interested in this position please contact randy@neuringerco.com with your resume.

Great content

Extension Highlight

We wanted to highlight some of the fantastic work that is being done by the ecosystem with Postgres Extensions. Although the base of Postgres is the amazing and extensible PostgreSQL, a lot of users don’t realize that Postgres has the feature they are looking for, if only they were to look to the ecosystem.

Notable Extensions:

  • pgaudit : The goal of pgAudit is to provide PostgreSQL users with the capability to produce audit logs often required to comply with government, financial, or ISO certifications.

  • pg_credereum : pg_credereum is a PostgreSQL extension that provides a cryptographically verifiable audit capability for a PostgreSQL database, bringing some properties of blockchain to relational DBMS.

  • H3-pg : PostgreSQL bindings for H3, a hierarchical hexagonal geospatial indexing system.

Postgres can do what?

There are a ton of Postgres compatible features out there. Some of them are overlooked core features and some of them require installing a different version of Postgres. Here are a few examples:

 

  • TimescaleDB: Time series data management with Postgres

  • YugabyteDB: Globally Distributed database with PostgreSQL compatibility

  • Postgres-XL: Horizontally partitioned PostgreSQL

  • Agensgraph: Graph capabilities with Postgres

  • PG-Strom: GPU accelerated extension for Postgres 

 

Upcoming Education and Networking opportunities:

 

Joshua D. Drake     August 07, 2019

Latest Posts

  •  A database such as PostgreSQL is not just there to store data – it is also a tool to protect data. Your data must not be lost and it must not be seen by people who are unauthorized or hostile. The...
  • Timescale Sponsor Highlight Blog for PostgresConf Silicon Valley 2019  As part of the countdown to PostgresConf Silicon Valley, learn more about featured Partner Sponsor Timescale, including their...
  •   It is late August, 2019. This is the time where we are usually prepping for the very busy fall season and not much else. However, this is the Year of Postgres and everyone is driving 200MPH do...
  • Congratulations Henrietta Dombrovskaya, Diversity, Equity, and Inclusion member for PostgresConf as well as Chicago Postgresql organizer nominated for Technologist of the Year!   Career Opport...