Server of the Beast

June 3rd, 2013 | Published in Uncategorized | No Comments


Hmmm… maybe I should reconsider rebooting my server until tomorrow.

Stupid maintenance...


Tags: ,

Review: Instant PostgreSQL Starter

May 27th, 2013 | Published in Book, Review | 4 Comments


Instant PostgreSQL Starter

As a newly minted Packt author, it makes sense that I might get a request to review one of their books from time to time. On this particular occasion, I have the opportunity to give a look at Instant PostgreSQL Starter by fellow PostgreSQL user and author Daniel K. Lyons.

I’ll be straight-forward with a giant caveat that I’m not the target audience for this booklet. I tried to read with the perspective of a new user since we’ve all been there once, but please bear with me if I break character.

Many new users will find that the examples given using pgAdmin are easy to follow and perform as expected. Users who are new to PostgreSQL likely don’t want to fiddle with the command-line for basic functionality, especially if they are coming from another database such as SQL Server, Oracle, or even MySQL. And for even more complex cases such as hstore, XML manipulation, or full-text searching, we get treated with function and view syntax to help abstract away some of the ugly or annoying syntax.

That said, the number of pgAdmin results were somewhat skimpy, especially when more advanced features are introduced. Seeing result output of some of these would have been nice considering the rather cumbersome and advanced syntax. A new user might have trouble fully understanding these, and if reading without following along, would have no basis for comparison. Additionally, Daniel’s adherence to pgAdmin extends only to using it as a connection method. When creating a user, table, or database, he prefers to use pure SQL instead of pgAdmin’s interface to create or modify these objects. Considering this book is for new users, and we have an entire section on basic SQL syntax for interacting with tables, why omit this?

Speaking of interacting with tables, for the newest of new users, Instant PostgreSQL Starter launches into a quick introduction of SQL syntax. Most readers can generally skip this section, but it’s good to know it’s included. Why? One commonly accepted aspect of marketing is to Get ‘em While They’re Young. If new users can read a simple explanation of SQL that speaks at their level while implying PostgreSQL is the natural extension of that concept, they’re more likely to equate the two. When I first started using databases in 1998, MySQL filled this role, so it’s great seeing material that treats PostgreSQL and beginning SQL as synonymous.

In that same vein, Instant PostgreSQL Starter picks a small set of nine features the author found particularly handy, and he spends some time delving into their use. Not only does this make another great form of marketing PostgreSQL, but these features are very practical. We learn about PostgreSQL-specific features such as pgCrypto, the RETURNING clause, hstore, XML, CTEs, full-text search, and everyday minutia like backup and restore, basic database and table sizing, and using EXPLAIN. But why only nine features? PostgreSQL has a whole matrix of functionality to choose from, and rounding up with another useful tidbit would have been appreciated.

Being more familiar with the database, I can’t help but wish Mr. Lyons considered his target audience in selecting these features. JSON for example, is mentioned in a note but otherwise ignored. Yet with Javascript libraries like node.js and JSON (JavaScript Object Notation) taking the web by storm, it would have been a perfect section to attract savvy users. This is especially true since PostgreSQL’s JSON performance rivals dedicated NoSQL JSON engines. And this is even before the enhancements expected in 9.3.

All in all, Instant PostgreSQL Starter is a little bit of everything a new user might want to know about what PostgreSQL can do, and how that might be accomplished. Instructions are clear, and largely a cut-and-paste affair, which lets users easily experiment with the syntax. This is especially important for things like full-text search, which still reflects somewhat unintuitive syntax even this late in PostgreSQL’s life-cycle. Having solid examples for all of these important features is a great way of satisfying new users. And since the book culminates with a lot of community resources and documentation links, new users are encouraged to explore the full power of their new database. And that, we can all support.


Tags: , ,

Winning (Free eBooks) is Everything

May 21st, 2013 | Published in Database, News, Tech Talk | 4 Comments


