…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
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_ident.conf files. I found them 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 Chris@MyMachineName postgres
(In normal unix style, the columns are separated by any amount of space or tab).
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
- Restart the Postgres service, for instance with a powershell command
Restart-Service 'PostgreSQL 9.6 Server'
- Trying logging in as user
psql -h localhost -U postgres
- 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
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
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
Integrated Security in .Net connection strings
Having done the above I can now use
as connection string for the npgsql Ado.Net driver
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.
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 '.
NVarchar is needed, just
varchar. The default installation uses UTF-8.
- Date & Time: use
- 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
Declare variables in one block before
Select 'This line is plain SQL' ;
anumber integer ;
If 1=1 Then
Raise Notice 'This block is plpgsql' ;
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
Parameters, still use the
@parametername syntax as you would for SQL Server
Create or Replace Function procedureName( forId int, newName varchar(20) )
Returns int -- use returns void for no return
Insert into mytable (id,name) values (forId, newName) On Conflict (id) Do Update Set Name=NewName ;
- 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.
Serial. For more complex options, Postgres uses SQL sequences.
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
- The Nuget package for Ado.Net, including for .Net core, is
- Scan these
Converting T-SQL to Postgres
Here’s my initial search/replace list for converting existing code:
|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
||websearch Create Extension “uuid-ossp” and then use uuid_generate_v1() or uuid_generate_v4()
||Create or Replace Function