Appears to be irritatingly missing. So I did https://github.com/chrisfcarroll/RequireClaimAttributeAspNetCore
'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 GUI 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`.
Your commandline solution in a bash shell on macos is
sed -i.bak $'s/\r//' * and on linux the same but you don’t need the ansi-C quoting:
sed -i.bak 's/\r//' *
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.
…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
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 for editing two files: your
pg_ident.conffiles. Find them both in the same directory in e.g.
pg_ident.confadd a line to map your windows login, in
[email protected]format, to the postgres user named
postgres. You can also add other users. Here’s what my lines look like:
# MAPNAME SYSTEM-USERNAME PG-USERNAME MapForSSPI [email protected] chris MapForSSPI [email protected] postgres
(In normal unix style, the columns are separated by any amount of space or tab).
pg_hba.conf, add lines that allow user
postgresto login with integrated security, whilst still allowing all other users to login with passwords. Again, you can add lines for other users too. Don’t forget to put lines targetting specific users above the catchall lines otherwise they will never be reached.
# TYPE DATABASE USER ADDRESS METHOD #== lines for specific users for SSPI (or anything else) BEFORE the catchall lines == # IPv4 local connections for SSPI: host all postgres 127.0.0.1/32 sspi map=MapForSSPI host all chris 127.0.0.1/32 sspi map=MapForSSPI # IPv6 local connections for SSPI: host all postgres ::1/128 sspi map=MapForSSPI host all chris ::1/128 sspi map=MapForSSPI #=================================================================================== # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256
- 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—if there is a postgres
user (i.e., a
LOGIN privilege) with your Windows username (just the name, without the @machinename).
By combining this with the SSPI map above you can then login without typing username or password.
Integrated Security in .Net connection strings
Having done the above I can now use either of
as a 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.
Why does the title of this post say ‘localhost or AD domains’? Because SSPI only works on Windows; and only on either localhost, or a Windows Domain environment as per https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows
Which brings us to the alternative that does work remotely without Domain servers: putting passwords in the
Postgres ‘Passwordless’ Login
Storing passwords in plaintext on a windows machine is largely a no-no in most peoples eyes. Unixland is more accepting of it, perhaps because they habitually expect file permissions to deny access to unauthorised users. And don’t expect to have virusses scanning their machines.
psql.exe on Windows will look for a
%appdata%\PostGres\pgpass.conf file (or
$env:AppData\PostGres\pgpass.conf for PowerShellers) and will parse lines in this format:
See https://www.postgresql.org/docs/9.1/static/libpq-pgpass.html for some wildcard options such as
Your plaintext password is then protected by Windows file permissions. Which should be fine for passwords to non-production servers.
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
rolesfor logins, users and groups, and so on.
'String concatenation is done with ' || 'a double pipe '.
NVarcharis needed, just
varchar. The default installation uses UTF-8. In fact the usual idiom is to use the
texttype, because omitting the length-specifier performs marginally better on postgres.
- Date & Time: use
- Read https://www.postgresql.org/docs/9.1/static/datatype-datetime.html for more on dates, times and intervals.
- Use semicolon terminators nearly everywhere.
If ... Then ... ; End If ;T-SQL doesn’t need them but Postgres demands them.
plpgsqlscript is strictly separated from “pure” standard
SQLin a way that T-SQL just doesn’t bother with. If you are writing scripts,
plpgsqlmust either be inside a
functionor inside an ‘anonymous function block’. It is always delimited by
Declarevariables in one block before
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, Postgres functions are defined in a string. The $$…$$ delimiters is actually a postgres syntax for a literal string constant anywhere in code, in which you don’t have to escape any special characters at all. 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
@symbol or other decoration. BUT as a consequence 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
@parameternamesyntax as you would for SQL Server
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 the ‘Create or Replace’ syntax, and how Postgres has a really good Upsert syntax –
Insert On Conflict Update?
- 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
\cto change database,
\lto list databases,
\dto list relations, 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.
Serialor from Postgres 10 you can use the standard-but-wordy
GENERATED BY DEFAULT AS IDENTITY. For more complex options, Postgres uses SQL sequences.
- Postgres replaces logins and database users and groups with a single concept:
role. Permit a role to login with the With Login clause when you
Create Role MyNameHere With Login. Grant it access to a database with Grant Connect:
Grant Connect on Database dbname TO roleName. Make it a group with – well, it already is a group. Add other roles to it with
Grant Role1 to Role2. I think this approach turns out to be much easier both to evolve and to understand and manage.
- 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:
but stay with @variable for .Net clients using
||First websearch for
Usually you will connect to postgres specifying a username. The
psql.exe commandline tool will default it to your windows username. You can avoid passwords in scripts that use
psql by putting them in the
pgpass.conf file). If you want to set up integrated security, the limitation is that for computers not in a Domain it only works on localhost. The instructions at Postgres using Integrated Security on Windows take about 5 minutes for the localhost case, and include a link to the extra steps for the Domain case.