It occurs to me I forgot to congratulate the winners of the free ebooks. So without further ado:

  • SAB, who seems to host a nice blog geared toward server administration.
  • Stephan, who’s looking to improve existing strategies.
  • Jeff and his growing PostgreSQL cluster.
  • Pierre, who apparently has an experimental PostgreSQL backend for MySQL. Interesting.

Congrats to the winners. But more, I call upon them to pay it forward by contributing to the community, either by corresponding with the excellent PostgreSQL mailing lists, or maybe submitting a patch or two to the code. There’s a lot of ground to cover, and more warm bodies always helps. :)

Thanks again, everyone!


Tags: , , , , ,

Make pg_hba.conf Redundant by Using pg_hba.conf

May 10th, 2013 | Published in Database, Tech Talk | 2 Comments


Let’s face it, the pg_hba.conf file is a pain in the ass to use regularly. Sure, reloading the database will cause it to re-read this file, but with a lot of active users and frequent changes, this isn’t really tenable.

Luckily lurking deep within its bowels, PostgreSQL has a little-known feature that can easily be overlooked because it’s so humbly stated. Here’s the manual entry for pg_hba.conf for the user section:

Specifies which database user name(s) this record matches. The value all specifies that it matches all users. Otherwise, this is either the name of a specific database user, or a group name preceded by +. (Recall that there is no real distinction between users and groups in PostgreSQL; a + mark really means “match any of the roles that are directly or indirectly members of this role”, while a name without a + mark matches only that specific role.) Multiple user names can be supplied by separating them with commas. A separate file containing user names can be specified by preceding the file name with @.

The implications of this are staggering and should be shouted from the rooftops frequently and with much fanfare. But what part of that paragraph is the feature that has me raving about its awesomeness? The + decorator for a specified role.

Initially, it might occur to a DBA to simply take advantage of this ability to use existing roles and segregate access by implementing a few well-placed group lines into the file. Say we wanted to allow all DB developers to connect, and our local subnet had a range for desktop systems. We could do this:

# TYPE  DATABASE    USER          CIDR-ADDRESS        METHOD
host    all         +developer    10.10.0.0/16        md5

And viola! Instead of granting access to each individual person, anyone in the developer group could connect provided they had a password. Neat, eh?

Ah, but it goes much deeper than that.

What happens when we apply this to the entire file, and completely purge all individual user entries entirely? Even for automated or batch systems? We get the opportunity to build a connection policy enforceable by in-database methods. Instead of modifying the access file and reloading the database, GRANT and REVOKE become the only commands we’ll ever need.

Imagine we have our production environment and we’ve locked down the entire pg_hba.conf file from external access with this single line for our internal VPN:

# TYPE  DATABASE    USER          CIDR-ADDRESS        METHOD
host    all         +prod_env     10.0.0.0/8          md5

Now, being quite this permissive is probably not a good idea. In a real setup, the production system should only accessible from a very limited range of addresses. However, for the purposes of this discussion, it’s fine. Next, let’s create the prod_env group, and a user to grant it to:

CREATE ROLE prod_env WITH NOLOGIN;
CREATE USER foobar WITH PASSWORD 'testing';
GRANT prod_env TO foobar;

Now our foobar user can connect as often as he likes, and we didn’t have to touch anything external to the database after the initial configuration. Here’s where it gets fun. The foobar user has been naughty, and we’re kicking him out of production. Our prod environment is regularly copied into stage in redacted form, so it’s still OK for him to connect there. Let’s save ourselves some effort and add a stage_env group.

REVOKE prod_env FROM foobar;
CREATE ROLE stage_env WITH NOLOGIN;
GRANT stage_env TO foobar;

And in our stage environment, it would have a pg_hba.conf similar to what we have in production:

# TYPE  DATABASE    USER          CIDR-ADDRESS        METHOD
host    all         +stage_env    10.0.0.0/8          md5

Now the same user can exist in both environments, but only be able to connect to one. This kind of interleaving is easy to accomplish and the controls can be as fine or coarse as your imagination demands.

