Wednesday, 29 May 2019

Avoiding Gmail's confidential mode

So this is one of the very few (maybe the first?) blog entries I've written that aren't directly related to PostgreSQL, however, it does affect how I (and others) may work on the project.

Last night I received email from Google about my personal G Suite account which I use for all my day-to-day email, which for the most part is related to work on pgAdmin and PostgreSQL. Google were proudly announcing the rollout of their new Gmail Confidential Mode update. If you've not come across this yet, then essentially what it does is allow users to send emails that can be deleted or expired after a certain amount of time, optionally require SMS verification to open them, and prevent printing (but not screen-shots of course), forwarding or downloading etc.

When using the Gmail web interface, this all works fairly seamlessly. I can see why some people would want it if that's all they use, however, like many people, I also use other clients, for example, via IMAP. In that case, instead of the original email Gmail sends a placeholder email to replace the actual message which contains a link to allow you to login to Google and view the message online (assuming the SMS verification passes and the message hasn't been deleted or expired of course). That's going to be quite inconvenient to me, besides which, I really don't want anyone to be able to control access to emails they've sent me, after I've received them.

There's another problem affecting PostgreSQL's mailing lists however. How long will it be until someone sends such a message to one of the PostgreSQL lists, where it will do nothing but clutter up the archives and annoy other users (who won't be able to read the message anyway as they won't be able to login to Google as or whatever the list address was)?

Fixing the PostgreSQL mail servers

After some discussion with some of the PostgreSQL sysadmin team, we discovered that Gmail adds a header to the messages that have confidential mode enabled (X-Gm-Locker: <token>). This is easy for us to deal with - Stefan was very quickly able to add a rule to the Exim configuration on our inbound SMTP servers that will immediately reject any messages with this header, sending a message back to the user explaining why their message was rejected, and to resend it with confidential mode disabled.

Fixing my Gmail outbound email

Disabling confidential mode for my own outbound email was pretty easy:
  1. Select the option to "Manage this domain"
  2. Navigate the G Suite admin interface to Apps -> G Suite -> Gmail -> User Settings.
  3. Under Confidential Mode, set the Disable option, and save the settings.
Now, confidential mode will no longer be available in the compose message interface in Gmail. Well, once the settings have propagated around and the UI has refreshed anyway.

Rejecting confidential messages from Gmail

This part is a little more complex, and requires a suitable G Suite account. It will not work with plain Gmail.
  • Select the option to "Manage this domain"
  • Navigate the G Suite admin interface to Apps -> G Suite -> Gmail -> Advanced Settings -> Content compliance
  • Add a policy:
    • Give it a name
    • Select "Inbound" for the email messages to be affected in section 1.
    • Set the option for "If ANY of the following match the message" in section 2, then add a new expression:
      • Set the type to "Metadata match".
      • Select "Gmail confidential mode" under Attribute.
      • Select "Message is in Gmail confidential mode" under Match type.
      • Save the expression.
  • In section 3, select Reject the message as the action and enter a suitable custom message, e.g. "This domain does not accept messages sent with Gmail Confidential Mode enabled. Please disable and resend."
  • Save the settings.
You should now find that email is rejected by Google, if confidential mode is enabled. I had my friend Jonathan send me a test message, which was duly rejected with the message:

550 5.7.1 This domain does not accept messages sent with Gmail Confidential Mode enabled. Please disable and resend. - gcdp z20sor188854uan.2 - gsmtp

There. Normality has been restored.

Wednesday, 5 September 2018

Why do we install as root?

A couple of common questions I hear from customers (usually long-time users of a particular database from Redwood) via our guys in the field is “why do we install our software as root?” And “why do we run services as postgres?”. The simple, TLDR; answer is “for security”. For a detailed explanation, read on…

