Add POSTGIS extension to Azure Postgres Database – Worked Example

Firstly we need to allow for the extension on the server.
Within the Azure portal navigate to server parameters

Search for azure.extensions

Select which extensions to add
REMEMBER to save before exit!

Wait a short period while the extension is loaded onto the server..

Some points
Azure Database for PostgreSQL supports a subset of key PostgreSQL extensions as listed HERE. This information is also available by running SHOW azure.extensions;. Extensions not listed in this document aren’t supported on Azure Database for PostgreSQL – Flexible Server. You can’t create or load your own extension in Azure Database for PostgreSQL.

After extensions are allow-listed and loaded, these must be installed in your database before you can use them. To install a particular extension, you should run the CREATE EXTENSION command. This command loads the packaged objects into your database.

The extension(s) should now be listed against your database.

Postgres on Azure : also named Azure Database for Postgres Flexible Server : MS Azure

Quick Start Azure Documentation

Good link on tips and tricks Azure

Login to your azure portal and choose Create a resource

Next select the Azure Database for PostgresSQL Flexible Server

Now indicate the starting parameters
Please note IF you want to connect using PGAdmin I found issues with version 15 so chose version 14 it is not clear why this is some quick googling suggested it was the version of PGAdmin I was using. So at the bottom of this post I capture the about PgAdmin 4 that I am using as a client. I was able to connect to the server vis psql.
Remember all of your parameters and choose an appropriate account

Note you can stop the server if this is a development test copy which is good for reducing costs…

Do this by navigating to the server and selecting the stop button at the top.

Note give it time to start and stop not like me try and start the server before it has finished stopping!

MS Azure : Identify outbound IP addresses from an MS Azure Web App Service

When setting up an Web App Service on Azure which you are wanting to hook up to an Azure Database you will need to ensure that the IP address of the Web App Service is white listed for the Database Server. This is true of Postgres and SQL Azure in my experience. Now when you upload your project to the Web App Service and connect to an SQL Azure the error message indicates what IP address of the web application is failing. That is not the case when trying to connect to Azure Database for Postgres Flexible Server (my experience at September 2023) so how do we find this. As usual the answer is in Azure documentation but thoroughly buried.

One paragraph states

Regardless of the number of scaled-out instances each has a set number of outbound IP addresses at any given time. Any outbound outbound connection from the App Service app, such as to a back end database, uses one of the outbound IP addresses as the origin IP address. The IP address is selected randomly at runtime,

SO YOUR BACK END SERVICE MUST OPEN ITS FIREWALL TO ALL THE OUTBOUND IP ADDRESSES FOR YOUR APP

So how do we find these outbound IP addresses?
Log into the Azure Portal using the administrator account that has access to the Web App Service you want to identify outbound IP Addresses for.

Select the web app service

Look to Networking within Settings menu

Now several boxes should be presented to you towards the right of your browser window identify the one marked Outbound Traffic.

IP Addresses you are interested in should be presented where the red box is. There is a helpful copy button should you need to quickly copy them.

So that is great but what if you are quickly wanting to identify the outbound IP address of a web app

So go to the windows terminal

and use the nslookup command followed by the domain you wish to identify the IP address of.

Postscript
One point is that I seemed to have difficulty registering the outbound IP addresses and it was repeatedly failing to connect a front end application to the Azure Database for Postgres flexible server – I went through my outbound addresses thoroughly and then stopped and started the server itself. Note the restart option wasn’t working for me so I did a complete stop and then start. After this it seemed to work. I will continue to monitor this but its not a process I perform often so it may be sometime before I can truly establish my issue. It should be noted that nslookup did not initially reveal the IP that worked I still don’t know which of the outbounds are working or whether it was the restart that made it work. I did note that the allow all azure connections option always corrected the issue (so this can be used for testing)

Postgres – Export a Spatial database and then Importing again (pg_dump + psql utilities)

Lets take this from the beginning. The first thing you need to do is be on the physical machine where the database is running.

Next check that the database server is up and running

To do that

Go to the windows bar click on the search icon and type in

Services

Scroll down to P and look for the name postgresql this and look to the Status column in the above example there are two postgres 11 and postgres 14 of the two only 14 shows as Running.

