Category Archives: Code

Software Development

‘bash\r’: No such file or directory. Or, editing unix files on a Windows machine

'bash\r': No such file or directory

What does that mean, you ask yourself? It usually means you are editing *nix script files on a windows system, and then running the files on a *nix machine. For instance in docker, or a VM. Your solution is to use a text editor that allows you to save with unix line endings. `Notepad++` calls it, very reasonably, `’EOL conversion’`. Sublime text calls it `View->Line Endings`.

A further complication if you are using git is that it keeps getting reset back to windows. You could fix this with `git config core.autocrlf false` but that may have other consequences which you don’t want to bother with.

Postgres : Using integrated security on Windows on localhost

…is slightly less straightforward than you might hope. 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.

  1. Locate and open both your pg_hba.conf and pg_ident.conf files. I found them in C:\Program Files\PostgreSQL\data\pg96
  2. In pg_ident.conf add a line to map your windows login to the postgres user named postgres. Here’s what my line looks like:
    # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
    MapForSSPI     [email protected]    postgres

    (In normal unix style, the columns are separated by any amount of space or tab).

  3. In pg_hba.conf, add lines that allow user postgres to login with integrated security, whilst still allowing all other users to login with passwords:
    # TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
    # IPv4 local & remote connections:
    host    all             postgres        127.0.0.1/32            sspi 	map=MapForSSPI
    host    all             all             127.0.0.1/32            md5
    host    all             all             0.0.0.0/0               md5 
    # IPv6 local connections:
    host    all             postgres        ::1/128                 sspi 	map=MapForSSPI
    host    all             all             ::1/128                 md5
  4. Restart the Postgres service, for instance with a powershell command
    Restart-Service 'PostgreSQL 9.6 Server'
  5. Trying logging in as user postgres:
    • psql -h localhost -U postgres
  6. Trying logging in as some other user:
    • 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 [email protected] to the postgres username yourname.

Integrated Security in .Net connection strings

Having done the above I can now use

"Host=localhost;Database=MyDb;Integrated Security=True;Username=postgres"

as connection string for the npgsql Ado.Net driver

Reference

https://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html and subsequent pages on Authentication methods and the pg_ident.conf file.

Postgres quick start for MS SQL / T-SQL developers

After 17 years on T-SQL I’m at last on my first project with Postgres. Here are the first things I needed for the transition:

  • Do this first: https://wiki.postgresql.org/wiki/First_steps as it answers most of your first questions about connections, databases, using roles for logins, users and groups, and so on.
  • 'String concatenation is done with ' || 'a double pipe '.
  • No NVarchar is needed, just varchar. The default installation uses UTF-8.
  • Date & Time: use timestamp for datetime/datetime2
  • Read https://www.postgresql.org/docs/9.1/static/datatype-datetime.html for more on dates, times and intervals.
  • Use more semicolon terminators If ... Then ... ; End If ;
  • 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 :
    Select 'This line is plain SQL' ;
    Do $$
    Declare
      anumber integer ;
      astring varchar(100);
    Begin
    If 1=1 Then
     Raise Notice 'This block is plpgsql' ;
    End If;
    End $$ ;
    Select 'This line is SQL again' ;
    

    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.Net Commands with Parameters, still use the @parametername syntax as you would for SQL Server
  • Use Function not procedure:
    Create or Replace Function procedureName( forId int, newName varchar(20) )
    Returns int -- use returns void for no return
    As $$
    Declare 
      localvariable int;
      othervariable varchar(10);
    Begin
     Insert into mytable (id,name) values (forId, newName) On Conflict (id) Do Update Set Name=NewName ;
    End $$;
  • Ooh, did you notice how postgres has a really simple upsert syntax?
  • 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.
  • Replace Identity with Serial. For more complex options, Postgres uses SQL sequences.
  • Replace Select top 1 ... with Select ... Fetch First 1 Only.
  • role covers all of logins (Create Role ... Login) and database users (Grant Connect on Database ... TO ...) and also covers groups.
  • The Nuget package for Ado.Net, including for .Net core, is Npgsql
  • Scan these

Converting T-SQL to Postgres

Here’s my initial search/replace list for converting existing code:

Search Replace
@variableName _VariableName
NVarChar VarChar
Identity Serial
UniqueIdentifier uuid
Raise Raise Exception
Print Raise Notice
Insert Into table Select … Insert into Table (COLNAMES) Select …
If Then Begin … End Else Begin … End If Then … ; Else … ; End If ;
Create Table #Name Create Temporary Table _Name
NewId() websearch Create Extension “uuid-ossp” and then use uuid_generate_v1() or uuid_generate_v4()
Alter Function|Procedure Create or Replace Function

Conway’s Law & Distributed Working. Some Comments & Experience

The eye-opener in my personal experience of Conway’s law was this:

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.

References