A basic principle when securing a software installation is “install with maximum privilege requirements and run with minimal”. In practice this equates to having software being installed and binaries/executables etc. owned by the root user, whilst the services themselves are actually run under a minimally privileged (and ideally dedicated) service user account, typically postgres in a PostgreSQL installation. Data files, and any other files that need to be modified by the software in normal operation are also owned by the service user account.

Let’s look at the running software first. Postgres (which will in fact refuse to run as root), is a server process which is often running on a network port that is accessible from other nodes on the network. Of course, we should limit access as much as possible to only those nodes that need access using both a firewall (even simple iptables rules will work), and Postgres’ pg_hba.conf access control file, but even with those measures in place, it’s possible that a determined attacker (let’s call him Zero Cool) can still gain access to the port the database server is running on.

Once our arch-nemesis Zero Cool has access to the database server port, he needs a way to escalate his attack. This may involve exploiting an unknown security issue in Postgres itself (as with any software, we hope there are none but we’re kidding ourselves if we think it’s totally secure), or it may be that he’s used other techniques such as social engineering to learn a users credentials.

If Zero gains “regular” access to Postgres, then he will be subject to any security measures (access control lists, RLS policies etc) that limit the scope of what the user account he’s used can access/delete/update/whatever. If the user account has superuser privileges or access to un-trusted procedural languages, or if Zero gained access using a lower-level exploit that allows him to execute arbitrary code in other ways, then he will be able to wreak chaos at a lower level in the system, such as overwriting files on disk.

However - and this is the important bit - assuming there are no exploits in the Operating System that he can leverage to gain further privileges, his chaos will be restricted to things that the service account under which Postgres is running can do. In a well secured system where an unprivileged account like postgres is used, that will be limited to damage to the Postgres data files and other files (or processes etc) that user can modify or control. If Postgres were running under a privileged account like root, Zero would have pwned (in script-kiddie parlance) the entire system at this point!

Now consider the case where the Postgres software files were also owned by the postgres user. Zero would not only be able to affect files and processes owned by the service account, but would also be able to modify the software itself, allowing him the opportunity to add backdoors for future access or other malware such as spyware etc. In the case of software that is started as root (even that which later drops those privileges or switches to another user account for normal operation), this could be exploited to gain even easier privileged access at a later time.

This is why we want our software to be installed and owned by a high privilege user such as root and run as a low privileged user such as postgres. Doing so ensures that even if Zero manages to crack his way into Postgres and potentially access or modify data, he cannot modify the software or other aspects of the host system and thus has a much harder time further escalating his attack.

Thursday, 13 July 2017

Some numbers, oh, and pgAdmin 4 v1.6 was released

It's been far too long since I wrote a blog post (again!), so in a bid to start fixing that, here's an easy one:

pgAdmin 4 v1.6 released

The pgAdmin Development Team are pleased to announce the release of pgAdmin 4 version 1.6. This release of pgAdmin 4 includes over 70 bug fixes and a dozen new features. For details, please see the release notes.
Notable changes in this release include:

  • Significant performance improvements on Windows, massively reducing initial load time and improving UI response for the vast majority of users during testing.
  • Enhancements to the Query Tool enabling the viewing of large query resultsets far more quickly. For example, a simple test query with 96K rows rendered results within 1 second vs. 22 seconds in pgAdmin III during testing!
  • A major rewrite of the Query History tab allows browsing of queries executed in the query tool with full details including the entire query, in a much nicer user interface.
  • The Desktop Runtime now supports detachable tabs, allowing the Query Tool and Debugger to be opened in new tabs and then moved to alternate displays (from 1.5 this was possible in web mode only)
  • The Query Tool's Results Grid has been overhauled with a new, sleek look an feel supporting selection of arbitrary rows, columns or blocks of cells with full copy support and column sizing retention.
  • The Dashboard tab can now be closed if desired, to minimise query traffic resulting from graph updates.
For more information, checkout the online documentation, the screenshots, and of course the download page.


