Abstract your database tables

For a long time I wanted to try an underused pattern to query a SQL database from a software application. I was reminded of it when I had to integrate pgledger, which I discovered recently and which uses this pattern. The common SQL access pattern in software is to perform SELECT commands to read data from the database tables or views, and INSERT / UPDATE / DELETE to write to tables.

But have you ever hit this kind of problem?

Database models at the application level are no longer in sync with the database, and the software breaks at deployment
You have performance issues on queries and you need to redeploy the app to fix SQL queries
You worry a new feature might incorrectly modify table data

The pattern that could solve all these problems is the following.

Instead of giving your application access to real tables:

Every read operation must be done through a view
Every write must pass through a function that performs the concrete INSERT / UPDATE / DELETE operations

The application does not have any privilege on the underlying tables. It has only SELECT privilege on views, and functions are declared as SECURITY DEFINER (Postgresql specific) so they run under the identity of the user who created them and not the one calling them.

Example

Create the underlying table and the related function and view.

create schema schema_example;
set search_path = schema_example;

create table items (
    id uuid primary key,
    name text not null,
    price int not null,
    active bool not null default true
);

create or replace function insert_item(name text, price int)
returns uuid
language sql
security definer
as $$
    -- It's important to schema-qualify all tables in SECURITY DEFINER
    -- functions to prevent search_path manipulation attacks.
    insert into schema_example.items (id, name, price)
    values (gen_random_uuid(), name, price)
    returning id;
$$;

create view items_v1 as
    select id, name, price from items where active = true;

Then grant privileges to a separate user only on the view (function access should be granted by default).

create user user_example with password 'user_example';
grant usage on schema schema_example to user_example;
grant select on items_v1 to user_example;

Finally try them under the user_example user. Note that direct access to the table is denied.

set search_path = schema_example;
SET
select insert_item('table', 200);
             insert_item              
══════════════════════════════════════
 8b88b634-5640-4198-8510-1ce86c15652b
(1 row)

select * from items_v1;
                  id                  │ name  │ price 
══════════════════════════════════════╪═══════╪═══════
 8b88b634-5640-4198-8510-1ce86c15652b │ table │   200
(1 row)

select * from items;
ERROR:  permission denied for table items

Advantages

Strong contract, it allows defining a clear contract between application and database, no more uncontrolled INSERT / UPDATE / DELETE access
Underlying table structure modification without breaking the contract, and managed by a specialized data team
Versioning, see below

Versioning

Views / functions can be versioned, so new code can safely and explicitly use them.

You just need to check if the database is at the right version before deployment. In case of rollback the old versions are used. The old versions can be removed when you’re sure they are no longer used.

App reads the items_v6 view and depends on database schema version >=122
Database team releases new items_v7 in database schema version 123
Development team releases a new version of the app which uses items_v7 and the app now depends on database version >=123
New app version goes in production, database version is checked beforehand
If app needs to be rolled back, it will find items_v6 which still exists

items_v6 can be removed later when you’re sure it’s no longer used.

Conclusion

It’s not for every use case… It can be good for critical applications (multi-tenant systems, financial applications, etc), but adds difficulties (no more ORM!). It could be especially relevant with AI development as an additional security layer against buggy LLM-generated code.