I was setting up a PostgreSQL database in Azure, to be used by a cutesy little Azure Function storing telemetry from a bunch of Azure IoT Edge Devices. But when everything was (supposed to be) configured successfully, my Azure Function failed to store anything anywhere because of a missing database…
Oh, right. Entity Framework can’t create PostgreSQL databases, as the connection is made to an existing database – so it can’t be instantiated if the database doesn’t exist on the server already.
So, what do I do to get this working?
Problem
I had only set up automated deployment scripts for a Postgres database running in an Azure IoT Edge module (a Docker container running on top of Azure IoT Edge runtime), not for a database running in Azure. My automatic code-first Entity Framework migrations would take care of updating the schema of whatever databases I might need and even seed some basic data to them. But they will not create the databases for me.
So – I figured I’d need to connect to the postgres database myself and run a couple of CREATE DATABASE commands. But I hate installing new tools, I don’t think you can do this in Azure Data Studio (or maybe you can, but I didn’t want to figure out how to do that), and obviously, I didn’t want to add my IP address to the firewall for the database server.
Solution
Well – the solution turned out to be quite obvious. You can just run psql in Azure Cloud Shell!
Wait – Azure Cloud Shell? What’s that? Let’s investigate together!
Cloud Shell is a bash/PowerShell -based command-line tool available on Azure Portal, so it’s very handily usable right on the administration page of the postgres database server. Neat!
But it doesn’t actually run in the browser – it runs on someone’s computer. Microsoft’s documentation says this:
"Cloud Shell runs on a temporary host provided on a per-session, per-user basis"
So there IS a host, that host is temporary on a per-session basis, and it has an IP address. But that IP address will change. Hence, You should not use that IP address as a basis of any kind of firewall rule.
But the good thing is the host is in Azure – so if allowing access from other Azure services temporarily is an acceptable risk for you, you can just flip that switch and your Cloud Shell can merrily run psql and access your Postgres database that way!
Sweet, eh? Let’s go through the steps on how to do all that!
Time needed: 15 minutes
How to configure Azure PostgreSQL to allow Cloud Shell connections?
- Browse your database resource in Azure Portal
Mine was hosted on a Postgres single server, but I trust you can find your database, wherever it’s hiding from.
- Allow Azure-internal connections
That setting is under “Settings > Networking > Allow public access from any Azure service within Azure to this server” – or something similar.
Enable it temporarily (so that Cloud Shell can access this database), and please PLEASE remember to disable it when you’re done. - Navigate to Connection Strings
You’ll need the connection string for your database. Copy-paste one (remember to replace the password -part) from the list – there should be one for psql readily available!
- Open Cloud Shell
Here it is:
- Run your scripts in the Cloud Shell
Depending on what you want to do, you’ll need to figure out the right SQL commands – but the login using psql works somewhat like this:
psql "host=myposgresdatabase.postgres.database.azure.com port=5432 dbname={your_database} user=pgadmin password={your_password} sslmode=require"
You’ll probably want to replace the parts inside squiggly braces (or “curly brackets”, if you’re boring) with whatever’s applicable to your case.
In case you DON’T have a database yet, just insert “postgres” for the database name.
Then, if you’re like me and want to create a database, just do something like this:CREATE DATABASE mydb;
And please PLEASE do remember that semi-colon. Postgres really, REALLY likes it. - Disallow Azure-internal connections again
Pretty important! You don’t want to leave your database open for anyone to access from their Cloud Shells or Virtual Machines.
And we’re done! Did it work for you?
References
- https://docs.microsoft.com/en-us/azure/cloud-shell/overview
- https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.
This is his personal professional (e.g. professional, but definitely personal) blog.
- M365 Copilot claiming “You have turned off web search in the work mode”? Easy fix! - November 19, 2024
- “Performing cleanup” – Excel is stuck with an old, conflicted file and will never recover. - November 12, 2024
- How to add multiple app URIs for your Entra app registration? - November 5, 2024