For those that aren't aware, pgAdmin 4 is the fourth complete rewrite of pgAdmin since the first code was written way back in 1998(!). One of the major aims of the technology change we made for the new version was to help attract new developers as we always found them extremely hard to find when we used C++ (we now use Python and Javascript). To kickstart the project, EnterpriseDB (my employer) allowed me to make use of one of our development teams and we embarked on a pretty epic journey to get the first release done. Much of that is documented in an earlier post.

We were happy to find that our plan worked - better than expected in fact - when Pivotal started contributing heavily to the project, bringing new skills and techniques that have enabled us to improve both pgAdmin and the way we design and develop it even further. This release of pgAdmin is the first to include major changes in functionality developed by the Pivotal team, which you can see in the overhauled Query Tool result grid UI and the enhanced Query History tab.

Today, community members, EnterpriseDB staff and Pivotal staff all collaborate to make pgAdmin better and better. Look out on the mailing lists for ways you can get involved, by joining periodic meetings we hold, participating in user focus sessions, or contributing to the project in any other way that may interest you.


Well, I promised you some numbers and here they are. This info was compiled for my boss Marc last week who was curious to see how things were working out expanding the developer base on the project. Numbers are based on 2017-01-01 - 2017-07-05 stats from the GIT repository:

Number of committed changes by EDB:     256 (mostly smaller items such as bug fixes)
Number of committed changes by Pivotal:  67 (some larger changes)
Number of committed changes by Others:   62

Lines of code added by EDB:        32,542
Lines of code added by Pivotal:   110,017
Lines of code added by others:     19,221

Lines of code removed by EDB:      20,056
Lines of code removed by Pivotal:  35,688
Lines of code removed by Others:    2,184

I love it when a plan comes together :-)

Friday, 15 April 2016

pgAdmin 4 - The elephant nears the finish line

As you may know, many of us from the pgAdmin team have been hard at work on pgAdmin 4 for some time now. pgAdmin 4 is a complete rewrite of pgAdmin (the fourth, as you may guess), the previous version having reached the end of it's maintainable life after 14 years of development.

Work on the project began slowly, almost two years ago, however the team at EnterpriseDB have ramped up the development pace over the last few months. Right now, we're approaching alpha-readiness which we expect to be at within a few weeks.


This new application is designed for operation on both the desktop and a webserver. Written in Python using the Flask framework for the backend, and Javascript/jQuery/Backbone for the frontend, it can easily be deployed as a WSGI application for multiple users in practically any network environment. A small runtime application allows it to be run as a desktop application - this is a Qt executable that incorporates a Python interpreter and web browser along with the main application in a single package that can be installed on a developer laptop as with previous versions of pgAdmin.


Whilst the core functionality of pgAdmin 4 remains similar to pgAdmin 3, there are a number of changes we've made:

  • Support for unsupported database versions has been dropped.
  • We haven't re-implemented support for some object types that no one really used in the tool before - for example, operator classes and families.
  • We haven't (yet) reimplemented some of the tools that didn't work so well in pgAdmin 3, such as the graphical query builder or database designer (which was always disabled entirely by default).
  • The Query Tool and Edit Grid have been merged into a single tool. Over coming releases we'll be improving the functionality further to allow in-grid updates to be made to results from arbitrary queries (where a query is determined to be updateable). For now though, updating is allowed when pgAdmin knows the data source is a single table with a primary key.
  • The user interface is more flexible than ever, allowing tabs to be docked and re-arranged in more ways than previously.
  • We've spent time redesigning some of the UI paradigms in pgAdmin 3. Gone are the list controls with Add/Remove buttons, replaced with what we call sub-node grid controls that will allow in-grid editing of key values, with more detail available when needed in expandable rows.
  • We also spend time thinking about how to make it faster to use pgAdmin, by minimising the need to switch between dialogues, using searchable combo boxes and more.
  • The UI is much more attractive, making use of control groupings and expandable regions to make things more readable.


