PostgreSQL Roles

Today I got involved in a discussion on the General mailing list about using ROLE in Postgres.  I realized this is a topic that can get confusing in a hurry and I thought I might try to clear things up, in my mind if nothing else.  Part of the confusion arises from a historical carry over. In versions of Postgres prior to 8.1 there where USER and GROUP.  (An aside, I found this out by using this : Feature Matrix, a very handy tool. ) These where distinct and operated somewhat like their OS equivalents, in that users logged in and then could be part of a group. In 8.1+, in other words all current community supported versions, USER and GROUP have been replaced by ROLE.  The confusion arises because while the current underlying mechanism is based solely on ROLE, the USER and GROUP commands have been maintained for backwards compatibility. So when you use the SQL command CREATE USER in 8.1+ you are just creating a ROLE with the clause LOGIN  whereas CREATE GROUP is just CREATE ROLE which by default has the clause NOLOGIN. For my own personal sanity I just use the CREATE ROLE command and pretend USER and GROUP do not exist. Since we already done the rabbit hole I might as well mention the psql \d commands that apply as well. Again for historical reasons these are \du and \dg which at one time listed users and groups respectively. Now they both return the same thing, a list of the roles. The thing to remember is that unless you are working with a pre 8.1 database you are working with some variation of a ROLE.

So what does a ROLE get you? As was implied above a ROLE can have the characteristics of a user or group. For my own use I follow the pattern used by the current CREATE USER/GROUP commands where a ROLE with LOGIN set is a taken to be a user and one with NOLOGIN is equivalent to a group.  Note: This is just a convenient way of viewing things and you will not see a ROLE defined that way when you list them. Assuming that pattern then it possible to create group like ROLES and then assign user like ROLES membership in them. For instance:

CREATE ROLE accounting;
CREATE ROLE production;


CREATE ROLE aklaver LOGIN IN ROLE accounting,sales;
CREATE ROLE daffy_duck LOGIN  IN ROLE production;
If you want to change role membership after CREATE ROLE then you will need to use the GRANT or REVOKE commands.

Where this pays off is in object permissions. Give the ‘group’ roles permissions to their respective objects and then add or drop ‘users’ to the ‘groups’ as needed. This way you can maintain access flexibility without continually  having to change permissions on objects.

I have touched on just the bare basics, this is a subject that covers a lot of ground. To really learn it requires multiple passes through the documentation and plenty of hands on testing.

Quantas gripe sheet

Ran across this years back, it still gives me a chuckle:

After every flight, Qantas pilots fill out a form, called a “gripe sheet,” which tells mechanics about the problems with the aircraft.

The mechanics correct the problems, document their repairs on the form, and then pilots review the gripe sheets before the next flight.  Never let it be said that ground crews lack a sense of humor.

Here are some actual maintenance complaints submitted by Qantas’ pilots (marked with a “P”) and the solutions recorded (marked with a “S”) by maintenance engineers.

By the way, Qantas is the only major airline that has never had an accident.

P: Left inside main tire almost needs replacement.

S: Almost replaced left inside main tire.

P: Test flight OK, except auto-land very rough.

S: Auto-land not installed on this aircraft.

P: Something loose in cockpit.

S: Something tightening in cockpit.

P: Dead bugs on windshield.

S: Live bugs on back-order.

P: Autopilot in altitude-hold mode produces a 200 feet per minute descent.

S: Cannot reproduce problem on ground.

P: Evidence of leak on right main landing gear.

S: Evidence removed.

P: DME volume unbelievably loud.

S: DME volume set to more believable level.

P: Friction locks cause throttle levers to stick.

S: That’s what they’re for.

P: IFF inoperative.

S: IFF always inoperative in OFF mode.

P: Suspected crack in windshield.

S: Suspect you’re right.

P: Number 3 engine missing.

S: Engine found on right wing after brief search.

P: Aircraft handles funny.

S: Aircraft warned to straighten up, fly right, and be serious.

P: Target radar hums.

S: Reprogramming target radar with lyrics.

P: Mouse in cockpit.

S: Cat installed.

And the best one for last…

P: Noise coming from under instrument panel.  Sounds like a midget pounding on something with a hammer.

S: Took hammer away from midget.

First Postgres post

This is more of a test post than anything. A way of checking that I have my feed to Planet PostgreSQL set up right. The point being to have only Postgres related posts come through, not my thoughts on cat juggling(thanks Steve Martin) or other such topics. Should the system work I will follow with a post on Streaming replication/Hot Standby/WAL archiving. As Bruce Momjian is aware I have been experimenting with SR.  That has led me to trying to figure out the relationships between the previously mentioned components.  More thoughts on that later.

Life Rule 2

“Easy is difficult.”

Subtitled the tale of two diesels. The principals:

Woods & Copland    1942 Cat