There are several Utility executables that come with every postgres installation.

The standard installation set up puts these into a directory equivalent to the following;

We need to navigate at the command prompt to the location of the executable we are interested in. Technically you could qualify the path to, the executable when running a command, but I tend to navigate to the location.

So firstly in the search button go to the command prompt

cmd

cd C:\Program Files\PostgreSQL\14\bin

pg_dump -U postgres -W -F p -d dbspatialhub > c:\sql\dbspatialhub.sql

pg_dump -U postgres -W -F p -d dbspatialhub > c:\sql\dbspatialhub.sql

and here is a string that works if your database is on the local server with a non standard port / substitute 5433 for your port

pg_dump -U postgres -h localhost -p 5433 -d dbsource > c:\sql\dbsourceexport.sql

run this command – you will note that I qualify the output directory here. I prefer to do this as I don’t particularly like writing and deleting files into the program directory. I prefer this directory to be reserved for standard files and reduce the likelihood of me in anyway accidentally deleting an important file.

You should be presented with the Password: option you will need to input the password for specified User (-U) complete

Success will be shown as a return to the former prompt with no message.

and running a DIR on the directory look for the sql file…

Next from either psql prompt or from within PGAdmin4 create a database you are going to import the sql into and ensure that the postgis extension is installed. Remember this is the psql prompt and not DOS command line prompt.

CREATE DATABASE dbspatialhubnew
CREATE EXTENSION postgis

Now you have to go back to the command prompt (and the correct directory).

If you are on a different port add the following string -p 5434

psql -U postgres -d dbspatialhubnew -f c:\sql\dbspatialhub.sql

After being asked for the password the import will start and continue showing a list of various commands and data being executed and imported.
Similar to below.

Now you can go back to a client such as QGIS , link to the database to start confirming a successful import.

Congratulations you have copied a database.

It should be noted that as it stands I am having problems importing the layers table which has the format of the boundaries for tables.
I will continue to work on this.

QGIS – Working with Free BASEMAP Services for Desktop Projects (Bing and Google Maps Aerial and Lines)

As soon as you need to practically implement any information in Spatial Databases display of the information through a mapping front end becomes absolutely vital. Many database administrators are used to simply going into their favourite database editors and displaying the raw subsets of tables and queries. That works well for financial transactions and inventory tables were collapsing the attributes of objects into single digits is often valuable or possibly preferable to simple photos. When dealing with boundary information the complete opposite applies. Display of information as simple screens of matrix numbers is completely useless.

Additionally often boundaries make zero sense unless referenced to the land beneath them either through aerial photography or topographic maps.

In a previous time where I worked we actually commissioned a company to give us aerial photos of a local authority. This was not an insignificant amount of money and was probably only marginally reduced by the vendor having multiple channels of sale. Google and Microsoft are now very good in offering very good aerial and line interpretations for limited use to companies and individuals. This is absolutely great as it can be used as background either to confirm accuracy of other information or as data upon which to calculate further information (eg routing).

So how can an individual get up and started with some of these basemaps.

Sometime recently (I know not when exactly) QGIS changed its implementation of Open Street Maps through their desktop – rather than being an additional plugin Open Street Map provision is now included on install.

Here I am working with QGIS version 3.10

Now you should be presented with the Data Source Manager Dialog which looks like this

Now expand the XYZ Tiles

You can then double click on any one of the three and the WMS will be transferred into the layer panel

Layer > Data Source Manager > XYZ

Thank you to Google and Microsoft and OSM and QGIS for a great implementation.

Long Passwords : Evidence showing why long passwords are a simple security principle

Creating long passwords is one of the easiest ways of improving the security of your applications and resources – here’s a graphic that estimates time it might take to brute force a password depending on the complexity of the character set and its length.

Secure Password Generator

Last Pass Password Generator

Norton Password Generator

1Password – random password generator

bitwarden random password generator

AVAST Password Generatotr

MariaDB – Add calculated field that shows day name

ALTER TABLE tablename 
ADD COLUMN dayname VARCHAR(20) GENERATED ALWAYS AS (DAYNAME(startdate)) 
STORED;

