This works like a charm:
PostgreSQL
Mix.install(
[
{:x509, "~> 0.9.2"},
{:postgrex, "~> 0.22.0"},
{:ecto_sql, "~> 3.12"},
{:req, "~> 0.5.17"},
{:envious, "~> 1.4"},
{:kino, "~> 0.18.0"}
]
)
Section
defmodule MicrosoftCerts do
defmodule CompileHelpers do
defp http_get(url) do
%Req.Response{status: 200, body: body} = Req.get!(url: url)
body
end
def download_certs_for_pinning() do
# https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-networking-ssl-tls#download-root-ca-certificates-and-update-application-clients-in-certificate-pinning-scenarios
"""
https://www.microsoft.com/pkiops/certs/microsoft%20azure%20rsa%20tls%20issuing%20ca%2004%20-%20xsign.crt
https://www.microsoft.com/pkiops/certs/Microsoft%20RSA%20Root%20Certificate%20Authority%202017.crt
https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem
https://dl.cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem
https://cacerts.digicert.com/DigiCertGlobalRootCA.crt
"""
|> String.split(["\n"], trim: true)
|> Enum.map(fn url ->
{url, http_get(url)}
end)
|> Enum.map(fn {url, data} ->
cond do
url |> String.ends_with?(".crt") ->
data
url |> String.ends_with?(".pem") ->
data
|> X509.Certificate.from_pem!()
|> X509.Certificate.to_der()
end
end)
|> Enum.uniq()
end
end
@certs MicrosoftCerts.CompileHelpers.download_certs_for_pinning()
def ssl_opts(hostname) do
[
protocol: :tls,
protocol_version: :"tlsv1.3",
verify: :verify_peer,
cacerts: @certs,
server_name_indication: String.to_charlist(hostname),
depth: 3
]
end
end
Path.join(System.get_env("HOME"), ".azure_creds.sh")
|> File.read!()
|> Envious.parse!(interpolate: true)
|> System.put_env()
defmodule MyApp.Repo do
use Ecto.Repo,
otp_app: :my_app,
adapter: Ecto.Adapters.Postgres
def configure_entra_token(tenant_id, client_id, client_secret) do
fn opts ->
{:ok, %Req.Response{status: 200, body: %{"token_type" => "Bearer", "access_token" => access_token}}} =
Req.request(
method: :post,
url: "https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token",
path_params_style: :curly,
path_params: [
tenant_id: tenant_id
],
form: [
grant_type: "client_credentials",
client_id: client_id,
client_secret: client_secret,
scope: "https://ossrdbms-aad.database.windows.net/.default"
]
)
opts
|> Keyword.put(:password, access_token)
|> Keyword.put(:ssl, MicrosoftCerts.ssl_opts(opts[:hostname]))
end
end
end
Application.put_env(:my_app, MyApp.Repo,
hostname: System.get_env("POSTGRESQL_SERVER_DOMAIN"),
username: System.get_env("POSTGRESQL_ADMIN_ENTRA_APP_NAME"),
database: "postgres",
configure: MyApp.Repo.configure_entra_token(
System.get_env("POSTGRESQL_ADMIN_ENTRA_TENANT_ID"),
System.get_env("POSTGRESQL_ADMIN_ENTRA_CLIENT_ID"),
System.get_env("POSTGRESQL_ADMIN_ENTRA_CLIENT_SECRET")),
pool_size: 10,
ssl: true
)
{:ok, repo_pid} = MyApp.Repo.start_link()
defmodule MyApp.User do
use Ecto.Schema
import Ecto.Changeset
schema "users" do
field :username, :string
field :email, :string
field :password_hash, :string
timestamps(type: :naive_datetime)
end
def changeset(user, attrs) do
user
|> cast(attrs, [:username, :email, :password_hash])
|> validate_required([:username, :email, :password_hash])
|> unique_constraint(:username)
|> unique_constraint(:email)
end
end
defmodule MyApp.Migrations.CreateUsers do
use Ecto.Migration
def up do
create_if_not_exists table(:users) do
add :username, :string, size: 50, null: false
add :email, :string, size: 255, null: false
add :password_hash, :string, size: 255, null: false
timestamps(type: :naive_datetime, default: fragment("CURRENT_TIMESTAMP"))
end
create_if_not_exists unique_index(:users, [:username])
create_if_not_exists unique_index(:users, [:email])
end
def down do
drop_if_exists table(:users)
end
end
# Run the migration
Ecto.Migrator.up(MyApp.Repo, 1, MyApp.Migrations.CreateUsers)
# To rollback:
# Ecto.Migrator.down(MyApp.Repo, 1, MyApp.Migrations.CreateUsers)
import Ecto.Query
# Insert a user
{:ok, user} =
%MyApp.User{}
|> MyApp.User.changeset(%{
username: "testuser",
email: "test@example.com",
password_hash: "hashed_password_here"
})
|> MyApp.Repo.insert()
# Query all users
MyApp.Repo.all(MyApp.User)
# Query with conditions
MyApp.Repo.all(from u in MyApp.User, where: u.username == "testuser")
# Get by id
MyApp.Repo.get(MyApp.User, 1)
# Delete all test users
MyApp.Repo.delete_all(MyApp.User)
Postgrex
create_table_query =
"""
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""
drop_table_query =
"""
DROP TABLE users
"""
db_host = System.get_env("POSTGRESQL_SERVER_DOMAIN")
Kino.nothing()
{:ok, %Req.Response{status: 200, body: %{"token_type" => "Bearer", "access_token" => access_token}}} =
Req.request(
method: :post,
url: "https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token",
path_params_style: :curly,
path_params: [
tenant_id: System.get_env("POSTGRESQL_ADMIN_ENTRA_TENANT_ID")
],
form: [
grant_type: "client_credentials",
client_id: System.get_env("POSTGRESQL_ADMIN_ENTRA_CLIENT_ID"),
client_secret: System.get_env("POSTGRESQL_ADMIN_ENTRA_CLIENT_SECRET"),
scope: "https://ossrdbms-aad.database.windows.net/.default"
]
)
{:ok, entra_conn} = Postgrex.start_link(
hostname: db_host,
port: 5432,
database: "postgres",
ssl: MicrosoftCerts.ssl_opts(db_host),
# The username here is the friendly name of our app...
username: System.get_env("POSTGRESQL_ADMIN_ENTRA_APP_NAME"),
password: access_token
)
Postgrex.query!(entra_conn, create_table_query, [])
Postgrex.query!(entra_conn, drop_table_query, [])
{:ok, password_conn} = Postgrex.start_link(
hostname: db_host,
port: 5432,
database: "postgres",
ssl: MicrosoftCerts.ssl_opts(db_host),
username: System.get_env("POSTGRESQL_ADMIN_NAME"),
password: System.get_env("POSTGRESQL_ADMIN_PASSWORD")
)
Postgrex.query!(password_conn, create_table_query, [])
Postgrex.query!(password_conn, drop_table_query, [])