But it actually gets better!

Suppose our organization has a support team, who we clearly don’t want to give superuser access, but they want to regularly modify user rights. Well, we could grant them every group WITH GRANT OPTION for later distribution, but that’s not really ideal. How about a function they can use instead?

CREATE OR REPLACE FUNCTION grant_conn_role(
  username VARCHAR, rolename VARCHAR
)
RETURNS BOOLEAN AS
$BODY$
BEGIN

  -- Only allow 'env' roles to be granted this way. That extension is
  -- reserved for connection restrictions.

  IF rolename !~ E'\_env' THEN
    RETURN False;
  END IF;

  -- Don't allow the use of this function to grant superuser access!

  PERFORM (WITH RECURSIVE rolecheck AS (
    SELECT rolname
      FROM pg_authid
     WHERE rolsuper
    UNION 
    SELECT r.rolname
      FROM pg_authid a
      JOIN rolecheck c ON (c.rolname = a.rolname)
      JOIN pg_auth_members m ON (m.roleid = a.oid)
      JOIN pg_authid r on (m.member = r.oid)
  )
  SELECT 1
     FROM rolecheck
    WHERE rolname = rolename);

  IF FOUND THEN
    RETURN False;
  END IF;

  -- It's now safe to do the grant.

  EXECUTE 'GRANT ' || quote_ident(rolename) || ' TO ' ||
          quote_ident(username);

  RETURN (SELECT pg_has_role(username, rolename, 'MEMBER'));
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE EXECUTE ON FUNCTION grant_conn_role(VARCHAR, VARCHAR) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION grant_conn_role(VARCHAR, VARCHAR) TO support;

Now anyone in the support group can modify user rights as if they were a superuser. Of course, we plugged the obvious hole so support users can’t grant themselves a superuser capable role. But we also want this to only work with roles that fit a certain naming scheme. In this case, anything ending in _env is set aside for connection wrangling. You could just as easily use conn_ as a prefix instead, or any preferable nomenclature. Just modify the function to reflect the standard.

As DBAs, we want to do as little work as possible while simultaneously providing a secure and reliable system. Reloading database configs unnecessarily and doing all user management personally doesn’t really reflect that goal. We might as well use the tools the database provides to be lazy but still protect the environment.

With PostgreSQL, this is both easy and surprisingly powerful, all thanks to pg_hba.conf making itself redundant.


Tags: , ,

Free PostgreSQL Backup Book? Yes Please!

April 26th, 2013 | Published in Database, News, Tech Talk | 16 Comments


A little while ago, I wrote to the PostgreSQL general mailing list that I’d been approached by Packt Publishing to contribute a quick manual on doing PostgreSQL backups: Instant PostgreSQL Backup and Restore How-to. They’re the same guys who published Greg Smith’s PostgreSQL 9.0 High Performance book which everyone seems to swear by.

The goal of the backup book was to distill the PostgreSQL documentation, tools, and Wiki down to a collection of short step-by-step guides much like the O’Reilly nutshell series. A lot of the backup recipes we DBAs trade back and forth as a matter of course may not be so obvious, and I threw a couple tricks in for advanced users, to boot.

Well, here’s your chance to spring a free copy! The marketing folks have given the go-ahead to hold a giveaway, and have set aside four copies for lucky winners. A longer description of the How-to is on their site.

All they want to know is what you, as a prospective reader, find most interesting or potentially useful about the book. My comment section will be open until May 8th for replies along these lines. If you are selected, Packt will email you with information on how to get your free digital copy. If your comment catches our attention, you’re one step closer. If you want a print copy, they’re available from Amazon separately.

So remember:

  • Free book
  • What interests you about it?
  • Submit a comment
  • You’re entered

I look forward to forcing Packt to do some community service by handing out free copies of the book, and you should too. :)


Tags: , , , , , ,

« Older Posts

Newer Posts »