Does this library reflect permissions?

[benjie]

PR available.

Is your feature request related to a problem? Please describe.

Hi, I've tried searching the repository for grant, grants, rbac, permissions to no avail; I was wondering if pgtyped understands role-based access control; for example:

create extension if not exists citext;

create table user_emails (
  id int primary key generated always as identity,
  address citext not null unique,
  receives_notifications boolean not,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

revoke all on user_emails from some_role;
grant
  select,
  insert (address, receives_notifications),
  update (receives_notifications),
  delete
on user_emails to some_role;

In this setup, we can know that insert into user_emails (address) values ('[email protected]') is fine; but insert into user_emails (id, address) values (1, '[email protected]') (when running with some_role) is not allowed.

Can pgtyped pick up on this? If so, is there some special setup I need? If not, how hard would it be for me to add this, either to core or via an external mechanism?

Describe the solution you'd like

I'd like to know that this is valid:

const query = sql`INSERT INTO user_emails (address) VALUES $$users(address) RETURNING id`;

And this should raise a TypeScript error (since insert to id column is not granted):

const query = sql`INSERT INTO user_emails (id, address) VALUES $$users(id, address) RETURNING id`;

Additional context

I searched the codebase for pg_class and information_schema and on finding no results got really curious and dug a bit deeper... From what I can tell from a quick scan of the code, you seem to be using the raw PostgreSQL protocol to prepare queries and then using the descriptions that PostgreSQL gives you back to determine the parameter/return types? This is a cunning way of side-stepping the need for introspection/a parser/etc! I've not yet dabbled with the wire protocol but it has been on my TODO list for a while now. That said, I don't think the wire protocol contains sufficient detail for this (I assume it contains similar details to the pg_prepared_statements view) - do you agree? If so, I think we might have a few options available short of adding a parser and introspection library... Would love to discuss further if you're interested. I think using EXPLAIN EXECUTE could help here without adding extra dependencies/complexity; I've raised an initial PR: #563