Category Archives: Code

…is slightly less straightforward than you might hope, but helpfully more flexible. For MS SQL Server, integrated security implies that windows user are magically also SQL users and that no password or username is needed to login. But also, that you can no longer choose which user you login as. Postgres is more configurable and more complex. You can specify which users use SSPI and which postgres user(s) each windows user can login as. You can specify, for instance, that you are allowed to use SSPI to login as the postgres superuser.

Here is how you can login with integrated security, as the user postgres, whilst still being able to login as a different user with a password.

Locate and open both your pg_hba.conf and pg_ident.conf files. I found them in C:\Program Files\PostgreSQL\data\pg96

In pg_ident.conf add a line to map your windows login to the postgres user named postgres. You can also add other users. Here’s what my lines look like:

psql -h localhost -U someotherusercreatedwithcreaterole
and you should be prompted for a password (unless you already mastered the pgpass.conf file)

Logging in without specifying a user name

You might expect that SSPI implies not having to specify a username. You would be wrong. Postgres still requires you specify a username when using SSPI, and, as above, allows you to choose which username.

You can however login without a username—with or without SSPI—simply by creating a postgres user (i.e., a role with LOGIN privilege) with your username. To let that user login with SSPI, you should still have a line in pg_ident.conf to map the SYSTEM-USERNAME yourname@yourmachinename to the postgres username yourname.

plpqsql script is separated from “pure” SQL in a way that T-SQL just doesn’t bother with. If you are writing scripts, plpqsql must either be inside a function or inside an ‘anonymous function block’, usually delimited by $$ or $somestring$; and Declare variables in one block before Begin :

In fact, functions are defined in a string, and the $$…$$ delimiters is a postgres syntax for specifying a string constant without having to escape any special characters at all. They can be used anywhere in code. Functions can be defined in other languages than plpgsql. See e.g. https://pgxn.org/dist/plv8/doc/plv8.html.

Variables: just use a plain identifier: myvariablename with no @ symbol or other decoration. BUT as a consequnce you must avoid variable names in a query that are the same as a column name in the same query. BUT BUT in Ado.NetCommands with Parameters, still use the @parametername syntax as you would for SQL Server

Postgres does function overloads, so to drop a function you must give the signature: Drop function functionname(int)

Whereas the T-SQLer does everything in T-SQL, other database systems use non-sql commands for common tasks. For working at a command-line, learn about psql meta-commands.. Start with \c to change database, \l to list object, and \? to list other meta commands.

Postgres have (I think) made more effort than MS to stay close to the SQL standards and as with T-SQL, the docs go into detail on deviations. But this means that much of your code for databases, schemas, users, tables, views, etc can be translated fairly quickly.

A company with an IT department on the 1st floor, and a marketing department on the 2nd floor, where the web servers were managed by the marketing department (really), and the back end by the IT department.

I was a developer in the marketing department. I could discuss and change web tier code in minutes. To get a change made to the back end would take me days of negotiation, explanation and release co-ordination.

Guess where I put most of my code?

Inevitably the architecture of the system became Webtier vs Backend. And inevitably, I put code on the webserver which, had we been organised differently, I would have put in a different place.

This is Conway’s law: That the communication structure – the low cost of working within my department vs the much higher cost of working across a department boundary – constrained my arrangement of code, and hence the structure of the system. The team “just downstairs” was just too far. That distance was composed of gaps & differences in priorities, release schedules, code ownership, and personal acquaintance.

Conway’s Law vs Distributed Working

Mark Seemann has recently argued that successful, globally distributed, OSS projects demonstrate that co-location isn’t all it’s claimed to be. Which set me thinking about communication in OSS projects.

In my example above, I had no ownership (for instance, no commit rights) to back end code and I didn’t know, and hence didn’t communicate with, the people who did. The tools of OSS—a shared visible repository, the ability to ‘see’ who is working on what, public visibility of discussion threads, being able to get in touch, to to raise pull requests—all serve to reduce the cost of communication.

In other words, the technology helps to re-create, at a distance, the benefits enjoyed by co-located workers.

When thinking of communication & co-location, I naturally think of talking. But @ploeh‘s comments have prodded me into thinking that code ownership is just as big a deal as talking. It’s just something that we take for granted in a co-located team. I mean, if your co-located team didn’t have access to each other’s code, what would be the point of co-locating?

Another big deal with co-location is “tacit” knowledge, facilitated by, as Alistair Cockburn put it, osmotic communication. When two of my colleagues discuss something, I can overhear it and be aware of what’s going on without having to be explicitly invited. What’s more, I can quickly filter out what isn’t relevant to me, or I can spontaneously join conversations & decisions that do concern me. Without even trying, everyone is involved when they need to be in a way that someone working in a separate room–even one that’s right next door–can’t achieve.

But a distributed project can achieve this too. By forcing most communication through shared public channels—mailing lists, chatrooms, pull request conversations—a distributed team can achieve better osmotic communication than a team which has two adjacent rooms in a building.

The cost, I guess, is that typing & reading is more expensive (in time) than talking & listening. Then again, the time-cost of talking can be quite high too (though not nearly as a high as the cost of failing to communicate).

I still suspect that twenty people in a room can work faster than twenty people across the globe. But the communication pathways of a distributed team can be less constrained than those same people in one building but separated even by a flimsy partition wall.