[Discussion] Column-level permissions: table-level or row-level?

[abnegate]

Hey everyone 👋

We're adding column-level permissions to Appwrite Databases, and there's one design decision I'd like your input on before we lock anything in:

Should column permissions live on the table, or on each row?

The answer changes the API, the Console UI, how queries behave and what it costs to run. More detail below. If you only have a minute, skip to the questions at the end.

The problem

Permissions today decide whether you can see a row, never which parts of it. If a row has a column some readers shouldn't see (salary, email, phone, internal notes), you currently have to move that column into its own table, give that table its own permissions, and join the two back together in your app.

What's the same either way

• A permission can optionally name a column. No column means the whole row, so every existing permission keeps meaning exactly what it means today.
• It's opt-in per table with a new columnSecurity flag, next to rowSecurity. If you don't turn it on, nothing changes, including performance.
• Columns you can't read are left out of the response, and you get the rest of the row.
• A column-scoped update lets a role change only that column.
• delete stays row-level.

Rough API shape, not final:

Permission.read(Role.any())                   // whole row, same as today
Permission.read(Role.any(), 'name')           // only the name column
Permission.update(Role.team('hr'), 'salary')  // may only change salary

Option A: table-level

Column grants are set on the table. Row permissions still decide which rows you see. The table decides which columns of those rows you see.

In other words, who can see a column depends on your role, never on the row.

await tablesDB.createTable({
    databaseId: 'main',
    tableId: 'staff',
    name: 'Staff',
    permissions: [
        Permission.read(Role.users(), 'name'),
        Permission.read(Role.users(), 'title'),
        Permission.read(Role.team('hr')),             // HR sees everything
        Permission.update(Role.team('hr'), 'salary'), // HR can edit salary, nothing else
    ],
    columnSecurity: true,
});

As a signed-in user who isn't in HR:

await tablesDB.getRow({ databaseId: 'main', tableId: 'staff', rowId: 'ada' });
// { $id: 'ada', name: 'Ada', title: 'Engineer' }    ← no salary

await tablesDB.listRows({
    databaseId: 'main',
    tableId: 'staff',
    queries: [Query.greaterThan('salary', 100000)],
});
// Error: Missing "read" permission for column "salary"

HR gets full rows and can filter, sort and aggregate on salary.

Another way to shape this option is to set permissions on the column itself:

await tablesDB.createIntegerColumn({
    databaseId: 'main',
    tableId: 'staff',
    key: 'salary',
    required: false,
    permissions: [
        Permission.read(Role.team('hr')),
        Permission.update(Role.team('hr')),
    ],
});

Good

• It's the same model as Postgres/Supabase, Hasura and Directus, so it'll feel familiar.
• It's easy to reason about. In the Console it's one grid per table: roles down the side, columns across the top.
• The columns you can see are worked out once per request, not once per row. If you query on a column you can't read, you get a clear error, never quietly partial results.
• How row permissions are stored doesn't change, so we don't need to migrate every table, and renaming a column is a single metadata update.

Not so good

• It can't handle visibility that depends on the row: "the owner sees their own email", or "members of this row's team see its notes". For those you're back to splitting the table.

Option B: row-level

Column grants can be set on the table and on individual rows, the same way row permissions work today.

In other words, it's rowSecurity, extended down to columns.

await tablesDB.createTable({
    databaseId: 'main',
    tableId: 'profiles',
    name: 'Profiles',
    permissions: [Permission.create(Role.users())],
    rowSecurity: true,
    columnSecurity: true,
});

await tablesDB.createRow({
    databaseId: 'main',
    tableId: 'profiles',
    rowId: userId,
    data: { name: 'Ada', avatar: '…', email: 'ada@example.com', phone: '+44…' },
    permissions: [
        Permission.read(Role.users(), 'name'),
        Permission.read(Role.users(), 'avatar'),
        Permission.read(Role.user(userId)),            // the owner sees everything
        Permission.update(Role.user(userId), 'phone'),
    ],
});

As any other user:

await tablesDB.getRow({ databaseId: 'main', tableId: 'profiles', rowId: userId });
// { $id: '…', name: 'Ada', avatar: '…' }

