Group-based auth with Lakebase Data API

[Malthe]

While Lakebase supports group-based authentication, this doesn't seem to have carried over to Lakebase Data API.

Here, we must assign on the individual user or service principal,

• Create principal
• Setup authenticator access
• Schema- and table grants

This means that it becomes rather a big administrative burden to set up for multiple users or service principals, as well as disorganized.

Will this be supported in the future?

[tom_n · Accepted solution]

Hi Malthe, you're right, the Data API doesn't support group roles yet.

The reason is how it authenticates. Over a normal Postgres connection you can log in as a group role (created with `databricks_create_role('<group>', 'GROUP')`). The Data API works differently: it always connects as `authenticator` and then switches to the role named in the caller's OAuth token. That token identifies the user or service principal, not their groups, so a principal whose only access is through a group role gets a `401 PGRST301 invalid token permissions`. The team knows about the gap and is working on it, but there's no public date yet.

Until then, you can use Postgres role inheritance so all grants live in one place. I've tested this pattern against a Data API endpoint with service principals:

-- One-off: a NOLOGIN role that holds the privileges
CREATE ROLE app_rw NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;

-- Per identity: three statements, no table grants
SELECT databricks_create_role('<user-email or SP application ID>', 'USER' | 'SERVICE_PRINCIPAL');
GRANT app_rw TO "<identity>";
GRANT "<identity>" TO authenticator;

When the Data API switches to the caller's role, that role inherits everything granted to app_rw. Tables not granted to app_rw still return 403. REVOKE app_rw FROM "<identity>" removes access straight away, and thanks to the default privileges, tables you create later are covered too. New tables can take a short while to appear in the Data API while its schema cache refreshes.

Schema and table permissions are then managed once, and onboarding someone new is just the three statements above. You can also script that part: pull your group's members from the SCIM API and run the statements for anyone who doesn't have a role yet. That gives you a rough group sync until native support arrives.

Docs:
- Data API auth: https://docs.databricks.com/aws/en/oltp/projects/data-api
- Postgres roles (incl. group roles): https://docs.databricks.com/aws/en/oltp/projects/postgres-roles