DEV Community: infohash

Dynamically Create Database & Tables With Async SQLAlchemy

infohash — Tue, 03 Oct 2023 19:38:27 +0000

Often times, you create a startup function in your services that creates data objects at various resources during the initialization of the service. A common example is creating tables in a database that various components of your service expects to be present before they can read and write data. SQLAlchemy library in Python allows you to interact with the database programmatically using database-agnostic Object Relational Mapper. Let's see how we can create database and tables by using SQLAlchemy ORM with async APIs.

Note: All examples below are in async version and written with SQLAlchemy 2.0+.

Setup

I'm using postgreSQL server running in docker.

$ docker run --name postgres-server -e POSTGRES_USER=admin POSTGRES_PASSWORD=admin -d postgres:latest

Database Creation

You may want a desired behaviour in your service that the startup hook should create database if it does not exist yet. Here's how:

import contextlib

from sqlalchemy import text
from sqlalchemy.exc import ProgrammingError
from sqlalchemy.ext.asyncio import create_async_engine


async def init_app() -> None:
    async with create_async_engine(
        url='postgresql+asyncpg://admin:admin@localhost:5432/postgres',
        isolation_level='AUTOCOMMIT').begin() as conn:
        # If db already exists, suppress the exception.
        with contextlib.suppress(ProgrammingError):
            await conn.execute(text(
                'create database testdatabase owner admin'))


if __name__ == '__main__':
    import asyncio

    asyncio.run(init_app())

Here's what's happening:

ProgrammingError exception is suppressed in case the database already exists.
We are connecting as a superuser to the default database postgres to execute create database query as a superuser.
Postgres does not allow you to create a database inside transactions, and SQLAlchemy always tries to run queries in a transaction. To get around this, isolation_level is set to AUTOCOMMIT.

Tables

Declare Models

Picking an example from its rich documentation:

import typing

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import (DeclarativeBase,
                            Mapped,
                            mapped_column,
                            relationship)


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, nullable=False)
    fullname: Mapped[typing.Optional[str]] = mapped_column(String)

    addresses: Mapped[list["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
        )


class Address(Base):
    __tablename__ = "address"

    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str] = mapped_column(String, nullable=False)
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))

    user: Mapped["User"] = relationship(back_populates="addresses")

Creating An Async Engine

If you have created the database dynamically or manually, pass the database URI to the engine.

from sqlalchemy.ext.asyncio.engine import AsyncEngine

async_engine: AsyncEngine = create_async_engine(url='postgresql+asyncpg://admin:admin@localhost:5432/testdatabase')

Creating Tables

async def init_app() -> None:
    async with async_engine.begin() as conn:
        await conn.run_sync(lambda sync_conn: Base.metadata.create_all(
            bind=sync_conn, checkfirst=True))

Here's what's happening:

We are using run_sync method because create_all method directly on an AsyncConnection or AsyncEngine object is not currently supported, as there is not yet an awaitable form. So, we are passing a synchronous connection sync_conn to the bind argument.
checkfirst argument checks if the table already exists.

Usecase

Usually, services are not given superuser access but if you have a service that onboards new tenants in your multi-tenancy cluster, it is required to dynamically create database during the onboarding of tenants. You can combine dynamic database and table creation here.

When creating tables, you will have to create another engine object that points to your newly created database. You don't have to pass isolation_level to this object unless you want to change the default one. The default is READ_COMMITTED for postgres.

import contextlib

from sqlalchemy import text
from sqlalchemy.exc import ProgrammingError
from sqlalchemy.ext.asyncio import create_async_engine

from .models import Base


async def init_app():
    async with create_async_engine(
        url='postgresql+asyncpg://admin:admin@localhost:5432/postgres',
        isolation_level='AUTOCOMMIT').begin() as conn:
        # If db already exists, suppress the exception.
        with contextlib.suppress(ProgrammingError):
            await conn.execute(text('create database mydb owner admin'))

    # Create another engine object which points to the newly created database.
    async with create_async_engine(
        url='postgresql+asyncpg://admin:admin@localhost:5432/testdatabase').begin() as conn:
        await conn.run_sync(lambda sync_conn: Base.metadata.create_all(
           bind=sync_conn, checkfirst=True))


if __name__ == '__main__':
    import asyncio

    asyncio.run(init_app())

How To Configure Audience In Keycloak

infohash — Sat, 30 Sep 2023 18:26:57 +0000

Keycloak has audience support that you can use to limit the recipients of your access token. Its documentation has a good example on why you should enforce audience before authorizing a request.

In the environment where trust among services is low, you may encounter this scenario:

A frontend client application requires authentication against Keycloak.

Keycloak authenticates a user.