So, enough of the babble, here are some pre-release, semi-polished screenshots:

The main user interface, showing the properties of a function. 

Setting the ACL on a function. 
Adding a member to a composite type using the sub-node grid control. 
The Query Tool and Data Editor.

The Procedural Language Debugger.


As you can imagine, there has been a significant amount of work done to get to this stage, and I really need to express my gratitude to those who have contributed, as well as the executive management team at EnterpriseDB who have allowed me to commit so many people to this project:

Project leadership
  • Ashesh Vashi (engineering team manager, code guru)
  • Karen Blatchley (project manager)

Development team
  • Khushboo Vashi
  • Akshay Joshi
  • Arun Kollan
  • Harshal Dhumal
  • Murtuza Zabuawala
  • Neel Patel
  • Sanket Mehta
  • Surinder Kumar

  • Muhammad Aqeel
  • Paresh More
  • Sandeep Thakkar

  • Priyanka Shendge
  • Fahar Abbas

Of course, there are also community members who are starting to contribute fixes and other improvements, such as Thom Brown (on his own time, not EDBs), Seçkin Alan, Ronan Dunklau and Prasad Somwanshi, all of whom (along with others I may have missed) deserve thanks.

Want to help or learn more?

If you want to help, you can checkout the code and start playing with it. We're not yet feature complete (for example, the Tables node in the treeview is still in development), but we're pretty close. Feel free to try out the code, and report or better yet, fix any bugs or issues you may find. If you wish to start working on new features that is also welcome, but please do email the hackers list first to ensure your work is not something that's already on our project plan!

If anyone would like to talk more about pgAdmin 4, I'll be at PGConf.US next week - the organisers know me well and should be able to help you find me for a chat or demo. See you there!

Sunday, 7 December 2014

The story of pgAdmin

I’m often asked how I first became involved in PostgreSQL, and how the pgAdmin project got started. Much as I’m happy to tell the story over beer, it becomes tedious to do so over email after the first half-dozen or so times. So in a vain attempt to save my fingers from future carpal tunnel syndrome, here’s the tale…

I first discovered PostgreSQL in 1997. I was working at a social housing provider in the UK where, at the time, we were running Informix SE and Pick databases. We wanted to start building in-house web applications for managing additional areas of the business. There were no useful interfaces for Pick that could be used with any of the web technologies available to us at the time and the licensing model for Informix and its ODBC driver was prohibitively expensive, so I started looking for alternatives. 

After spending some time researching mSQL and MySQL, I eventually found PostgreSQL, which seemed to offer everything we needed—a SQL-based DBMS with ODBC and C interfaces, a liberal license, a rich set of features, and a vibrant community supporting it that was responsive to bug reports and open to feature requests. My colleagues and I developed a number of applications using PostgreSQL in the following years and the database became a critical part of our business. We had applications written in PHP for the web as well as Visual Basic for the desktop users. 

However, it was early on in our journey with Postgres that, as a Windows shop (on the desktop at least), we found ourselves wishing for a good graphical management tool for the database. At the time, the only option was pgAccess, which was a basic tool written in TCL/TK that was not easy to get to work on Windows. So I decided to write my own in Visual Basic. The first version was never released to the public and was modeled on the pgAccess design. Called pgManager, it was quickly abandoned as we found the UI to be restrictive and, well, not overly usable. The second incarnation was pgAdmin, which was redesigned from the ground up. It was released as an Open Source project and quickly became a popular tool amongst Postgres users (which was extremely gratifying as it was my first Open Source project). 