This query will add a new column called dayname to the tablename table. The column will be of type VARCHAR with a maximum length of 20 characters. The GENERATED ALWAYS clause tells MariaDB to calculate the value of the column each time a row is inserted or updated. The AS keyword specifies the expression used to calculate the value, in this case the DAYNAME function applied to the startdate column. Finally, the STORED keyword tells MariaDB to store the calculated value in the table so that it can be retrieved more efficiently.

Note that the GENERATED ALWAYS and STORED clauses require MariaDB version 5.7.6 or later. If you are using an earlier version of MariaDB, you can still add a calculated field using a trigger or a view.

MariaDB – Create query that matches value to a range in another table

For example lets say we were wanting to allocate transactions to a financial year but allow the users to adjust that financial year depending on their circumstances. How could we do that.

Here we have a table called
t0165financeyear
whose structure is

pkid
taxyear
startdate
enddate

And a table of transactions called t023finance

CREATE VIEW v0004financeyear
AS SELECT a.pkid pkidt0023,
a.banked,
a.transactiondate,
a.description,
a.category,
a.Direction,
a.hmrcint,
a.pkidt0001,
a.pkidt0007,
a.vamount,
a.invoiceno,
a.pkidt0011,
a.dupdated,
a.dcreated,
b.pkid pkidt00165,
b.taxyear from t0023finance a, t00165taxyears b
where
a.banked between b.startdate and b.enddate

Remarkably simple – I suspect that we might get cartesian join issues if your start date and end dates in the t00165 table overlap.

MS SQL Azure – TSQL to identify all Triggers in a database / Drop Trigger TSQL syntax

I had been told that it was better not to use Triggers..

Nonetheless I had to learn the hard way.

So fast forward a period and I’m wanting to remove triggers from a database and I first need to find where I had put them in the first instance.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'

And running this against your database you should see something like this.

And how you drop the triggers

IF OBJECT_ID ('sampledelete2chargehistory', 'TR') IS NOT NULL  
   DROP TRIGGER sampledelete2chargehistory;

Thanks to Stack Overflow – I still get some answers from it over AI

Stackoverflow

SQL Server Express – starting to work with Geometry Functions in SQL Server

Firstly I created a database.

This is essentially me trying to implement in SQL Server what I had written about in Postgres here – Using ST_Within in Postgres

Now I create the starting tables.

CREATE TABLE [dbo].[t00001fields](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[fieldname] [varchar](50) NULL,
	[geom] [geometry] NULL

Then

CREATE TABLE [dbo].[t00002plots](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[plotname] [varchar](50) NULL,
	[geom] [geometry] NULL

Now a magic junction table that will identify what plot is within which field when it is run

CREATE VIEW v001FieldPlotJunction AS
SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
FROM
t00002plots,
t00001fields
WHERE
t00002plots.GEOM.STWithin(t00001fields.GEOM) = 1;

and here’s an example of it running on local in SQL Express

And this is what I am seeing when viewed through QGIS = many of the fields are smaller than the plots and so although 1 2 look like they should be in the view because the plot is not WITHIN the field it does not show which is the correct response in this case.

Batch File to split CSV into smaller parts

So I am using CHATGPT regularly now and this is a good example of where it can prove very useful. The bottom bat iteration was obtained from StackOverflow and successfully split a large file into chunks of 2,500. I then realised that each sub file needed to have headers in it to assist in importing the information into a database. I asked CHATGPT to alter the starting file to ensure that headers were included subsequently.

It did it no problem..

@echo off

setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.

SET BFN=C:\csv\target.csv

REM Edit this value to change the number of lines per file.
SET LPF=2500

REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=SplitFile

REM Do not change beyond this line.

SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
set "header="
for /f "tokens=* usebackq" %%a in ("%BFN%") do (

if not defined header (
set "header=%%a"
echo !header! > %SFN%!FileNum!.%SFX%
) else (
SET /A LineNum+=1
echo %%a >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
echo !header! > %SFN%!FileNum!.%SFX%
)
)

)

endlocal

Pause

This is the original obtained from Stack Overflow..

@echo off

setlocal ENABLEDELAYEDEXPANSION

REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=C:\csv\target.csv

REM Edit this value to change the number of lines per file.
SET LPF=2500

REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=SplitFile

REM Do not change beyond this line.

SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1

)

)

