One View Per Layer: Four Sharp Edges I Found in My Own Code

There is a layer in my database called 1.

Somebody created it, presumably by accident, and it sat there for months looking harmless. It was the only layer in the system that never served a single tile, and nobody noticed, because it was empty anyway.

That layer turned out to be a symptom of a SQL injection vulnerability. This post is about the design that produced it — which I still think is a good design — and the four things I got wrong inside it.

The setup

A web GIS with about 2.7 million features: 1.8 million points, 697,000 lines, 172,000 polygons. Users create layers through the UI, upload data into them, edit geometry, and expect to see it on a map.

The features do not live in a table per layer. They live in three tables — one for points, one for lines, one for polygons — with a layer_id foreign key and a JSON column for attributes:

project_pointfeature     1,820,288 rows
project_linefeature        697,009 rows
project_polygonfeature     171,830 rows

That's a deliberate trade. A table per layer means DDL every time a user clicks "new layer", a migration story that never ends, and a schema that drifts. Three generic tables mean one schema, one set of indexes, and layers that are just rows in a metadata table.

The cost lands on the tile server.

The pattern

Martin serves vector tiles from PostGIS. Point it at a database and it discovers spatial tables and views and publishes each as an MVT endpoint. It can be told to publish views but not tables:

postgres:
  auto_publish:
    from_schemas: [public]
    publish_tables: false
  reload_interval: 5s

So: give every layer its own view. A Django post_save signal on the Layer model creates it:

CREATE OR REPLACE VIEW t19_saobracajni_znakovi AS
    SELECT f.id, f.feature_attrs, f.geom, f.layer_id,
           l.name AS layer_name, lg.name AS layer_group_name,
           p.title AS project_title
    FROM project_pointfeature f
        JOIN project_layer l  ON f.layer_id = l.id
        JOIN project_layergroup lg ON l.layer_group_id = lg.id
        JOIN project_project p ON lg.project_id = p.id
    WHERE f.layer_id = 81

A user creates a layer in the browser. Five seconds later — one reload_interval — it is a live tile endpoint. No migration, no deploy, no restart. There are 106 of these now.

I still like this. Everything below is what it cost.

Edge 1: the layer named 1

The signal built the view name like this:

view_name = f"{instance.name.replace(' ', '_')}"
cursor.execute(f"CREATE OR REPLACE VIEW {view_name} AS ...")

Layer.name is a CharField filled in by users, with no validation on it. It goes straight into DDL through an f-string.

A layer named x" AS SELECT 1; DROP TABLE project_layer; -- executes on save, with whatever privileges the application's database role happens to hold.

I did not find this by thinking about attackers. I found it because of the layer called 1, whose view was missing:

CREATE OR REPLACE VIEW 1 AS ...
-- ERROR: syntax error at or near "1"

An unquoted identifier can't start with a digit. The exception was caught, logged, and swallowed, and that layer quietly had no tiles. A user-supplied string that breaks SQL syntax is the same string that could complete it. The failure was the tell.

The fix is psycopg2.sql:

from psycopg2 import sql

stmt = sql.SQL("CREATE OR REPLACE VIEW {view} AS ... WHERE f.layer_id = {lid}").format(
    view=sql.Identifier(view_name),
    lid=sql.Literal(layer.id),
)

Rendered with a hostile name, the whole thing lands inside one quoted identifier with the quote doubled:

CREATE OR REPLACE VIEW "x""_as_select_1;_drop_table_project_layer;_--" AS SELECT ...

And "1" is now a perfectly legal view name, so that layer works too.

Edge 2: quoting changes your names

This is the part that would have caused a bad afternoon if I'd shipped the obvious fix.

An unquoted identifier in Postgres is folded to lower case. A quoted one is not. Every one of those 106 views was created unquoted, so they're all lower case in the catalog — while 23 layers have capital letters in their names.

Switch naively to sql.Identifier and T53_Traffic_Cameras stops resolving to the existing t53_traffic_cameras and creates a second view beside it. The old one keeps existing. Martin keeps publishing both. Half your layers quietly fork.

So the derivation has to reproduce what Postgres was doing implicitly:

