Posts

Don't let your PostgreSQL get exploited

As you may have heard, there are reportedly over 1,500 PostgreSQL servers that have been exploited to mine Bitcoin. And your server could be next if you haven't taken precautions. Firstly, you need to update to the latest minor release, just so no known exploitable bugs exist on your system. But regardless of whether you update, your PostgreSQL instance could still be misconfigured in a way that would allow unwelcome visitors access, so you need to make sure you shore up your defenses. Here are some steps you should take. 1. Get it off the internet Your database shouldn't be accessible from the open internet. People run port scanners on public IP addresses all the time, and yours will eventually be found. Edit postgresql.conf and ensure listen_addresses isn't set to "*" , because this tells PostgreSQL to accept connections from any network interface. Instead, make sure it only listens to connections from the local network. For example: listen_addresses = '...
Post a Comment
Read more

jsquery vs SQL/JSON

SQL/JSON is coming to PostgreSQL 12 and provides a native way to query JSON data (although, to be specific, JSON data stored as a JSONB data type).  This also introduces the jsonpath data type which is used for SQL/JSON query expressions.  I'll not be going into its usage in detail, or covering performance characteristics (at least not in this post), but I will compare the syntax and functionality with jsquery.  Note that this may potentially change prior to final release. jsquery was introduced as a non-core extension for PostgreSQL 9.4 and higher by Teodor Sigaev, Alexander Korotkov and Oleg Bartunov.  Like jsonpath, it also used its own datatype, jsquery. We have some functions to which we can pass jsonpath expressions to, 2 of which have operators which can be used as shorthand (albeit without additional parameter control, as they exist primarily for indexing purposes): Function Operator Description jsonb_path_exists ...
Post a Comment
Read more

PostgreSQL 9.6 - Part 1.1 - Horizontal Scalability revisited

In my previous blog post about horizontal scalability changes in PostrgreSQL 9.6, I covered pushing sorts and joins to the remote server, but Robert Haas (EnterpriseDB) has just committed a change that allows DML (that's UPDATEs and DELETEs) to be pushed to the remote server too. This is thanks to the work of Etsuro Fujita (NTT) with advice and testing from Rushabh Lathia (EnterpriseDB), Shigeru Hanada, Robert Haas (EnterpriseDB), Kyotaro Horiguchi (NTT), Albe Laurenz and myself (EnterpriseDB). So this calls for an appendix to my original post! DML pushdown The problem previously was that an update or a delete would mean fetching a batch of rows from the remote server, and sending individual UPDATE commands to the remote server. So if we ran this on the local server (where remote.big_table is a remote table which resides on the remote server): UPDATE remote.big_table SET content = content || '.'; The remote server would receive the following request from the local s...
Post a Comment
Read more

PostgreSQL 9.6 - Part 2 - Monitoring

This is part 2 in a 4 part blog series on improvements coming in PostgreSQL 9.6. Part 1 - Horizontal Scalability Join Pushdown Sort Pushdown "Walk and chew gum at the same time" Part 2 - Monitoring VACUUM progress monitoring Wait monitoring Part 3 - Parallelism Part 4 - Vertical Scalability VACUUM progress monitoring When you run VACUUM on a large table, do you know how much it's done after 5 minutes? Do you know how much more there is to go? The answer is, no. You can't know, at least not accurately or easily. In PostgreSQL 9.6, you'll be able to monitor just this (although not VACUUM FULL), thanks to the work of Amit Langote (NTT) and Robert Haas (EnterpriseDB), which was built upon the work done by Rahila Syed (EnterpriseDB) and Vinayak Pokale (NTT). We now have a system view called pg_stat_progress_vacuum. This is the first in, hopefully, several progress monitoring views to come in future. Here's an example of what it shows you (shown in...
1 comment
Read more

PostgreSQL 9.6 - Part 1 - Horizontal Scalability

PostgreSQL 9.6 is shaping up to be an impressive release, so I thought I'd try to summarise some of the big features and improvements that it will bring. This certainly won't be exhaustive, but it will touch upon areas I've looked at closely. I'm looking at breaking this down into the following areas: Part 1 - Horizontal Scalability Part 2 - Monitoring Part 3 - Parallelism Part 4 - Vertical Scalability So without further ado, here's Part 1 - Horizontal Scalability I guess I should start with the question some might be asking: "What is 'Horizontal Scalability'?" This is just a fancy way of describing the ability to distribute workload among several clusters/servers/locations etc. So rather than putting all the burden on one server, you can delegate at least some of the work to other servers to speed things up. For 9.6, I'll specifically be covering foreign tables (i.e. tables on remote servers which are being queried as if they wer...
Post a Comment
Read more

This is a blog test

This is just a blog test. Please ignore.
Post a Comment
Read more

Students, we want you involved in Google Summer of Code

Image
The PostgreSQL Project is yet again participating in Google Summer of Code for 2014. Google will be funding students who take part at $5,500 USD per student (see GSoC FAQ for more details). We would like to hear from students who would be willing to work on projects to add new or enhance existing features. You won't be going it alone, we'll assign experienced community developers to mentor you throughout your project. Have a look at the TODO list on the wiki for inspiration, or the list of project ideas submitted so far by the community, although these are by no means what projects are limited to. Whether you've spotted something you think you could work on, or have a new idea to propose, submit it to the pgsql-students mailing list for discussion (see the mailing list page to subscribe) but do it soon as there's less than 3 weeks to register final proposals. Also, if you know of any students who may be interested in participating, please send them our way. ...
Post a Comment
Read more