endlocal

Pause

MS Azure – SQL Azure – Reliability update 001 (38 days) – using Azure Logic App to execute a Stored Procedure

So a month ago in an non critical application I set up a trigger for a stored procedure to copy records (about 3000) from one table to another 6 times a day.

Approximately 38 days in and I have 100% reliability.

I have firewalled the required IP addresses listed by Microsoft
Azure Logic Apps list of IPs for Firewalls

I will keep running this for testing purposes to identify the reliability and report back

MS Azure – TSQL a simple example using a Common Table Expression (CTE)

A common table expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and exists only for the duration of the query.

I’m finding them useful as you can use them within a view to contain all the references required for that particular view. This I feel makes things more maintainable.

An example of a simple common table expression (CTE) in T-SQL:

WITH CTE_EmployeeSalaries AS
(
    SELECT EmployeeID, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT *
FROM CTE_EmployeeSalaries;

This CTE defines a virtual table named CTE_EmployeeSalaries that contains all employees with a salary greater than 50,000. The CTE is then used to select all rows from this virtual table.

Common table expressions (CTEs) have several advantages:

1.Improved readability: can make complex queries easier to read and understand by breaking them up into smaller, more manageable pieces.
2.Improved maintainability: Because CTEs are self-contained, they can be easily modified or replaced without affecting the rest of the query.
3.Improved performance: In some cases can improve query performance by reducing the need for subqueries.
4.Recursive queries: CTEs can be used to perform recursive queries, which are useful for querying hierarchical data such as an organizational chart or a bill of materials.
5.Temporary results: can be used to store intermediate results that are used multiple times within a single query. This can be more efficient than repeatedly calculating the same intermediate results.
6.Better alternative to view: CTEs can be used as an alternative to views, which can be difficult to modify and are not always optimized for performance.
7.Reusable: CTEs can be defined once and used multiple times within a single query or in multiple queries within the same session.

MS Azure TSQL writing comments

In TSQL, comments can be added to code in two ways:

1.Single-line comments: These start with two hyphens (–) and continue until the end of the line. For example:

-- This is a single-line comment

2.Multi-line comments: These start with a forward slash and an asterisk (/) and continue until the closing asterisk and forward slash (/). For example:

/*
This is a multi-line comment. It can span
multiple lines and is often used to provide
detailed explanations or to comment out large
blocks of code.
*/

When writing comments, it’s important to keep them clear and concise, and to use them to explain why something is being done rather than how it is being done. This helps to make your code more readable and easier to understand for other developers who may be reading it.

SQL Azure – Using Azure Logic Apps to execute a Stored Procedure in SQL Azure

It is important in a lot of systems to be able to run queries on a regular schedule. Previously I have outlined that this can be done using an automation account. It is also possible to accomplish this using an Azure Logic App to call a Stored Procedure.

The procedure is
Create Logic App
Add items to it

Firstly using SSMS connect to the database and create an example stored procedure.

CREATE PROCEDURE [dbo].[TestInsert]
AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.T0003NLPRSitesTarget
(sitename)
SELECT Sitename from dbo.T0003NLPRSites
END
GO

Next log into your Azure Portal and identify the following icon

Select it and then hit the Add button..

You will be presented with the following screen

Now you should be taken to an overview showing that the logic app has been created

Hit Go to resource

You should be presented with the following screen

Here we choose Recurrence

You will be presented with the following Logic Apps Designer

Here is an example
Interval is like how many times the logic app will run over what Frequency
So if we put 3 in Interval and Frequency Day it would run once every 3 days
At these hours – sets specific times when the logic app will run within the set Frequency
On these days – allows for you to set what days the logic app will run At these hours

To explain the logic app running work from the bottom up. So the below example

On Monday, Tuesday , Wednesday, Thursday and Friday at 9,10,11,12,13,14,15,16 and 17 hours

Every week this will run (If Interval had been set to 3 it would have been every three weeks on Monday,Tuesday,Wednesday, Thursday and Friday at the hours 9,10,11,12,13,14,15,16 and 17 hours it would run.

Now hit next step and then type in execute stored procedure

You will first need to set up the connection to the database and then enter the parameters of the server database name database username and password or your chosen security to connect to the database and then you will be taken to the appropriate item.

You will need to set up the firewall on the server to ensure otherwise the logic app is likely to stop working at somepoint..

You can get a list of the appropriate firewall rules to set up by going here..

Managed connectors outbound IP addresses

So after further testing with the following parameters I discovered that in practice SQL Azure is a bit loose about the hours recurrence option. For example the following

Has been runnnig at 43minutes past the hour. Which is indicative of when I hit a run trigger at..

I will be checking to see when it next runs but I suspect it is going to be 17:43

SQL Azure (Basic notes on backup operation)

If you are running things on the cloud you always want to be thinking about backups and ensure that you are really aware of what is happening with backups and how to restore from backups. You should regularly check what is happening with backups and regularly practice restoring databases.

Basic / Standard and Premium SQL Azure has two basic options for restore.

Point in Time Restore and
Long Term Retention

They perform the same task – they collect old databases that can be used to restore to a server if need be but what varies in the UI by which you restore them.

First point – Long Term Retention Policy is attached to the server and you go into it by first navigating to your server and then looking to the left hand section marked as Data management.

Select Backups and you will be presented with a list of all the long term backups of the databases on that server. The following are a couple of basic dtu databases and what can bee seen on the fictional server 01SeverName

In the above table you can see that Long Term Retention policy has NOT been set. How do you change this.

Firstly ensure that you are on the retention policies tab and then tick the particular database you would like to set the retention policy for in the lower section this will allow you to Configure a policy for that particular database.

Here are the options for a BASIC DTU database

Compare this with the options for a database in the Standard Tier

Set it up and then hit apply and you should be good to go.

MS SQL Azure – CREATE USER with read only access to 2 tables

In a lot of situations you may need a satellite website feeding off the main website with greatly reduced access.

It is a good idea to only give that user access to only what they need and limit it down to the bare minimum.
Here I have an azure database called LandRegister and I connect to it in the usual way through SSMS.

Use LandRegister

USE LandRegister
GO

CREATE user TestUserRestricted with password = 'Password01';

Next you can go to the security section after a refresh to see if the user has been added.

And then grant access to the tables you wish them to see.

USE LandRegister
GO

GRANT SELECT on dbo.T0003NLPRSites to TestUserRestricted
GRANT SELECT on dbo.T0024Authority to TestUserRestricted;

TestUserRestricted can now be used by your web application without any fear that
1) A USER cannot login to a server through SQL Server Management Studio
2) That they can see anything except the tables listed.
3) That in this case they can do anything except select statements on the stated tables. (So no write delete or updates on table)

