Friday 30 July 2010

Cleaning up the Oracle mess

I've been caught off-guard by the number of news stories that have been posted about Oracle turning off their Solaris boxes that participated in the PostgreSQL Buildfarm and the fact that I've been working to replace them. I think most of us in the community assumed it was going to happen sooner or later - Sun were contributors to PostgreSQL, but once they bought MySQL I think it was the beginning of the end of their contributions to the project. Being acquired by Oracle was the solid-gold nail in the coffin.

Anyway, just to clarify a few points in some of the articles I've seen:
  1. My employer and corporate contributor to PostgreSQL, EnterpriseDB is providing the hardware for the new buildfarm members. We try to give back to the community wherever we can, and this is one thing that we can do quite easily.
     
  2. So far I've enabled two Solaris SPARC buildfarm members, one running the Sun Studio compiler, and one using GCC. There are equivalent Solaris Intel members being setup at the moment, as well as a couple of Windows 7 ones using VC++ 2008 32 and 64 bit.
     
  3. Contrary to reports and what might be inferred from the short bio on my blog, I actually work for EnterpriseDB on a full time basis. The "in my spare time I work for..." bit was my poor British attempt at humour. Sorry 'bout that - it won't happen again!
Well, that's about it I guess. We now return you to your scheduled programming.

Wednesday 28 July 2010

Postgres, Passwords and Installers

By far the most common issues we see reported with the "one-click" PostgreSQL installers that we build here at EnterpriseDB are password related. In this post I'll explain what the passwords are, why we need them, and how to reset them.

Superuser Password

The Superuser Password is used by Postgres to secure the built-in "postgres" superuser account in the database itself. This is the only account found in a fresh installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdmin, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD 'VeryVerySecret';


The superuser password is not required to upgrade Postgres to a newer version, however it will be required if you install certain StackBuilder modules, such as PostGIS or any of the PHP applications that are available. The password is required for these installers because they connect to the database server and create databases and other objects required to run the software.


Service Password

The service password is the one that tends to confuse people. Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called "postgres". On Unix-like operating systems such as Linux and Mac OS X, the account is setup without a password and users generally never need to worry about it again.

On Windows however, things are slightly different. In a normal installation of Postgres we'll setup the service account on Windows to use the same password that we use for the superuser account (expert users can override this behaviour using command line options for the installer). We need to do this because Windows requires that service accounts have secured passwords.

Note: If you change the superuser password in the future, that does not change the service password as well.

When you upgrade Postgres on Windows, in order to re-install the service we need to know the service password which is why the installer will require that it be re-entered correctly.

If you have forgotten the password, you can reset it on the command line (there are GUI tools that can be used as well, but they are not available on all versions of Windows). Start a command prompt, and then enter a command like:

net user postgres *

Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on "Command Prompt" in the Start menu and choose "Run as administrator" from the context menu.

You will be prompted to enter and confirm a new password for the user. If you do this, you must then also update the password in the service configuration for any Postgres or related services (such as pgAgent or pgBouncer) that may use the account, or expect them to fail to start at the next reboot. You can do this using the Services management console which can be found in the Administrative Tools folder. Just right-click each service, select Properties, and enter the new password and click OK.

We get a lot of people asking us to reset their service password, as they mistakenly think it's related to either their Postgres Community Login, or their account on the EnterpriseDB website. It's not - and we cannot change it for you!

Uninstallation


If you uninstall Postgres, the service account will not be removed from the operating system. This is because each individual uninstaller has no way of knowing if you are using the service account with other packages or installations. This isn't a huge problem unless you come to reinstall Postgres at a later time and realise you've completely forgotten what the password was set to - in which case you can reset it as shown above. If you prefer to remove the account, you can use a command like:

net user postgres /delete

Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on "Command Prompt" in the Start menu and choose "Run as administrator" from the context menu.

Summary

So to recap, we have Superuser passwords which are used to secure the database server's superuser account, and on Windows a service password to secure the operating system service account used by Postgres. Accounts use the same username (postgres) and the same password in a normal installation, but either password can be changed independently of the other post-installation. Both passwords are specific to your own machine, and can be changed (or the service account deleted) as shown.

For a walkthrough of the installation process, please see this guide.

If you need any further assistance having read this post, please use the pgsql-general@postgresql.org mailing list, or the EnterpriseDB Forums.

Thursday 1 July 2010

Postgres Testimonials

Those of you who attended PG East in Philadelphia earlier this year may recall that we were recording user testimonials on video. Well, we've finally got a bunch of those edited and ready for public consumption - you can find them on the EnterpriseDB YouTube channel.

These videos are ideal material to help persuade your boss or co-workers to adopt Postgres - for example, this video features Jim Nasby talking about how Enova Financial managed $1 billion worth of loans last year using Postgres, having previously scrapped MySQL after just a few months of use.



Check them out. Spread the word.

SQL Injection attacks

There was another malicious attack targeting websites running Microsoft Internet Information Server (IIS) recently. I would imagine that the vast majority of website developers and DBAs are aware of SQL Injection attacks and how to avoid them - primarily, by using nothing but parameterised queries in your code - and a large percentage of those have probably seen the excellent XKCD comic strip that illustrates (yeah, yeah, pun intended) the point nicely.

It never hurts to have a belt-and-braces approach to these things though, which is why version 9.0 of our Postgres Plus Standard Server and version 8.4 of Advanced Server will include a new module called SQL/Protect. This is a relatively small plugin to Postgres prototyped by Jim Mlodgenski, the Chief Architect here at EnterpriseDB which helps protect against these sort of attacks. It uses a number of techniques, such as detection of tautology in WHERE clauses of your SQL to try to detect and prevent injection attacks. It can also run in a learning mode in which it will monitor your web app and learn the usage patterns of the database so it can prevent anything out of the ordinary being executed.

Look out for it in our upcoming releases.