def normalize_layer_name(layer_name):
    name = layer_name.strip().replace(' ', '_')
    # Postgres folds only ASCII A-Z. str.lower() would also fold Cyrillic
    # and diverge from the names already in the catalog.
    return ''.join(c.lower() if 'A' <= c <= 'Z' else c for c in name)

That ASCII-only detail matters here: four layers have Cyrillic names, and str.lower() would have renamed them. The existing views were created by Postgres's rule, not Python's, and the two disagree outside ASCII.

I verified it before merging by running the new function over every layer and diffing against the catalog: 102 layers, 0 names changed. That check took a minute and was the only thing standing between me and 23 orphaned views.

Edge 3: two code paths, two shapes

There were two places that created these views: the signal, and a management command for bulk regeneration. Over time they diverged. The command included symbology columns for point styling; the signal didn't.

The database ended up holding two shapes of the same thing — 15 views with symbology columns, 24 identical point layers without.

That's not just untidy, because of a rule worth memorising:

CREATE OR REPLACE VIEW can add columns at the end. It cannot remove them, and it cannot reorder them.

So saving a layer whose view had the "wrong" shape failed with cannot drop columns from view. Caught, logged, swallowed — and the view silently kept its old definition. This had already caused a 500 on an unrelated endpoint, because the failed statement poisoned the caller's transaction.

Two fixes. First, the column set is no longer a flag anyone can pass; it's derived from the geometry type, because only the point table has a symbology_id column — the command had been passing with_symbology=True for lines and polygons too, where it could only ever have failed.

Second, and this is the useful bit: the optional columns moved to the end of the SELECT list. Because CREATE OR REPLACE can append, 24 views could be brought into line with no interruption at all. Only the 15 that needed reordering required DROP + CREATE:

102 layers, 63 already aligned
 24 replaced in place   (no downtime)
 15 required DROP       (brief lock, inside a transaction)

Column order is invisible to clients — MVT attributes are named. Choosing it deliberately turned most of a migration into a no-op.

Edge 4: the view namespace is global, the layer namespace wasn't

Layer names were unique per project. View names are unique per database.

So two projects both had a layer called tacke. Both mapped to one view. Whichever was saved last owned it, and the other layer served the wrong project's features — with no error anywhere, because from Postgres's point of view nothing was wrong.

The tempting fix is to rename the views: layer_81 instead of tacke. It's correct, and it's a coordinated deploy — tiles are requested by name, so every client has to change on the same day.

The cheaper fix follows from noticing why the frontend works at all: it builds the tile URL from the layer name it reads from the API. If layer names are unique, tile addresses are unique for free. So the constraint belongs on the layer name, not on a new naming scheme:

def validate_name(self, value):
    holder = layer_holding_view_name(tile_view_name(value),
                                     exclude_pk=self.instance.pk if self.instance else None)
    if holder:
        raise ValidationError(...)

Comparing the derived name, so Tacke and tacke collide, and so do centralne linije and centralne_linije. One of the two existing duplicates was an empty layer in a test project; renaming it cost nothing, and the frontend followed automatically because it reads the name from the API rather than remembering it.

While I was in there: renaming a layer created a view under the new name and left the old one behind, publishing a source no layer pointed at. A pre_save now remembers the previous name so post_save can drop it — unless another layer is using it.

Would I build it this way again

Yes, with the edges filed off.

A generic feature table plus a view per layer gets you user-created layers that become tile endpoints in seconds, without DDL migrations or deploys, and Martin's reload_interval does the discovery for free. For an internal tool where people create layers as part of their work, that's the right shape.

But it means your users write DDL identifiers, indirectly, by typing a name into a form. Once you accept that, four things follow, and I got all four wrong first:

• Compose DDL with sql.Identifier, never an f-string. The failure that reveals it may look like a syntax error, not an attack.
• If you're adding quoting to something that ran unquoted, reproduce the old folding exactly and diff every existing name before you ship.
• Put optional columns last, so CREATE OR REPLACE can add them without a drop.
• Check whether your derived namespace is wider than the namespace you enforce uniqueness in. Ours was, by exactly one level.

The layer named 1 serves tiles now. It's still empty.