await tablesDB.listRows({
    databaseId: 'main',
    tableId: 'profiles',
    queries: [Query.equal('email', 'ada@example.com')],
});
// { total: 0, rows: [] }    ← a row only matches if you can read its email

The owner gets the full row.

Good

• It can express anything row permissions can, per column. Owner-only and per-team fields work without splitting tables.
• It's symmetric with rowSecurity: the table and the row accept the same permissions.

Not so good

• Every filter, sort and aggregate on a restricted column has to be checked row by row. You get back whatever you're allowed to see, and that can be surprising. For example, sum('salary') only adds up the rows where you can read salary.
• The same column can be on one row and missing from the next. That makes building grids and tables harder.
• There's no single place in the Console that shows who can see a column. You'd have to dig through individual rows.
• Reads and queries cost more per row on tables that turn it on.
• Every table's permission storage changes, and columns need stable internal IDs so a rename doesn't rewrite every permission that mentions the column. Because columnSecurity is opt-in, a table only gets migrated when it's turned on, but it's still a lot more work to ship.

Side by side

	A: table-level	B: row-level
"HR can see salary"	✅	✅
"Owners see their own email"	⚠️ split the table	✅
"Members of this row's team see its notes"	⚠️ split the table	✅
Querying a column you can't read	Error	Checked per row, partial results
Response shape	The same on every row	Can differ from row to row
Console UI	Roles × columns grid on the table	Column picker on every row permission
Read overhead	Once per request	Once per row
Storage changes	Table metadata only	Every table's permissions

Option C: A now, B later

Ship table-level first, with a permission format that already carries an optional column. If real use cases need row-level, we add it later without a breaking change.

How others do it

• Postgres / Supabase: column GRANTs per role, at table level. Selecting a column you weren't granted fails, SELECT * included. Supabase calls this an advanced feature and recommends most people use RLS with a separate table instead (docs).
• Hasura: for each role and table, a row filter plus a list of allowed columns. Table-level.
• Directus: for each role and collection, an item filter plus a list of allowed fields. Table-level.
• Firestore: no field-level read rules. A document is readable in full or not at all, so private fields go in a separate document.

Every product we looked at pairs a row filter with a column list set on the table. None of them do per-row column grants. That's why I'm especially keen to hear from you if you'd need B.

Questions

1. A, B or C? The most useful answer is a real use case that needs per-row column visibility, and whether splitting it into a second table would work for you.
2. Columns you can't read: should they be left out of the response (what we're proposing), returned as null, or cause an error when you ask for them explicitly with Query.select()?
3. Querying a column you can't read: should that be an error, or should those rows quietly not match?
4. API shape: Permission.read(role, 'column'), Permission.read(role, ['a', 'b']), or permissions set on the column itself?
5. Column-scoped create: should a role be allowed to create rows but only set certain columns, or should create stay row-only?
6. Turning columnSecurity off: this drops the column restrictions, so a column grant then applies to the whole row and access widens. Is that what you'd expect, or should we block turning it off while column grants still exist?

Prototypes

If you want to dig in:

• Column level permissions utopia-php/database#959 (column names in permissions)
• Column level permissions immutable utopia-php/database#985 (stable column IDs)
• Column permissions #13971 (API)

[steparena]

I think the best approach would be to leave this decision up to the developer.

If a developer selects a column they don't have permission to access, they should be able to decide how Appwrite handles it (strict mode)— either throw an error (on) or silently omit the column (off).

It would also be useful to define what value should be returned when the user doesn't have permission to access the column. For example, if a column is a boolean, the developer might want it to always return false instead of null.

Rather than simply returning null, I think this could work similarly to the existing default null value logic, but with a separate value specifically for unauthorized access. One developer might want null, while another might want false, 0, an empty string, etc.

However, the configured fallback value should still be validated against the column's type. For example, a boolean column could reasonably return either null or false/true, but it should not be possible to configure an empty string as the fallback value for a boolean column. The same type validation that applies to the column's default value should apply here as well.

I would also recommend supporting both levels, table-level and row-level.

This would provide a lot of flexibility while still allowing developers to choose the behavior that makes the most sense for their application.