As I already mentioned, passwords were traditionally used to prove identity electronically, but are showing their weakness with increased computing power has increased and expanded attack vectors. Basically, user passwords have several restrictions:

• must be simple enough to remember
• must be short enough to type repeatedly
• must be complex enough to not be easily guessed
• must be long enough to not be easily cracked (discovered by repeated password attempts) or the number of password attempts must be limited

As you can see, the simple/short and complex/long requirements are at odds, so there is always a tension between them. Users often choose simple or short passwords, and administrators often add password length and complexity requirements to counteract that, though there is a limit to the length and complexity that users will accept. Administrators can also add delays or a lockout after unsuccessful authorization attempts to reduce the cracking risk. Logging of authorization failures can sometimes help too.

While Postgres records failed login attempts in the server logs, it doesn't provide any of the other administrative tools for password control. Administrators are expected to use an external authentication service like LDAP or PAM, which have password management features.

Continue Reading »

Security is a super important topic. This is not only true in the PostgreSQL world – it holds true for pretty much any modern IT system. Databases, however, have special security requirements. More often than not confidential data is stored and therefore it makes sense to ensure that data is protected properly. Security first !

PostgreSQL: Listing all permissions

Gaining an overview of all permissions granted to users in PostgreSQL can be quite difficult. However, if you want to secure your system, gaining an overview is really everything – it can be quite easy to forget a permission here and there and fixing things can be a painful task. To make life easier, Cybertec has implemented pg_permission (https://github.com/cybertec-postgresql/pg_permission). There are a couple of things, which can be achieved with pg_permission:

• Gain a faster overview and list all permissions
• Compare your “desired state” to what you got
• Instantly fix errors

In short: pg_permission can do more than just listing what there is. However, let us get started with the simple case – listing all permissions. pg_permission provides a couple of views, which can be accessed directly once the extension has been deployed. Here is an example:

test=# \x
Expanded display is on.
test=# SELECT * 
       FROM     all_permissions 
       WHERE    role_name = 'workspace_owner';
-[ RECORD 1 ]-------------------------------------------------------
object_type | TABLE
role_name   | workspace_owner
schema_name | public
object_name | b
column_name | 
permission  | SELECT
granted     | t
-[ RECORD 2 ]-------------------------------------------------------
object_type | TABLE
role_name   | workspace_owner
schema_name | public
object_name | b
column_name | 
permission  | INSERT
granted     | t
-[ RECORD 3 ]-------------------------------------------------------
object_type | TABLE
role_name   | workspace_owner
schema_name | public
object_name | b
column_name | 
permission  | UPDATE
granted     | f

The easiest way is to use the “all_permissions” view to gain an

I needed to push data from a Microsoft SQL Server 2005 to our beloved database, so why don’t use a FDW to the purpose? It has not been as simple as with other FDW, but works!

PostgreSQL to Microsoft SQL Server Using TDS Foreign Data Wrapper

At work I needed to push data out from a Microsoft SQL Server 2005 to a PostgreSQL 11 instance. Foreign Data Wrappers was my first thought! Perl to the rescue was my second, but since I had some time, I decided to investigate the first way first.

The scenario was the following:

• CentOS 7 machine running PostgreSQL 11, it is not my preferred setup (I do prefer either FreeBSD or Ubuntu), but I have to deal with that;
• Microsoft SQL Server 2005 running on a Windows Server , surely not something I like to work with and to which I had to connect via remote desktop (argh!).

First Step: get TDS working

After a quick research on the web, I discovered that MSSQL talks the Table Data Stream (TDS for short), so I don’t need to install an ODBC stack on my Linux box. And luckily, there are binaries for CentOS:

$ sudo yum install freetds $ sudo yum install freetds-devel freetds-doc 

freetds comes along with a tsql terminal command that is meant to be a diagnosing tool, so nothing as complete as a psql terminal. You should really test your connectivity with tsql before proceeding further, since it can save you hours of debugging when things do not work.
Thanks to a pragmatic test with tsql I discovered that I needed to open port 1433 (default MSSQL port) on our...

The following commit has been merged into Postgres 12, adding a new option for VACUUM and ANALYZE:

commit: 803b1301e8c9aac478abeec62824a5d09664ffff
author: Michael Paquier <michael@paquier.xyz>
date: Thu, 4 Oct 2018 09:00:33 +0900
Add option SKIP_LOCKED to VACUUM and ANALYZE

When specified, this option allows VACUUM to skip the work on a relation
if there is a conflicting lock on it when trying to open it at the
beginning of its processing.

Similarly to autovacuum, this comes with a couple of limitations while
the relation is processed which can cause the process to still block:
- when opening the relation indexes.
- when acquiring row samples for table inheritance trees, partition trees
or certain types of foreign tables, and that a lock is taken on some
leaves of such trees.

Author: Nathan Bossart
Reviewed-by: Michael Paquier, Andres Freund, Masahiko Sawada
Discussion: https://postgr.es/m/9EF7EBE4-720D-4CF1-9D0E-4403D7E92990@amazon.com
Discussion: https://postgr.es/m/20171201160907.27110.74730@wrigleys.postgresql.org

Postgres 11 has extended VACUUM so as multiple relations can be specified in a single query, processing each relation one at a time. However if VACUUM gets stuck on a relation which is locked for a reason or another for a long time, it is up to the application layer which has triggered VACUUM to be careful to look at that and unblock the situation. SKIP_LOCKED brings more control regarding that by skipping immediately any relation that cannot be locked at the beginning of VACUUM or ANALYZE processing, meaning that the processing will finish on a timely manner at the cost of potentially doing nothing, which can also be dangerous if a table keeps accumulating bloat and is not cleaned up. As mentioned in the commit message, there are some limitations similar to autovacuum:

• Relation indexes may need to be locked, which would cause the processing to still block when working on them.
• The list of relations part of a partition or inheritance tree to process is built at the beginning of VACUUM or

I mentioned previously that it is possible to implement certificate authentication on removable media, e.g., a USB memory stick. This blog post shows how it is done. First, root and server certificates and key files must be created:

$ cd $PGDATA
 
# create root certificate and key file
$ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root.momjian.us"
$ chmod og-rwx root.key
$ openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
 
# create server certificate and key file
$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=momjian.us"
$ chmod og-rwx server.key
$ openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

Continue Reading »

About once a month I get this question: “How do I contribute to Postgres?”. PostgreSQL is a great database with a solid code base and for many of us, contributing back to open source is a worthwhile cause. The thing about contributing back to Postgres is you generally don’t just jump right in and commit code on day one. So figuring out where to start can be a bit overwhelming. If you’re considering getting more involved with Postgres, here’s a few tips that you may find helpful.

Follow what’s happening

The number one way to familiarize yourself with the Postgres development and code community is to subscribe to the mailing lists. Even if you’re not considering contributing back, the mailing lists can be a great place to level up your knowledge and skills around Postgres. Fair warning: the mailing lists can be very active. But that’s ok, as you don’t necessarily need to read every email as it happens—daily digests work just fine. There is a long list of mailing lists you can subscribe to, but here are a few I think you should know about:

• pgsql-general - This is the most active of mailing lists where you’ll find questions about working with Postgres and troubleshooting. It’s a great place to start to chime in and help others as you see questions.
• pgsql-hackers - Where core development happens. A must read to follow along for a few months before you start contributing yourself
• pgsql-announce - Major announcements about new releases and happenings with Postgres.
• pgsql-advocacy - If you’re more interested in the evangelism side this one is worth a subscription.

Following along to these lists will definitely prepare you to contribute code in the future. And will give you the opportunity to chime in to the discussions.

Familiarize yourself with the process

As you are reading along with the mailing lists the docs will become one of your best friends for understanding how certain things work. Postgres docs are rich with how things work so when you have questions best to check there ahead of asking what may have already

Interested in attending this year’s conference?

Register for PostgresConf 2019

Interested in an AWESOME international Postgres Conference opportunity? Consider attending PgConf Russia

I have developed a particular Git workflow for maintaining PostgreSQL feature branches and submitting patches to the pgsql-hackers mailing list and commit fests. Perhaps it’s also useful to others.

This workflow is useful for features that take a long time to develop, will be submitted for review several times, and will require a significant amount of changes over time. In simpler cases, it’s probably too much overhead.

You start as usual with a new feature branch off master

git checkout -b reindex-concurrently master

and code away. Make as many commits as you like for every change you make. Never rebase this branch. Push it somewhere else regularly for backup.

When it’s time to submit your feature for the first time, first merge in the current master branch, fix any conflicts, run all the tests:

git checkout master
git pull
make world
make check-world
git checkout reindex-concurrently
git merge master
# possibly conflict resolution
make world
make check-world

(The actual commands are something like make world -j4 -k and make check-world -Otarget -j4, but I’ll leave out those options in this post for simplicity.)

(Why run the build and tests on the master branch before merging? That ensures that if the build or tests fail later in your branch, it’s because of your code and not because a bad situation in master or a system problem. It happens.)

Now your code is good to go. But you can’t submit it like that, you need to squash it to a single patch. But you don’t want to mess up your development history by overwriting your feature branch. I create what I call a separate “submission” branch for this, like so:

git checkout -b _submission/reindex-concurrently-v1 master

The naming here is just so that this shows up sorted separately in my git branch list. Then

git merge --squash reindex-concurrently

This effectively makes a copy of your feature branch as a single staged commit but doesn’t commit it yet. Now commit it:

git commit

At the first go-round, you should now write a commit message for your feature patch. Then c

Traditionally, passwords were used to prove identity electronically. As computing power has increased and attack vectors expanded, passwords are proving insufficient. Multi-factor authentication uses more than one authentication factor to strengthen authentication checking. The three factors are:

1. What you know, e.g., password, PIN
2. What you have, e.g., cell phone, cryptographic hardware
3. What you are, e.g., finger print, iris pattern, voice

Postgres supports the first option, "What you know," natively using local and external passwords. It supports the second option, "What you have," using cert authentication. If the private key is secured with a password, that adds a second required factor for authentication. Cert only supports private keys stored in the file system, like a local file system or a removable USB memory stick.

One enhanced authentication method allows access to private keys stored on PIV devices, like the YubiKey. There are two advantages of using a PIV device compared to cert:

• Requires a PIN, like a private-key password, but locks the device after three incorrect PIN entries (File-system-stored private keys protected with passwords can be offline brute-force attacked.)
• While the private key can be used to decrypt and sign data, it cannot be copied from the PIV device, unlike one stored in a file system

Continue Reading »

Seasons each have a different feel, a different rhythm. Temperature, weather, sunlight, and traditions—they all vary by season. For me, summer usually includes a beach vacation. And winter brings the smell of hot apple cider on the stove, days in the mountains hoping for the next good snowstorm—and New Year’s resolutions. Somehow January is the time to pause and reflect on the accomplishments of the past year, to take stock in what worked, and what didn’t. And of course there are the TOP TEN LISTS.

Spoiler alert, yes, this is a Top 10 list. If you’re a regular on the Citus Data blog, you know our Citus database engineers love PostgreSQL. And one of the open source responsibilities we take seriously is the importance of sharing learnings, how-to’s, and expertise. One way we share learnings is by giving lots of conference talks (seems like I have to update our Events page every week with new events.) And another way we share our learnings is with our blog.

So just in case you missed any of our best posts from last year, here is the TOP TEN list of the most popular Citus Data blogs published in 2018. Enjoy.

The Postgres 10 feature you didn’t know about: CREATE STATISTICS

BY SAMAY SHARMA | Postgres stores a lot of statistics about your data in order to effectively retrieve results when you query your database. In this post, Samay dives deep into some of the statistics PostgreSQL stores and how you can leverage CREATE STATISTICS to improve query performance when different columns are related.

In Postgres, the EXPLAIN planner collects statistics to help it estimate how many rows will be returned after executing a certain part of the plan, which then influences which type of scan or join algorithm will be used. Before Postgres 10, there wasn’t an easy way to tell the planner to collect statistics which capture the relationship between columns. But since the release of Postgres 10, there’s a feature which is built to solve exactly this problem. And this feature has gotten even better in PostgreSQL 11, too. All about C

Related with my post: https://abdulyadi.wordpress.com/2015/11/14/extension-for-qr-code-bitmap/. I have repackage the module and available on github: https://github.com/AbdulYadi/pgqr.

This project adds 2 functionality to QR code generator from repository https://github.com/swex/QR-Image-embedded:

1. In-memory monochrome bitmap construction (1 bit per pixel).
2. Wrap the whole package as PostgreSQL extension.

This project has been compiled successfully in Linux against PostgreSQL version 11.
$ make clean
$ make
$ make install

On successful compilation, install this extension in PostgreSQL environment
$ create extension pgqr

Function pgqr has 4 parameters:

1. t text: text to be encoded.
2. correction_level integer: 0 to 3.
3. model_number integer: 0 to 2.
4. scale integer: pixels for each dot.

Let us create a QR Code
$ select pgqr(‘QR Code with PostgreSQL’, 0, 0, 4);
The output is a monochrome bitmap ready for display.

What is the role of PgBouncer in a Postgres High Availability stack? What even is PgBouncer at the end of the day? Is it a glorified traffic cop, or an integral component critical to the long-term survival of a Postgres deployment?

When we talk about Postgres High Availability, a lot of terms might spring to mind. Replicas, streaming, disaster recovery, fail-over, automation; it’s a ceaseless litany of architectural concepts and methodologies. The real question is: how do we get from Here to There?

The Importance of Proxies

It’s no secret that the application stack must communicate with the database. Regardless of how many layers of decoupling, queues, and atomicity of our implementation, data must eventually be stored for reference. But where is that endpoint? Presuming that write target is Postgres, what ensures the data reaches that desired terminus?

Consider this diagram:

In this case, it doesn’t matter what type of Standby we’re using. It could be a physical streaming replica, some kind of logical copy, or a fully configured BDR node. Likewise, the Failover Mechanism is equally irrelevant. Whether we rely on repmgr, Patroni, Stolon, Pacemaker, or a haphazard collection of ad-hoc scripts, the important part is that we separate the application from the database through some kind of proxy.

Patroni relies on HAProxy and Stolon has its own proxy implementation, but what about the others? Traditionally PgBouncer fills this role. Without this important component, applications must connect directly to either the Primary or post-promotion Standby. If we’re being brutally honest, the application layer can’t be trusted with that kind of responsibility.

But why is that? Simply stated, we don’t know what the application layer is. In reality, an application is anything capable of connecting to the database. That could be the official software, or it could be a report, or a maintenance script, or a query tool, or any number of other access vectors. Which database node are they connecting to, and doe

pgbouncer is the most widely used connection pooler for PostgreSQL.
This blog will provide a simple cookbook how to configure user authentication with pgbouncer.

I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site.
But it should work pretty similarly anywhere.

What is a connection pooler?

Setting max_connections to a high value can impact performace and can even bring your database to its knees if all these connections become active at the same time.

Also, if database connections are short-lived, a substantial amount of your database resources can be wasted just opening database connections.

To mitigate these two problems, we need a connection pooler. A connection pooler is a proxy between the client and the database: clients connect to the connection pooler, which handles the SQL requests via a relatively stable set of persistent database connections (the “connection pool”).

Since clients connect to pgbouncer, it will have to be able to authenticate them, so we have to configure it accordingly.

The very simple method (authentication file)

This method is useful if the number of database users is small and passwords don’t change frequently.

For that, we create a configuration file userlist.txt in the pgbouncer configuration directory (on my system /etc/pgbouncer).
The file contains the database users and their passwords, so that pgbouncer can authenticate the client without resorting to the database server.
It looks like this:

"laurenz" "md565b6fad0e85688f3f101065bc39552df"
"postgres" "md553f48b7c4b76a86ce72276c5755f217d"

You can write the file by hand using the information from the pg_shadow catalog table, or you can create it automatically.
For that to work, you need

• • the PostgreSQL command line client psql
    If it is not on your PATH, you’ll have to use the absolute path (something like /usr/pgsql-11/bin/psql or "C:\Program Files\PostgreSQL\11\bin\psql").
  • write access to the pgbouncer configuration file, which may require th

Nordic PGDay 2019 is happy to announce our schedule has now been published.

Our registration has also been opened. Seats are limited so we recommend that you register early if you are interested! Early Bird discount is available until February 17th or for the first 40 tickets.

We look forward to seeing you in Copenhagen!

After I shared the sequential UUID benchmarks a couple of weeks ago, one of the points raised in feedback was the choice of the storage space. I’ve intentionally used a fairly weak storage system (RAID10 on three 7.2k SATA drives) because I wanted to demonstrate the benefits. But a couple of readers suggested using SSDs might significantly reduce the difference between regular and sequential UUIDs due to SSDs handling random I/O much better than rotational storage. My hypothesis was that while using SSDs may reduce the gap, it certainly won’t eliminate it entirely because the amplification (both in terms of number of I/O requests and WAL volume) is independent of the storage system. But the only way to verify this it is to repeat the tests, this time on SSDs. So here we go …

I’m not going to repeat the explanation of how sequential UUIDs work – you can find that in the previous post. I’ll just show results on SSDs, with a short discussion.

The tests were done on two different systems I always use for benchmarking. Both systems use SSDs, but quite different types / generations. This adds a bit more variability, which seems like a good thing in this sort of tests anyway.

i5-2500K with 6 x S3700 SSD

This first system is a fairly small (and somewhat old-ish), with a relatively old Intel CPU i5-2500K with just 4 cores, 8GB of RAM and 6 Intel S3700 SSD disks in a RAID0.

The following chart illustrates the throughput for various test scales:

The degradation is clearly not as bad as with rotational storage – on the largest scale it drops “only” to ~50% (instead of ~20%). And for the medium scale the drop is even smaller.

But how is that possible? The initial hypothesis was that the I/O amplification (increase in number of random I/O requests) is the same, independently of the storage system – if each INSERT means you need to do a 3 random I/O writes, that does not depend on storage system type. Of SSDs do not behave simply as “faster” rotational device – that’s an oversimplification. For example most SSDs are interna

The following commit has been merged into Postgres 12 a couple of months ago, easing failover control flow:

commit: 10074651e3355e2405015f6253602be8344bc829
author: Michael Paquier <michael@paquier.xyz>
date: Thu, 25 Oct 2018 09:46:00 +0900
Add pg_promote function

This function is able to promote a standby with this new SQL-callable
function.  Execution access can be granted to non-superusers so that
failover tools can observe the principle of least privilege.

Catalog version is bumped.

Author: Laurenz Albe
Reviewed-by: Michael Paquier, Masahiko Sawada
Discussion: https://postgr.es/m/6e7c79b3ec916cf49742fb8849ed17cd87aed620.camel@cybertec.at

Promotion is a process which can be used on a standby server to end recovery and allow it to begin read-write operations, bumping this standby server to a new timeline on the way. This operation can be done using a couple of options:

• pg_ctl promote, which waits for the standby to finish the promotion before exiting by default.
• Define promote_trigger_file in postgresql.conf and create the file which would be detected by the startup process and translated so as recovery finishes (or trigger_file in recovery.conf up to v11, recovery parameters being merged to postgresql.conf in v12 and newer versions).

The commit mentioned above offers a third way to trigger a promotion with a SQL-callable function, which presents a huge advantage compared to the two other methods: there is no need to connect to the standby physical host to trigger the promotion as everything can be done with a backend session. Note however that this needs a standby server able to accept read-only operations and connections.

By default pg_promote() waits for the promotion to complete before returning back its result to its caller, waiting for a maximum of 60 seconds, which is the same default as the promote mode of pg_ctl. However it is possible to enforce both the wait mode and the timeout value by specifying the wait mode as a boolean for the first argument, and the timeout as an integer in seconds

The Jan meeting will be held at 18:00 EST on Tues, the 22^nd. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed.

What

CoverMyMeds’ very own CJ will be presenting this month. He’s going to tell us all about how CoverMyMeds uses VividCortex to assist with monitoring PostgreSQL and maintaining the high performance of the database.

Where

CoverMyMeds has graciously agreed to validate your parking if you use their garage so please park there:

You can safely ignore any sign saying to not park in the garage as long as it’s after 17:30 when you arrive.

Park in any space that is not marked ‘24 hour reserved’.

Once parked, take the elevator/stairs to the 3^rd floor to reach the Miranova lobby. Once in the lobby, the elevator bank is in the back (West side) of the building. Take a left and walk down the hall until you see the elevator bank on your right. Grab an elevator up to the 11^th floor. (If the elevator won’t let you pick the 11^th floor, contact Doug or CJ (info below)). Once you exit the elevator, look to your left and right; one side will have visible cubicles, the other won’t. Head to the side without cubicles. You’re now in the community space:

The kitchen is to your right (grab yourself a drink) and the meeting will be held to your left. Walk down the room towards the stage.

If you have any issues or questions with parking or the elevators, feel free to text/call Doug at +1.614.316.5079 or CJ at +1.740.407.7043

The Story

Postgres is up and running, and things are humming along. Replication is working, vacuums are running, and there are no idle transactions in sight. You poke around your logs and make sure things are clean, but you notice a little warning:

2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.

Strange. There are no open transactions. You look in pg_stat_activity once, twice, three times–nothing. What’s up with that? How long has this been happening? You go back to you logs and you throw the whole thing into grep and find:

$ cat db1.log | grep "WARNING"
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:55 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:55 GMT WARNING:  oldest xmin is far in the past
...
$ cat db1.log | grep "HINT"
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
...

Uh oh. What’s going on? And the pressure is on–nobody wants to be stuck dealing with wraparound problems. The adrenaline rush hits, and you’re frantically looking for the silver bullet to kill off this problem. Kill. Maybe restart the database? No, you can’t do that–it’s a production server. You’ll get chewed out! You look everywh

Since the release of v3, Postgres-BDR has evolved into the go-to clustering technology built specially for businesses that require geographically distributed databases with multiple masters.

To get an update on Postgres-BDR’s development, new features, and future roadmap, 2ndQuadrant held the pglogical and Postgres-BDR Update webinar as part of its PostgreSQL webinar series.

The webinar was presented by Simon Riggs, Founder & CEO of 2ndQuadrant, who is also a major contributor of the open source PostgreSQL project. Those who weren’t able to attend the live event can now view the recording here.

For any questions or comments regarding Postgres-BDR, please send an email to info@2ndQuadrant.com.

The team has released pgBackRest 2.08 today. As part of a continuing effort, more bits have been moved from Perl to C. Sadly, this adds a new wrinkle for those of us on OSX, as when compiling, you now get:

gcc -I. -I../libc -std=c99 -D_POSIX_C_SOURCE=200112L -O2 -Wfatal-errors -Wall -Wextra -Wwrite-strings -Wswitch-enum -Wconversion -Wformat=2 -Wformat-nonliteral -Wno-clobbered -Wno-missing-field-initializers -Wstrict-prototypes -Wpointer-arith -Wvla `xml2-config --cflags` `perl -MExtUtils::Embed -e ccopts` -DWITH_PERL -DNDEBUG  -c common/lock.c -o common/lock.o
warning: unknown warning option '-Wno-clobbered'; did you mean '-Wno-consumed'?
      [-Wunknown-warning-option]
common/lock.c:76:21: warning: implicit declaration of function 'flock' is invalid in C99
      [-Wimplicit-function-declaration]
                if (flock(result, LOCK_EX | LOCK_NB) == -1)
                    ^
common/lock.c:76:21: warning: this function declaration is not a prototype [-Wstrict-prototypes]
common/lock.c:76:35: fatal error: use of undeclared identifier 'LOCK_EX'
                if (flock(result, LOCK_EX | LOCK_NB) == -1)
                                  ^
3 warnings and 1 error generated.
make: *** [common/lock.o] Error 1

To fix this, you will need to edit src/Makefile and change line 12 from:

CSTD = -std=c99 -D_POSIX_C_SOURCE=200112L

to:

CSTD = -std=c99 -D_DARWIN_C_SOURCE

Then, you can follow the other steps on my previous post and everything should compile and function properly.

Enjoy!

Data Anonymization is a complex topic but PostgreSQL has a lot of interesting features to tackle this challenge! Here is an overview of different approach and how to implement them directly within a PostgreSQL database.

Over the last few months I have been working on a project called PostgreSQL Anonymizer. It led me to try various techniques to remove personal data for different purposes: development, CI, functional testing, Analytics, etc.

See my previous article “Introducing PostgreSQL Anonymizer” for more details about that project…

So far, I have found 8 different ways to anonymize a dataset. Here’s a quick tour with practical examples. All the queries in the article will use a simplified table (see below) and should work with any current version of PostgreSQL (from 9.4 to 11).

CREATE TABLE people (
    id SERIAL,
    name TEXT NOT NULL,
    address TEXT,
    age INTEGER,
    salary INTEGER,
    phone TEXT
);

0. Sampling

Sampling is not Anonymization! But when you need to remove personal data from a database, most of the time you don’t need to publish all the rows. The anonymization process will be faster and you will limit the risk of unintended disclosure.

So before going any further it is important to note that PostgreSQL provides a feature called TABLESAMPLE that will reduce the size of your dataset.

For example, if you want to work only on 20% of a table:

SELECT * FROM people TABLESAMPLE BERNOULLI(20);

And if you want to extract a subset among several tables while maintaining referential integrity, pg_sample is your friend !

1. Suppression

This is the most obvious way to get rid of personal identifiers: just wipe a column by replacing all values with NULL (aka “Nullification”) or with a constant value (“Static Substitution”).

Example :

UPDATE people SET name = '<CONFIDENTIAL>';
UPDATE people SET address = NULL;

This is simple and effective. For useless or highly sensitive data fields, it may be the best option.

But of course it will break integrity constraints (PRIMARY, UNIQUE, NOT NULL, etc.). An

Please join Percona’s Senior Software Engineer, Ibrar Ahmed as he presents his High-Performance PostgreSQL, Tuning and Optimization Guide on Friday, January, 4th, at 8:00 AM PDT (UTC-7) / 11:00 AM EDT (UTC-4).

Register Now

PostgreSQL is one of the leading open-source databases. Out of the box, the default PostgreSQL configuration is not tuned for any workload. Thus, any system with least resources can run it. PostgreSQL does not give optimum performance on high permanence machines because it is not using the all available resource. PostgreSQL provides a system where you can tune your database according to your workload and machine’s specifications. In addition to PostgreSQL, we can also tune our Linux box so that the database load can work optimally.

In this webinar on High-Performance PostgreSQL, Tuning and Optimization, we will learn how to tune PostgreSQL and we’ll see the results of that tuning. We will also touch on tuning some Linux kernel parameters.

After the final release of patch 9.3.25 on November 8th 2018, PostgreSQL 9.3 is no longer supported. Therefore it’s time for all users of PG 9.3 to upgrade their databases to a newer supported version. The benefits of having a supported version are many and that’s what Craig Ringer talks about in the Q&A session below:

Why is it important to upgrade your PostgreSQL database to the latest version?

Craig: Always update to the latest minor version. 2ndQuadrant’s 24/7 support services often help customers who could’ve avoided experiencing a production outage or fault simply by updating every minor version or two. The PostgreSQL community (including 2ndQuadrant) releases minor point releases conservatively, and for good reasons. Keep up to date on those patches.

Note: A “minor” PostgreSQL release, i.e. a maintenance release, is an increment in the last part of the version number, whether it is a two number version like 10.x or a three-number version like 9.6.x. See “version policy” on the PostgreSQL website.

For major release version updates keeping up is not that important. It’s often best to stay on the latest minor release of your current major version and skip a major PostgreSQL release or two before updating. Update if the new major releases will solve problems you are facing, your current version is approaching the community end-of-life date, or you’re planning system changes like OS upgrades or server migrations anyway.

You’ll miss out on performance improvements, data integrity protection enhancements and improvements in monitoring and diagnostics offered by new major versions. But any change has a risk, and a well-planned upgrade requires testing and preparation, which has costs. So seeking a middle ground is usually wise.

Note that it’s usually harder to upgrade from a very old PostgreSQL to the latest, and it may need more planning, so it’s wise to start planning well before the End of Life (EoL) date. That way you maintain continuous coverage for security updates and any serious bugs that may be discovered

Once I received a mail with question:
Can you tell me why I can’t select the column `references`?

=# \d v_table_relation
View "public.v_table_relation"
Column | Type | Collation | Nullable | Default
------------+-------------------------------------+-----------+----------+---------
schema | information_schema.sql_identifier | | |
table | information_schema.sql_identifier | | |
columns | information_schema.sql_identifier[] | | |
references | jsonb[] | | |

=# select * from v_table_relation ;
schema | table | columns | references
———–+————+—————–+——————————————————————————————————————–
public | a | {b,c} | {}
public | a2 | {b,c} | {“{\”toTable\”: \”a\”, \”toSchema\”: \”public\”, \”toColumns\”: [\”b\”, \”c\”], \”fromColumns\”: [\”b\”, \”c\”]}”}
workspace | t_employee | {id,name,state} | {}
(3 rows)

=# select references from v_table_relation;
ERROR: syntax error at or near “references”
LINE 1: select references from v_table_relation;

Well, the quick answer will be: because REFERENCES is a keyword you should use double quotes around it, e.g.


=# select references from v_table_relation;


But why sometimes you need explicitly quote identifiers and sometimes it works like without them? And how to know what exactly keywords are used in the PostgreSQL? And what keywords may be used as object names? And so on…

As usual one may find the answers in the PostgreSQL manual:
www.postgresql.org/docs/current/sql-keywords-appendix.html

There we have detailed table that lists all tokens that are key words in the SQL standard and in PostgreSQL. From the manual you will know about reserved and non-reserved tokens, SQL standard compatibility and much more which is out of scope od this post.

There are two more ways to know what tokens can or cannot be used in certain situations. First is for true programmers, and the second is for true admins. Choose your pill, Neo.

Investigating the sources

PostgreSQL uses LALR(1)* parser to work with SQL statements. Implementation of the grammar (parser itself) may be found in the gram.y and impleme