May 27th, 2013 |
Book, Review | 4 Comments
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.
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.
May 21st, 2013 |
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!
May 10th, 2013 |
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;
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
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
-- Only allow 'env' roles to be granted this way. That extension is
-- reserved for connection restrictions.
IF rolename !~ E'\_env' THEN
-- Don't allow the use of this function to grant superuser access!
PERFORM (WITH RECURSIVE rolecheck AS (
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)
WHERE rolname = rolename);
IF FOUND THEN
-- It's now safe to do the grant.
EXECUTE 'GRANT ' || quote_ident(rolename) || ' TO ' ||
RETURN (SELECT pg_has_role(username, rolename, 'MEMBER'));
$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.
April 26th, 2013 |
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.
- 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.
March 15th, 2013 |
Rant, Tech Talk | No Comments
Mid March is an interesting time of year in 2013. Now that the Galaxy S4 has been announced, there are really three major contenders for my itchy spending finger. Well, technically there are four, but one of them doesn’t really count, for reasons I’ll expound upon shortly.
Google Nexus IV
This is the phone that doesn’t count. One major benefit it has over all of the others, is that it gets updates directly from Google. Any Android advances are adopted in very short order, without any of the usual US carrier shenanigans. Unfortunately Google seems to believe 16GB ought be enough for anybody. Not only is 16GB the largest amount of memory in this particular phone, but since it doesn’t have an SD-card slot, it’s the most anyone will ever have.
Google believes The Cloud can supplant the need for large amounts of storage. It is wrong. Not only are some games on Google Play over 2GB in size, but carrier reception is hardly ubiquitous, and bandwidth is anything but generous. Anyone depending entirely on The Cloud for their entertainment will quickly find themselves tune-less or over their (likely 2GB) bandwidth cap in short order.
In a few years this may no longer be an issue, but now, in 2013, it is very much relevant and a limiting factor. Phones need at least 32GB of storage, and the ability to add more is often appreciated.
There are many—possibly millions—of people that can ignore this shortcoming, and the sales of the Nexus 4 have been booming. I however, do not count among their number. The other relative strengths or weaknesses of this device are heavily outweighed by this one missing feature, so I won’t even mention them. This phone doesn’t count in my list, and it’s a shame.
Sony Xperia Z
Sony? What? When did Sony make a good smart phone? Apparently they’ve woken up and decided they want to actually compete in the arena, and delivered what many agree is a very strong entry. 1080p screen, quad-core Snapdragon, 13 megapixel camera, a big 2330 mAh battery, SD-card slot, NFC chip, it’s mostly all there. Amusingly, they also made it waterproof, which is a big plus for all those accidental drops into puddles and the occasional toilet.
What’s not so great is that they didn’t include wireless charging, a strange thing to omit in a waterproof device. Needlessly opening up the USB port cover for charging could eventually wear it out. And though they actually have an SD-card slot, the phone itself only includes 16GB; there are no 32GB or 64GB versions. Oddly, it’s also only compatible with SD-cards up to 32GB. Again, 64GB cards are old news these days, and a strange thing to leave out.
The phone is also hilariously expensive at $850 off contract, and for all that, most reviews ding the screen for its relatively bad viewing angles. Anyone who watches media on the 5-inch screen while it’s on a kickstand may find that unacceptable. The battery life is also reportedly on the low end.
Yet a few gripes are what anyone would expect. All in all, it’s a device I could live with. That’s more than I could say about the Nexus 4.
Go home HTC, you’re drunk. You’ve already had a One S, a One X, and possibly everything in between. Whoever is in charge of your marketing and branding division should be dragged out into the street and shot. Why?
Because this phone is fucking beautiful, that’s why. It is hands-down the best looking Android phone I have ever laid eyes upon. The 4.7 inch-screen isn’t quite as large as Sony’s 5-inches, but it’s still 1080p. The two-tone machined aluminum shell with black accents is breathtaking, but it’s also functional. The top speaker grill obscures the notification LED so it isn’t so obtrusive. The stereo speakers are prominently on the front of the phone, and reviewers have gushed at how amazing they sound.
The power button on top? Ok, that’s somewhat odd. But it also doubles as an IR-blaster. Finally, a phone I could use as a TV remote! Jesus, why has it taken this long to add this? A couple well-designed universal remote apps could render remote juggling a thing of the past. It also has 802.11ac, the newest (draft) standard in wireless. Not really essential until compatible routers are more ubiquitous, but it’s a nice touch. They also opted for a larger camera sensor instead of simply higher resolution. The pictures totally blow away those from other recent phones. That said, they probably went too low with 4MP. What looks great on a phone may not look so well when printed or zoomed for editing. Anything larger than a 3×5 print will look grainy. Not really a problem for me, but some might be unpleasantly surprised when at the copy shop.
Like the Sony Xperia it has no wireless charging. No induction coil will really work effectively through the aluminum shell. And while it comes in a 32GB flavor, unlike the Nexus 4, it has no expandable storage. What may be worse: it can’t be disassembled to replace the battery if it goes bad. That aluminum shell with polycarbonate injection molding is effectively a beautiful, inaccessible slab.
Why do I care? The battery in my Galaxy S2 went bad. Not only did it not hold a charge, but the phone acted very oddly regardless of the reported power level. Eventually I returned the bloated ruin and received a replacement. With an HTC One, they’re most likely going to offer a replacement phone, and take the old one for refurbishing because it has to go back to a factory for a new battery. That, or I throw it away and buy a new phone. That kind of disposable planned obsolescence makes me exceptionally angry, especially considering how much the One is likely to cost off contract. At least the Nexus 4 is only a few screws away from a new battery.
So the awesome is tempered with some very real annoyances. And worse, even those who could forgive the permanent battery may never even know about the phone at all. HTC’s marketing budget was actually cut by 15% this year, and like I said, most people would naturally assume the One is an older version of the One X or some other HTC variant. HTC failed big-time in this regard, unless they start a new trend like what we see in the automotive industry. I mean, a Hyundai Sonata can vary greatly between model years, but its name never changes.
If that’s the direction they’re going, I wish them all the luck in the world. This endless stream of sequential numbering and nonsensical buzzword-rich names is long since tiresome and idiotic.
Samsung Galaxy S4
And finally, the most recently revealed entry in the Galaxy series. I could say a lot about Samsung, but their phone division is some kind of magical beast straight out of ancient mythology. It has every feature from all the phones I just mentioned, and then adds a few just for good measure.
Five inch screen? Yep. 1080p? Of course. 13MP camera? Why not? NFC chip? Duh. 2600 mAh battery? Holy crap! Wireless charging? It would be stupid not to. Barometer? Sure. SD-card? Is 64GB enough for ya? Internal memory? Up to 64GB, because Fuck You, every other phone. 802.11ac? No contest. A dual quad-core Exynos 5? You better believe it. 2GB RAM? Why use less. IR-blaster? Suck it, HTC. Less than 8mm thick? Anything thicker is only suitable to pave sidewalks. Replaceable battery? Only if you think a trunk is an essential feature in your car (it is).
And it goes on and on like that. The only real problem with the S4, is that it’s as ugly as balls. Seriously. I can hardly believe this is the same company that made the S2, which was as functional as it was good-looking. The S3 was a hideous Lovecraftian writhing horror by comparison. It’s sad to see Samsung continue that trend by only slightly revising that revolting design, especially after witnessing the ethereal perfection that is the HTC One. But we can’t have everything we want, and I’d rather have features I’d use than a phone to attract the drool of filthy passers-by.
It’s too soon to say how well the battery performs, but it’s bigger than what they put in the original Galaxy Note, a much larger device. And since it was just announced yesterday, there aren’t a lot of long-term reviews out there, but it looks like this is the top of the heap for people who don’t mind that it resembles regurgitated anus. While I would prefer something as classy as the HTC One, the S4 stomps all over it in terms of features. Provided it isn’t a buggy mess and the dev community provides some good ROMs, it’s very likely this will be my next device when it’s finally available in the US.
This is why Samsung sells a ton of phones and has become the top grossing Android manufacturer. It’s not just the endless buckets of cash they spend on marketing, but the fact they somehow cram every iota of functionality into their devices. People aren’t blind to that. HTC can make snide comments about it all day long, but until they can give us all the features Samsung does, they can shut the hell up.
And I’m sorry Sony, you really tried this time. I’m honestly shocked at how well the new Xperia compares to the S4. The fact they made a waterproof phone and still managed to add an SD-card slot is commendable. See that HTC and LG? It’s not impossible. But the Galaxy is just… more. Somehow, it always is. How? I have no clue. It should be impossible to be so consistently ahead of everyone else.
Honestly, HTC should be glad Samsung apparently hires drunken orangutans to design its phones. Does HTC actually want a good looking Galaxy device to compete with? Fuck no. How would HTC compete then? Still, HTC almost had me with the One. If not for the battery being firmly embedded in about five acres of aluminum, I may have even bought it despite having fewer features. It’s that enticing. Seriously, just fucking look at it. Why, Samsung?!
Oh well, the consumers are the winners here no matter what. I for one, love the competition. Come April, my S2 is going to be on Craigslist, and I’ll have to re-train my thumb for another half inch of real-estate.
« Older Posts
Newer Posts »