Keycloak issues a token to the application.

The application uses the token to invoke an untrusted service.

The untrusted service returns the response to the application. However, it keeps the applications token.

The untrusted service then invokes a trusted service using the applications token. This results in broken security as the untrusted service misuses the token to access other services on behalf of the client application.

Setup

Installation

I'm running Keycloak version 22.0.0 in development mode in docker. You can deploy it in docker by running this command.



docker run -d --name Keycloak -p 8080:8080 -e KEYCLOAK_ADMIN=admin -e KEYCLOAK_ADMIN_PASSWORD=admin quay.io/keycloak/keycloak:22.0.0 start-dev

Client Creation

Let's create 3 clients Alice, Bob & Charles and assume that none of them trust each other. To create a client, go to Clients > Create client.

For the purpose of this demo, in order to obtain the access token from CLI, the only authentication flow I have enabled is Service accounts roles which enables client credentials flow. Now let's start.

Problem

Alice wants to invoke APIs of Bob. She obtains an access token using client credentials flow.



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic d2F0Y2hkb2dzOnlMdGVLR0U4VzJiWVVObjRYbEUwMHFNaHlZNVdiemhZ' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials



{
  "exp": 1696069210,
  "iat": 1696068610,
  "jti": "03e260af-5b79-4e84-885d-7f08307c7f29",
  "iss": "http://localhost:8080/realms/master",
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "scope": "",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

In the above output, scope is empty because there are no default scopes assigned to Alice yet. The aud claim which is the audience of her access token is also missing. If Alice sends this token to Bob, Bob can do 2 things with it:

Bob can reject it for not including him as the intended recipient of the token.
Bob can misuse the token by reusing it for invoking APIs of other services that do not validate the audience which is why you should always enforce audience for your own services.

Now let's configure audience for Alice so that she can set the intended recipient(s) of her access token to prevent misuse. An access token can have multiple audiences.

Configuring Audience

Custom Client Scope

Let's create a client scope untrusted-audience which I will assign to Alice as optional. It should be optional because Alice should be able to decide the intended recipient(s) of the access token by specifying different scopes. To create a client scope, go to Client scopes > Create client scope.

Hardcoded Audience

After creating it, inside this scope you will see a tab called Mappers which is empty. This is where an Audience mapper will be created that will include the client ID of Bob.

Go to Mappers > Configure a new mapper > Audience. In the Included Client Audience field, I have added the client ID of Bob. There is another similar field next to it called Included Custom Audience. This is used when you want to add some custom name as audience like the subdomain of your web service or 3rd party service instead of client ID. How do you validate the aud claim is up to you so any arbitrary name is allowed in this field if you are not specifying the client ID.

Assigning Client Scope To Alice

Go to Clients > alice > Client scopes (tab) > Add client scope > Select untrusted-audience and assign it as an optional client scope.

Result

With Scope



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic YWxpY2U6RHl3a2daZWZtNVFJVU9NbW0ydzdCY2dMbzB6dzI5TTc=' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials --data scope=untrusted-audience



{
  "exp": 1696072871,
  "iat": 1696072271,
  "jti": "ea0d8b36-4946-497f-ab3b-e5dc07f85c62",
  "iss": "http://localhost:8080/realms/master",
  "aud": "bob",
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "scope": "untrusted-audience",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

Now Bob can verify that he is the intended recipient of this token. Also, Bob cannot misuse it for invoking API of Charles if Charles is validating the aud claim. Similarly, to invoke API of Charles, Alice can create another optional client scope and create an audience mapper in it to add Charles' client ID. In this way, Alice will have control over who to include in the audience by accordingly setting the scope.

Multiple Audiences

If Bob and Charles trusts each other and if Alice wants to use the same access token to invoke APIs of both Bob and Charles, instead of creating optional client scope for each, she can just create another audience mapper to the existing client scope.



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic YWxpY2U6RHl3a2daZWZtNVFJVU9NbW0ydzdCY2dMbzB6dzI5TTc=' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials --data scope=untrusted-audience



{
  "exp": 1696094105,
  "iat": 1696093505,
  "jti": "8e90c92b-28d8-4da2-a527-37bc7d23fcec",
  "iss": "http://localhost:8080/realms/master",
  "aud": [
    "bob",
    "charles"
  ],
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "scope": "untrusted-audience",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

Without Scope

I have omitted scope as the parameter so no audience will be included.



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic d2F0Y2hkb2dzOnlMdGVLR0U4VzJiWVVObjRYbEUwMHFNaHlZNVdiemhZ' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials



{
  "exp": 1696097766,
  "iat": 1696097166,
  "jti": "4966e248-5467-4412-b6d1-8d08d3807750",
  "iss": "http://localhost:8080/realms/master",
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "scope": "",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

Automatically Add Audience

If you have a large number of clients in your organization, it becomes impractical to create and manage hardcoded audience for each. The Audience Resolve protocol mapper automatically adds client ID as an audience if following conditions are true:

The client must have at least one client role created on itself. (Let's call this role as common-client-role, I'll use this name later.)
The client that you are using must have a role-scope mapping for that role.
The user must be assigned that role.

Let's satisfy all 3 conditions.

Creating a client role on Bob

Creating an optional client scope for Alice

Go to this client scope, there's a tab called Scope which is empty. There, click on Assign role, filter roles by client and assign the role that you created on Bob. This is called role-scope mapping.

Client Scope Assignment

Assign this client scope to alice as optional.

User Role Assignment

I am using client credentials flow to obtain an access token, so who and where is the user in my case? When you enable Service Account Roles to enable Client Credentials Flow for your client, Keycloak automatically creates a user called service-account-clientID. If I go to Alice, there's a tab called Service account roles. This is where I will assign the role common-client-role to the user service-account-alice.

Result

With Scope



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic YWxpY2U6RHl3a2daZWZtNVFJVU9NbW0ydzdCY2dMbzB6dzI5TTc=' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials --data scope=auto-add-audience



{
  "exp": 1696112410,
  "iat": 1696111810,
  "jti": "eeb61dbe-3a49-4147-9191-c34c39598a52",
  "iss": "http://localhost:8080/realms/master",
  "aud": "bob",
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "resource_access": {
    "bob": {
      "roles": [
        "common-client-role"
      ]
    }
  },
  "scope": "auto-add-audience",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

Multiple Audiences

To also include Charles as an audience, Alice can create and assign another optional client scope on herself and create a role-scope mapping in that client scope and on the user. For role-scope mapping, just like Bob, Charles also has to create at least one client role on himself.

Alice can accordingly specify scopes to include either or both. Here I'm sending 2 scopes to add both Bob's and Charles's client ID as an audience.



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic YWxpY2U6RHl3a2daZWZtNVFJVU9NbW0ydzdCY2dMbzB6dzI5TTc=' --header 'Content-Type: application/x-www-form-urlencoded' --data 'grant_type=client_credentials&scope=auto-add-audience+another-scope-to-include-charles'



{
  "exp": 1696114210,
  "iat": 1696113610,
  "jti": "05a2906b-f2d2-4011-970a-7cff0be38b6e",
  "iss": "http://localhost:8080/realms/master",
  "aud": [
    "bob",
    "charles"
  ],
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "resource_access": {
    "bob": {
      "roles": [
        "common-client-role"
      ]
    },
    "charles": {
      "roles": [
        "charles-role"
      ]
    }
  },
  "scope": "auto-add-audience another-scope-to-include-charles",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

Multiple role-scope mapping

Alice can also create multiple role-scope mappings within a single client scope to add both Bob and Charles as an audience if Bob and Charles trust each other. Trust between Bob and Charles matters because Charles will know that Bob will not misuse Alice's token to invoke his API.



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic YWxpY2U6RHl3a2daZWZtNVFJVU9NbW0ydzdCY2dMbzB6dzI5TTc=' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials --data scope=auto-add-audience



{
  "exp": 1696097015,
  "iat": 1696096955,
  "jti": "02abe148-7e41-429a-91e3-a6c60249a3af",
  "iss": "http://localhost:8080/realms/master",
  "aud": [
    "bob",
    "charles"
  ],
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "resource_access": {
    "bob": {
      "roles": [
        "common-client-role"
      ]
    },
    "charles": {
      "roles": [
        "charles-role"
      ]
    }
  },
  "scope": "auto-add-audience",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}

Without Scope



curl --request POST --url http://localhost:8080/realms/master/protocol/openid-connect/token --header 'Authorization: Basic YWxpY2U6RHl3a2daZWZtNVFJVU9NbW0ydzdCY2dMbzB6dzI5TTc=' --header 'Content-Type: application/x-www-form-urlencoded' --data grant_type=client_credentials



{
  "exp": 1696116071,
  "iat": 1696115471,
  "jti": "33cba1d0-eae3-415c-bc16-f0ae804bc67c",
  "iss": "http://localhost:8080/realms/master",
  "sub": "9baa1d4f-982c-4d1b-b1bd-af8455234d29",
  "typ": "Bearer",
  "azp": "alice",
  "acr": "1",
  "allowed-origins": [
    "/*"
  ],
  "scope": "",
  "clientHost": "172.17.0.1",
  "clientAddress": "172.17.0.1",
  "client_id": "alice"
}