Some years later, we once again found ourselves suffering due to the way the application had been designed. I shut myself away for a couple of weeks whilst my family were away visiting relatives in Scotland and wrote pgAdmin II, using a far more structured and maintainable design that implemented a much more usable user interface. I was particularly proud of the design and cleanliness of that code, but by 2002, we needed to start over again. This time it wasn't the application design that was holding us back, but rather the choice of technology. Visual Basic didn't handle internationalization or localization well, nor did it run on platforms other than Windows without the use of WINE, under which it was never particularly stable. The hard decision was made to rewrite everything again, this time using C++ with the wxWidgets framework. pgAdmin III looked much like pgAdmin II, but it solved all of the problems our (in particular) Japanese Linux-using friends were experiencing. Now in its third incarnation, pgAdmin remains the most popular Open Source GUI tool for managing Postgres.

I continued to work at the housing provider and make good use of Postgres until 2007, at which time I moved to EnterpriseDB so I could concentrate on my Postgres work full time. At EDB, not only do I work on our own tools, I also continue to contribute to the PostgreSQL community in various ways. I have the privilege of having full support of the management at EDB for my community work, allowing me the freedom to work on whatever I need to in order to fulfill my community roles on the core team, the PostgreSQL Europe board, and of course, pgAdmin. One of the products I’m responsible for at EDB is Postgres Enterprise Manager, which has a fat client interface that’s based on pgAdmin. This has allowed us to put more effort into the maintenance of pgAdmin, with members of the PEM team at EDB regularly contributing code, fixing bugs and reviewing the patches from elsewhere. Ashesh Vashi has even been made a committer for his efforts.

Despite the hard work over the last 12 or so years, the pgAdmin developers have come to the conclusion that there is limited life left in the current code. Aside from it being extremely hard to find C++ developers these days, we’ve also run into numerous difficult-to-fix bugs that can be traced back to issues or design choices in our code, the underlying wxWidgets framework, and some versions of GTK (the choice of which, we typically have no control as we inherit GTK from the users’ Linux distribution). What’s more, the world is shifting to a web based model these days. Cloud deployments are becoming more widely used, as well as simple instances supplied through hosting providers. Users are getting used to being able to pickup their laptop or a tablet and do whatever they need to do without having to install software – open a browser, read and write email, build a spreadsheet or create a database!

Consequently, we're now starting to design pgAdmin 4. The plan is to build a single Python-based pgAdmin that users can either deploy on a web server or run from their desktop. It’ll be designed to “live query” databases rather than query and cache schema details like the current implementation (which was designed with use over a dialup connection in mind!), and we’ll be making the application much more task focused (and therefore usable) than it is at present, as well as implementing some of the oft-requested features that are hard to do in the current code such as direct data editing in the query tool and tabbed query windows. The new code will also be extensible from the ground up, allowing support for new object types or functionality to be simply “dropped in” and auto-discovered at runtime.

We're still in the proof of concept stage at the moment though, so there's nothing available to download just yet. But stay tuned as work kicks up a gear over the next 12 months!

Friday, 28 September 2012

PostgreSQL Conference Europe 2012 - 3 weeks to go!

There are less than four weeks until this years PostgreSQL Conference Europe, to be held at the Corinthia Hotel, Prague, organised by PostgreSQL Europe. This years event is shaping up to be our best ever, with a higher rate of attendee registrations than we've ever seen before, a bumper crop of talks from PostgreSQL users and contributors from around the world, and a keynote presentation from well known industry veteran, renowned author on all things SQL, and one of the original authors of the SQL89 and SQL92 standards, Joe Celko.

For more information on the event, visit the conference website.

