May 21st, 2013 |
Published in
Database, News, Tech Talk | 1 Comment
It occurs to me I forgot to congratulate the winners of the free ebooks. So without further adieu:
- 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 |
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.
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.
March 15th, 2013 |
Published in
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.
Sorry Google.
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.
HTC One
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.
Final Thoughts
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.
December 27th, 2012 |
Published in
Contemplation, News | No Comments
About a week ago, I was prescribed Lexapro. While this may not be the stuff I’m on long-term, it’s still long overdue for reasons obvious to anyone who knows me.
What I currently find most interesting about it however, is actually related to a dream I had last night. My dreams tend to be very vivid and numerous, though sometimes they follow a theme or storyline. Last night, there was one particular scene I recall with such clarity, it’s almost difficult to accept I wasn’t awake. Clearly I was asleep, and obviously it didn’t happen, but the fact it’s burned into my long-term memory is very strange.
In this portion of the dream, I was about to take some of my Lexapro. For the first few days, the doctor wanted me to take half doses, and my sleeping brain still had a firm grasp of this constraint. But alas! Upon pouring the pills into my hand, I accidentally dropped them all on the floor. Did I mention I was at work? And for some reason, work in my dream had shag carpeting? While bent over and putting all the pills back into the bottle, I was worried I wouldn’t get them all. So worried in fact, I was basically just shoving various floor detritus into the bottle. I even picked carpet fibers from one of the split tablets; I didn’t want to lose any!
This included some rotten vegetables (why?) that were also tangled up in the carpet. Realizing my mistake, I carefully extracted the vegetables from the bottle, but then noticed the pills were gone! What happened to them? They were dissolved by the juices of the rotten produce, which was now all over my hands. Somehow the tablets I had cut in half—the whole reason I had to pour them into my hand—had become capsules and also dissolved into the goo. The new worry of course, was that I had overdosed by osmosis.
Work was having a big party that night, but of course I had to leave because I needed to get my prescription replaced. Leaving the party was partially a relief, because of course I hate large gatherings of people. And wouldn’t you know it? I forgot the bottle, so I had to go back into work and retrieve it before I could actually leave.
Now, the dream itself had a few other elements I somewhat recall, but details are sketchy and I’d be too tempted to fill in missing pieces. The part about dropping everything, picking through the carpet, and then having Lexapro-infused veggie goo all over my hands was extremely vivid. I didn’t sleep well last night in general, waking up a couple times and then just being awake for no reason, so it’s good to know I got some sleep. But the fact my dreams centered on a bunch of worrisome BS is hardly relaxing.
I now realize I do that quite often. Some of my dreams are grand adventures, but another portion of them are just a bunch of worrying about things. It’s ridiculous, really. My only real hope is that the Lexapro isn’t contributing to my periodic insomnia. That’s really the last thing I need right now.
Until Tomorrow