The machines pictured are two I had personal experience with. So what do a couple of pieces of heavy equipment have to do with the rule? In this case starting the beasts . The old Cat had a multiple stage means of starting. The primary motor was a diesel, but  to start it you first had to deal with a gas ‘pony’ motor. A rough outline; crank the pony motor, let it warm up, use hand clutch to have pony motor spin diesel, spin diesel for awhile, diesel coughs to life, let diesel warm up, do work.  For a more detailed account see ( and read the part ‘Starting an Old Cat’. Compare this to starting the Woods & Copland tractor; turn key . From the end user view point starting the tractor is easy compared to turning over the Cat. To make it easy though was difficult. The reason the Cat had the pony motor was that the electrical starter motor/battery combination’s of the day were not strong enough to spin the diesel motor. Over the years much work was done on this to get to the system that was on the tractor. Even so this particular tractor had a wrinkle. The earlier models from Woods and Copland used air starters to spin the 320 HP motor because 12V starters where not powerful/reliable enough. To make an electrical start system work this model actually had a 24V starter. The wrinkle was that the rest of the electrical system was 12V. How did this work you ask? Enter a electronic switch over module. The tractor had two 12V batteries. When you turned the key to the start position it switched the batteries into a series connection effectively creating a 24V battery. It also isolated the rest of the electrical system from the 24V. When you let the key return from the start position it put the batteries back into a parallel connection and reconnected the non-starter electrical system. Why do all this? 24V components are a lot more expensive then 12V ones and harder to find. This method was a compromise between power when you needed it and economy elsewhere.  Getting back to the rule. There is easy and there is easy. Many times what is presented as easy it really a veneer over a difficult process, think usability studies. Most I have seen are about hiding the complexity from the end user. Said end user hopefully comes away saying ‘that was easy’. What should not be lost sight of though is that the underlying process is still difficult.  Hence tech support.

Life Rule 1

Part of me wanted to do random(life_rules), but then the Dutchman in me kicked in. So I will start at the beginning.

“The good will take care of itself, its the bad you have to plan for. ”

Nothing too original, this is the basic premise of insurance. It is one of those things that people, myself included, often pay lip service to without really following through on. The importance of the follow through has more to do with state of mind than anything. Too many times I have seen manageable problems descend into crises solely because the people involved never entertained the thought that something could go wrong. The act of planning prepares you for the unfortunate. Even if you do not have a fully fleshed out plan, the mere act of anticipating the need is often sufficient to avert a situation from spiraling out of control. An example from my greenhouse days, no matter the amount of preventative maintenance you do, a boiler will choose  3 AM on the coldest night of the year to take a break.  A boiler is the intersection of several systems;  solid state controls, electrical wiring, vent plumbing, water plumbing and gas plumbing. Further, modern boilers are shrines to safety and have more interlocks between these systems than you can imagine. Coming up with a plan that deals with every eventuality is possible but ultimately counter productive.  Too much time spent studying the plan, not enough solving the problem. The key is to have a skeleton troubleshooting scheme either in your mind or on paper, preferably both. To complement this, sufficient tools and materials. I am using an expansive definition of tools to include written matter, such as control schematics. Materials would be common replacement parts i.e. flame sensor,thermocouple, relays,etc.  With the above in hand it is possible to bring a boiler back to life under trying circumstances. Truth compels me to also acknowledge that when ever possible I tried to hedge my bets by having redundant heat sources available.  Nothing like having a couple of unit heaters cranking along to lower the blood pressure a few notches. All part of the planning process. It is easy to get overly pessimistic if you carry this rule to the extreme. As in many things moderation is in order. Where that balance point is,  comes with time in the School of Hard Knocks. Taking a long walk down a country road because you had a flat tire, a spare tire  and no lug wrench is a tremendous learning experience.

Starting out

My attempt at extended thought on Facebook  ran into a character limit for status messages.  I have thought about cranking up a Blog for some time now;  the Facebook  limit has motivated me to start.  Per the title of this site the entries will cover a spectrum of topics.  To get a sense of where the ideas come from, some background. I am at heart a scientist. I have a BS in Fisheries Science and an MS in Environmental Science and my father is Geologist. Science is what I have known since I can remember.  That being said, it is not what I do now. At this point in time I am a duly licensed General Contractor in the  state of Washington. The reality is that I am a glorified handy man. The path to this has been anything but straight.  In roughly chronological order the stops have been– grocery store stocker, catfish farmer, crop duster loader, monument(headstones) installer, greenhouse worker, delivery driver, greenhouse maintenance supervisor, landscaper, retail nursery sales, budding software developer, self employed contractor.  Basically a testament to a restless mind.  The good is that I have learned a lot, the bad is it involved starting over many times. There are many lessons and stories to tell and over the course of time I will cover them one way or another. For now though I will stick to what I call Adrians Rules of Life. There are five and like most things they are not necessarily original to me.   They  do however seem to encapsulate my experiences. With out further ado the rules, further detail to follow:

1) The good will take care of itself, its the bad you have to plan for.
2) Easy is difficult.
3) Its all the same problem.
4) The more I know the less I know.
5) Don’t depend on a negative.