Security

EmbraceSQL also embraces security 👮.

However, we don't pretend to know your security requirements, so we provide the ability to inject middleware into the EmbraceSQL processing stack to let you secure however you

Database Security

Most folks won't do this, but you plenty well can have a database user for each of your actual users, and collect their username and password, send those along on the database connection URL (make sure HTTPS!) and use the database as the security provider.

Old 🎓.

ROLE Based Security

The SQL standard has a notion of ROLE that you can use to set permissions within your database. EmbraceSQL comes out of the box with ROLE based security available.

The idea is straightforward, when you go to invoke an EmbraceSQL request, you supply a ROLE header, and that becomes the database ROLE for the invocation of the query.

This way one single service account can switch security level on the fly.

import { MiddlewareContext, Next } from "./types";

/**
 * Database ROLE based access control.
 *
 * If a ROLE header is supplied, switch to that ROLE for the current
 * transaction.
 */
export default async function databaseRole(
  context: MiddlewareContext,
  next: Next,
) {
  // ROLE header is well known -- and generated -- header
  if (context.request?.options?.headers?.ROLE) {
    // call into the database in the current transaction setting the
    // role for the remainder of the transaction
    // the 'actual' query will run with this ROLE set, and this different
    // permissions that your initial database connection
    await context.sql.unsafe(
      `SET LOCAL SESSION AUTHORIZATION '${context.request.options.headers.ROLE}';`,
    );
  }
  return next();
}

Here is an example of using a ROLE header in a server side scenario.

import { Database } from "../../src/marshalling";

describe("The database can", () => {
  let db: Database;
  beforeAll(async () => {
    // no spanning transaction to roll back in this test
    // this is read only and testing the 'live' path
    // that would be used in an actual application
    db = await Database.connect(
      "postgres://postgres:postgres@localhost:5432/marshalling",
    );
  });
  afterAll(async () => {
    await db.disconnect();
  });
  it("call a proc with an allowed role", async () => {
    const ret = await db.Api.Procedures.Echo.call(
      {
        message: "Hello",
      },
      { headers: { ROLE: "postgres" } },
    );
    expect(ret).toBe("Hello");
  });
  it("call a proc with an disallowed role", async () => {
    await expect(async () => {
      const ret = await db.Api.Procedures.Echo.call(
        {
          message: "Hello",
        },
        { headers: { ROLE: "no_access" } },
      );
      console.assert(ret);
    }).rejects.toThrow();
  });
});

Here is an example of passing along a ROLE via HTTP client. Note that for this to be a secure security approach, you will need to prevent client side tampering.

import { EmbraceSQLExpressApp, Database } from "../../src/marshalling";
import { EmbraceSQLClient } from "../../src/marshalling-browser";
import { Express } from "express";
import { Server } from "http";

describe("The client can", () => {
  const postgresUrl = "postgres://postgres:postgres@localhost:5432/marshalling";
  const PORT = "4445";
  let app: Express;
  let server: Server;
  let database: Database;
  let client: EmbraceSQLClient;
  beforeAll(async () => {
    database = await Database.connect(postgresUrl);
    // running express in process
    app = await EmbraceSQLExpressApp(postgresUrl, database);
    // use this client
    client = new EmbraceSQLClient({ url: `http://localhost:${PORT}` });
    return new Promise<void>((resolve) => {
      server = app.listen(PORT, () => {
        resolve();
      });
    });
  });
  afterAll(async () => {
    await database.disconnect();
    return new Promise<void>((resolve) => {
      server.close(() => resolve());
    });
  });
  describe("per call", () => {
    it("call a proc with an allowed role", async () => {
      const ret = await client.Api.Procedures.Echo.call(
        {
          message: "Hello",
        },
        { headers: { ROLE: "postgres" } },
      );
      expect(ret).toBe("Hello");
    });
    it("call a proc with an disallowed role", async () => {
      await expect(async () => {
        const ret = await client.Api.Procedures.Echo.call(
          {
            message: "Hello",
          },
          { headers: { ROLE: "no_access" } },
        );
        console.assert(ret);
      }).rejects.toThrow();
    });
  });
  describe("per client", () => {
    it("call a proc with an allowed role", async () => {
      const client = new EmbraceSQLClient({
        url: `http://localhost:${PORT}`,
        options: {
          headers: { ROLE: "postgres" },
        },
      });
      const ret = await client.Api.Procedures.Echo.call({
        message: "Hello",
      });
      expect(ret).toBe("Hello");
    });
    it("call a proc with an disallowed role", async () => {
      await expect(async () => {
        const client = new EmbraceSQLClient({
          url: `http://localhost:${PORT}`,
          options: {
            headers: { ROLE: "no_access" },
          },
        });
        const ret = await client.Api.Procedures.Echo.call({
          message: "Hello",
        });
        console.assert(ret);
      }).rejects.toThrow();
    });
  });
});

Express Middleware

If you are using EmbraceSQL mounted in Express, everything you already know about middleware still applies for authentication and authorization.

Some ideas:

• Use JWT to identify individual users and then set the database ROLE header.
• Use user session cookies to set the database ROLE header.

NextJS Functions

Similar to Express, in your route handler running server side, you can intercept and set the ROLE header before transmission to the database.