This years conference will start with a day of training sessions (available at extra cost), places on which must be pre-booked when you register:
  • Joe Celko - A day of SQL with Celko: How often does an opportunity to learn SQL from the master come along? Not very often! Not to be missed if you're new to SQL or want to polish up your skills.
  • Bruce Momjian, Devrim Gunduz (EnterpriseDB) - Mastering PostgreSQL Administration: Hone your mastery of PostgreSQL, learning from Bruce, one of the project founders, and long term contributor and RPM maintainer, Devrim. This day long session starts with the basics, moving into more advanced administration and management topics.
  • Greg Smith, Peter Geoghegan (2nd Quadrant) - PostgreSQL Performance Training: Renowned PostgreSQL performance expert Greg and developer Peter spend half a day showing you how to get the best performance from your database servers.
  • Dimitri Fontaine, Simon Riggs (2nd Quadrant) - PostgreSQL Replication Training: PostgreSQL developer Dimitri and committer and author of features such as hot standby and point in time recovery, Simon, spend half a day discussing the streaming and synchronous replication, as well as failover and monitoring options available to PostgreSQL users.
  • Pavel Stehule - Implementace uložených procedur v PostgreSQL: Veteran contributor to PostgreSQL's stored functions languages Pavel shows you how to write stored procedures in PostgreSQL. Half day, presented in Czech.
  • Tomas Vondra (GoodData) - Čtení exekučních plánů: Long time contributor Tomas teaches the finer points of reading query (EXPLAIN) plans. Half day, presented in Czech.
Following the training sessions, we have three days of talks in three different rooms, with something for everyone - check out the conference schedule for details - and as always there will be social events hosted by our sponsors, and the hallway track to meet and chat with other PostgreSQL users and developers.

Convinced? Of course you are. Head over to the registration page and book your place now!

Monday, 26 March 2012

PostgreSQL Developer Meeting 2012

For the last few years I've organised a by-invitation meeting of the most active developers in the PostgreSQL community the day before PGCon's sessions start in Ottawa. This is undoubtedly the best time of the year to do this as the vast majority of PostgreSQL hackers attend this event - many only attend local events for the rest of the year.

As you can imagine, figuring out how to keep the meeting productive is not an easy task. Opening it up to anyone to attend is not really an option unfortunately, as we would be unlikely to be able to provide a suitable conference room for large numbers of people due to the cost of the space (which really needs to be comfortable as we'll be sitting around a table for nine hours or so) and the food. Budget aside; having too many people in attendance makes it very difficult to have a productive meeting, a problem we believe we had last year when we had our highest number of attendees to date (around 30) and what many felt was our least productive meeting to date.

This year, the Core Team spent quite some time discussing the upcoming meeting and came to the conclusion that we needed to do two things to make the meeting a success again:

1) We need to spend more time fine-tuning the agenda.

We try to leave the agenda up to the attendees - after all, it is their meeting. However, there is a tendency to include items that haven't really been thought through in enough detail in advance to make good discussion points. This year we're working to ensure the agenda contains only items that have had some amount of thought given to them already, but are yet to be resolved. Ideally, we suspect many of these topics will be things that have been discussed on the mailing lists already, but haven't come to any sort of conclusion. That happens from time to time, and face to face discussion often helps reach consensus on the way forward. What we don't want to discuss are the ideas that we all have after a couple of drinks at the pub, scribbled on the back of a beer mat, added to the agenda and then forgotten about. Those ideas are best saved for the evenings of the conference (with more beer)!

2) We need to reduce the number of attendees at the meeting.

This was the really hard one. We decided that we should reduce the number of attendees down from last years 30 to maybe 20 - 25. Two of those people are Josh Berkus and myself who are primarily there to take notes and chair the meeting respectively. The rest of the attendees come from a variety of backgrounds typically - committers, active developers, future (hopefully) active developers, past developers and senior staff from Postgres-friendly companies. This year we decided that the criteria for invitations would be "has this person been a significant contributor to PostgreSQL 9.2"? We've applied that test to all the proposed attendees, and whilst many people who received invitations are amongst the normal crowd, unfortunately it does mean that some people who have attended in the past were not sent invitations this year. If you're one of those people, please accept my apologies - hopefully this blog post will clarify why that is the case.

Of course, it is possible we have overlooked inviting people who have made significant contributions to the upcoming 9.2 release. If you're one of those people, please let me know if you would like to attend.

Regardless of whether or not you'll be at the developer meeting dear readers, I do hope to see you in Ottawa in May.