Amazon Web Services carefully explain how to use a private docker image in ECS
, the Amazon container service. But fails to mention what repository URL to use for a dockerhub public repository. The answer is … none at all. Just the same as the docker CLI, if you specify just namespace/imagename
for a container image, ECS will pull it from dockerhub.
Postgres : Using Integrated Security or ‘passwordless’ login on Windows on localhost or AD Domains
…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 for editing two files: your
pg_hba.conf
andpg_ident.conf
files. Find them both in the same directory in e.g.C:\Program Files\PostgreSQL\data\pg96
orC:\Program Files\PostgreSQL\10\data\
- In
pg_ident.conf
add a line to map your windows login, inuser@machinename
format, to the postgres user namedpostgres
. You can also add other users. Here's what my lines look like:# MAPNAME SYSTEM-USERNAME PG-USERNAME MapForSSPI chris@YOURMACHINENAMEHERE chris MapForSSPI chris@YOURMACHINENAMEHERE postgres
(In normal unix style, the columns are separated by any amount of space or tab).
- In
pg_hba.conf
, add lines that allow userpostgres
to 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
postgres
: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 thepgpass.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—if there is a postgres user
(i.e., a role
with 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
"Host=localhost;Database=MyDb;Integrated Security=True;Username=postgres"
"Host=localhost;Database=MyDb;Integrated Security=True;Username=chris"
as a 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.
Caveats
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 pgpass.conf
file.
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:
hostname:port:database:username:password
See https://www.postgresql.org/docs/9.1/static/libpq-pgpass.html for some wildcard options such as
localhost:*:*:chris:mydevpassword
Your plaintext password is then protected by Windows file permissions. Which should be fine for passwords to non-production servers.
Postgres quick start for SQL Server / T-SQL Developers
After 17 years on T-SQL
, I at last started working on projects using Postgres. Here are the first things I needed for the transition.
- Do this first: https://wiki.postgresql.org/wiki/First_steps as it answers first questions about connections, databases and login.
- Postgres replaces logins and database users and groups with a single concept:
ROLE
.- Permit a
ROLE
to login with the With Login clause when youCreate
orAlter
it: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 works out well both for evolving and managing users & groups.
- Permit a
- No
NVarchar
is needed, justtext
. The default installation uses UTF-8.Text
is the usual idiom overVarChar(n)
, allegedly it performs marginally better. 'String concatenation is done with ' || ' a double pipe '
.- Date & Time: use
timestamp
fordatetime/datetime2
. Read Date/Time Types 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. - Before version 11, use
Function
notProcedure
(usereturns void
if there is no return value). From version 11, you canCreate Procedure()
but note brackets are needed even for no parameters. A Procedure is called with CALL. - A typical definition:
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)
- Function and Procedural code is strictly separated from “pure”
SQL
in a way that T-SQL just doesn't bother with, and is typically written inplpgsql
script.Declare
variables in one block beforeBegin
. For interactive work you can use an Anonymous Function block starting withDO
: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 $$ delimiter is not a special syntax for functions, it is the postgres syntax for a literal string constant in which you don't have to escape any special characters at all. So that's handy for multiline strings and quotes.
- Functions can be defined in other languages than plpgsql. For javascript, for example, see https://github.com/plv8/plv8.
- If you don't need variables other than parameters, or control statements you can write a routine in pure sql by specifying:
Create or Replace Procedure ProcName(Date onDate) Language Sql As $$ Select * from LatestNews where PublishTimeStamp::Date = onDate $$;
- The double colon :: is the cast/conversion operator.
- 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 databases,\d
to list relations (ie tables), and\?
to list other meta commands. - Variables: are only available inside plpgsql (or other language) code, not in plain SQL. Just use a plain identifier
myvariablename
with no@
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.NetCommands
withParameters
, still use the@parametername
syntax as you would for SQL Server - Postgres has probably stayed ahead of of T-SQL in keeping close to the SQL standard syntax, though they both become more, not less, standards-compliant with each version. 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 the SQL StandardGENERATED BY DEFAULT AS IDENTITY
for Postgres 10 onwards. (You will seeSerial
in older versions). For more complex options, Postgres uses SQL Sequences. - 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 but stay with @variable for .Net clients using Npgsql |
NVarChar |
Text |
datetime |
timestamp |
Identity |
Generated By Default As Identity Before postgres 10 use Serial |
Raise |
Raise Exception |
Print |
Raise Notice |
Select Top 10 ... |
Select ... Fetch First 10 Rows Only ; |
Insert Into table Select ... |
Insert into Table (COLNAMES) Select ... |
If Then Begin … End |
If Then … ; |
Create Table #Name |
Create Temporary Table _Name |
IsNull |
Coalesce |
UniqueIdentifier |
uuid |
NewId() |
Before version 13, first websearch for Create Extension "uuid-ossp" to install the guid extension to a database. Then you can use uuid_generate_v4() and other uuid functions |
Alter Function|Procedure |
Create or Replace Function |
Authorization
Usually you will connect to postgres specifying a username. The psql.exe
commandline tool will default it to your OS login 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.
GUI Admin
Your replacement for SQL Server Manager is the pgAdmin GUI which gets you nicely off the ground with a live monitoring dashboard.
Command Line and Scripting
psql -h localhost -d myDB -c 'Select Current_User'
runs the quoted command. Note -h
for “host”, not -s
for “server”. Use psql --help
to see more options. You can also use the pipe:
echo 'Select Version(), Current_Database();
Select Current_TimeStamp' | psql -h localhost
For large data dumps, pg_dump
and pg_restore
are designed to generate consistent backups of entire databases or selected tables —data or DDL or both—without blocking other users.
There are more command line utilities
BootCamp says “Can’t install the software because it is not currently available from the Software Update server”
If you see this error message when trying to install bootcamp:
- Press the back button and try again
- Wait half an hour/day/month and try again