create-postgres-app-database.ps1

#! /usr/bin/env pwsh

<#
  .SYNOPSIS
  Create a Postgres application database and roles for owner, application, and application_readonly
  
   .DESCRIPTION
  Create a Postgres application database and create roles for owner, application, and application_readonly.
  
  - the owner role has no login. the user that runs this script will be assigned to that role
  - the application and application_readonly roles will be login roles with password either specified by you
    or generated by this script. 
  - If the passwords are generated by this script, they will be printed as the first 
    2 lines of output of this script. 
  - If the script scram_postgres_password.py exists in the path, the passwords will be scram encrypted.

  - Optionally install common extensions UUID, plv8 and trigram
  - Optionally create functions for view and drop database connections
   
  .LINK
  https://gist.github.com/chrisfcarroll/ed3f8c368ad6bdbbe8a71d4c3afa48f7
  
  .LINK
  For Scram encryption (requires python3): https://gist.github.com/chrisfcarroll/4c819af67ec5485ed0d6aef7863562a4
#>
Param(
  $postgresHost="localhost",
  [string]$superuser=[Environment]::UserName,
  $databaseName="umbraco_green",
  [string]$databaseOwner="umbraco_owner",
  $appAccount="umbraco_green",
  [string]$appAccountPassword,
  $readonlyAppAccount="umbraco_green_readonly",
  [string]$readonlyAppAccountPassword,
  $dbLocale,
  [switch]$createViewAndDropConnectionFunctions,
  [switch]$addTrigram,
  [switch]$addPlv8,
  [switch]$dryRun,
  [bool]$addUUID=$true
)


function defaultLocaleForLocalhost{
  $lc= if($IsWindows){
    "ENCODING = 'UTF8' LC_COLLATE = 'english_United Kingdom' LC_CTYPE = 'english_United Kingdom'" }
    else {"ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8'" }
  return $lc
}
if(-not $dbLocale){$dbLocale= defaultLocaleForLocalhost}

function runOrDryRun($command, $db){
  if($dryRun){ "-d $db :  $command"}
  else{ $command | psql --host=$postgresHost -d $db -U $superuser -X --echo-all }
}

function New-Password([int]$length=12){ 
  1..($length * 3) |
          ForEach-Object{ Get-Random -Minimum 48 -Maximum 122 } |
          Where-Object { $_ -lt 58 -or $_ -gt 64 } | Where-Object {$_ -lt 91 -or $_ -gt 96 } |
          ForEach-Object{ [Char]$_ } | Select-Object -first $length | 
          ForEach-Object {$agg=""} {$agg += $_} {$agg} 
}

if(-not $appAccountPassword){
  $appAccountPassword= New-Password 20
  $appAccountPassword
  $didGeneratePassword=$true
}
if(-not $readonlyAppAccountPassword){
  $readonlyAppAccountPassword= New-Password 20
  $readonlyAppAccountPassword
  $didGeneratePassword=$true
}

if($didGeneratePassword)
{
  write-warning "-----------------------------------------------------
You did not provide passwords, so passwords were created and shown above this line."
}

if(get-command scram_postgres_password.py){
  $canScramEncrypt=$true
  $appAccountPassword= scram_postgres_password.py $appAccount $appAccountPassword
  $readonlyAppAccountPassword=scram_postgres_password.py $readonlyAppAccount $readonlyAppAccountPassword
}
elseif (Get-Command md5)
{
  
}

"
-----------------------------------------------------
  Will log in to Host=$postgresHost as User=$superuser to create:
  
    Database= $databaseName 
    with
    database Owner=$databaseOwner
    Locale=$dbLocale
    application Login=$appAccount
    application readonly login=$readonlyAppAccount
    add UUID extension: $addUUID
    add Trigram extension: $addTrigram
    add plv8 extension: $addPlv8
    Create Functions for View and Drop Connections: $createViewAndDropConnectionFunctions
    $(if($canScramEncrypt){"Passwords will be stored as scram-hashes"})
"

"
-----------------------------------------------------

starting ...
"

$requireds= ('$postgresHost','$databaseName','$dbLocale','$databaseOwner','$appAccount','$readonlyAppAccount',
                '$appAccountPassword','$readonlyAppAccountPassword')
$invalids= $requireds | Where-Object { -not $ExecutionContext.InvokeCommand.ExpandString($_) }

if($invalid.Count){
  $dryRun=$true
  write-warning "dry running because you missed a parameter: $([string]::Join(", ", $invalids))"
}

runOrDryRun @"
Create Role $databaseOwner CreateDb CreateRole ;
Grant $databaseOwner to current_user ;
CREATE DATABASE $databaseName With Owner $databaseOwner TEMPLATE = template0 $dbLocale ;
Alter Database $databaseName set client_encoding='UTF8' ;
Create Role $appAccount Login Password `'$appAccountPassword`' ;
Create Role $readonlyAppAccount Login Password `'$readonlyAppAccountPassword`' ;
Grant Connect , Temporary on Database $databaseName TO $appAccount ;
Grant Connect , Temporary on Database $databaseName TO $readonlyAppAccount ;
Grant $readonlyAppAccount to $appAccount;
Grant $appAccount to $databaseOwner;
Revoke Connect On Database $databaseName From Public;
"@  'postgres'


if($createViewAndDropConnectionFunctions)
{
  "    Adding favourite Utilities to database postgres ..."
  
  runOrDryRun @"
Create or Replace Function ps() 
  Returns Table (pid int, datname name, usename name, application_name text, client_addr inet) 
  Language SQL 
  As 'Select a.pid, a.datname, a.usename, a.application_name, a.client_addr from pg_stat_activity a' ;

Create or Replace Function DropConnections(id int, database name)
  Returns Void
  Language PlpgSQL
  As `$`$
  Begin
    If (id is null and database is null ) Then Raise 'At least one of id or database must be not-null' ; End If;
    Perform pg_terminate_backend(pg_stat_activity.pid)
    FROM pg_stat_activity
    WHERE ( pg_stat_activity.datname = database Or database is null)
      AND ( pid = id or id is null);
  End `$`$;
"@ 'postgres'
}

if($addUUID){
  "    Adding extension uuid-ossp to database lb ..."
  runOrDryRun 'Create Extension If Not Exists "uuid-ossp" ;' $databaseName
}

if($addTrigram){
  "    Adding extension pg_trgm to database lb ..."
  runOrDryRun 'Create Extension If Not Exists "pg_trgm" ;' $databaseName
}

if($addPlv8){
  "    Adding extension plv8 to database lb ..."
  runOrDryRun 'Create Extension If Not Exists "plv8" ;' $databaseName
  
    if(-not $?){
      write-warning "Create Extension plv8 failed. Trying to first install to db=postgres"
      'Create Extension If Not Exists "plv8" ;' | psql --host=$postgresHost -d postgres -U $superuser -X --echo-all
      'Create Extension If Not Exists "plv8" ;' | psql --host=$postgresHost -d lb -U $superuser -X --echo-all
  
      if(-not $?){
        write-warning "Create Extension plv8 failed. Trying to install plv8 to your postgres instance"
        start -verb runas powershell "$PSScriptRoot/install-postgres-plv8-extension.ps1"
        write-error "rerun this script after the server has restarted."
        exit 1
      }
    }
}