Here I give full access to the table

USE LandRegister
GO

GRANT INSERT,SELECT,DELETE,UPDATE ON 
dbo.T039Country to TestUserRestricted;

And it works with views as well

Use LandRegister
GO

GRANT SELECT on dbo.v032 to TestUserRestricted;

MS Azure & Web Applications – TSQL Create a check constraint on nvarchar column specifying a minimum length in DB and through the front end ensure enforce OWASP security

Double security!
Enforce length in database and then through the front end by validating in this case password values (PHPRunner example).

First the backend
You have a nvarchar(128) column in MS Azure SQL Database (or any Microsoft SQL Server database) and you wish to add a check constraint that does not allow a value in the column to be less than lets say 15 characters long.

tablename = users
field = userpassword

ALTER TABLE dbo.users
ADD CONSTRAINT [MinLengthConstraint] 
CHECK (DATALENGTH([userpassword]))>14;

And then a good way to check the constraints on an individual table using TSQL through SSMS for MS AZURE.

SELECT TABLE_NAME, 
CONSTRAINT_TYPE, 
CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME ='users';

Front End
Now for double security in your web application use a regular expression to prevent users entering a weak password.

^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{15,128}$

^               # start of the string
(?=.*\d)        # a digit must occur at least once
(?=.*[a-z])     # a lower case letter must occur at least once
(?=.*[A-Z])     # an upper case letter must occur at least once
.{15,128}       # 15-128 character password must be a minimum of 15 characters and a maximum of 128
$               # end of the string

OWASP – Open Web Application Security Project