cancel
Showing results for 
Search instead for 
Did you mean: 
Lakebase Discussions
Ask questions, share challenges, and connect with others working on Lakebase. From troubleshooting to best practices, this is where conversations happen.
cancel
Showing results for 
Search instead for 
Did you mean: 

Lakebase synced tables: how can a database owner/admin configure default SELECT privileges for the m

Pat
Esteemed Contributor

Question

We use Databricks Lakebase synced tables to sync Unity Catalog tables into Lakebase. We want a reader role to automatically receive SELECT access on newly created synced tables, without having to rerun grants whenever another table is added.

Granting access to existing tables works:

GRANT USAGE ON SCHEMA target_schema TO reader_role;
GRANT SELECT ON ALL TABLES IN SCHEMA target_schema TO reader_role;

However, synced tables are owned by a managed role named databricks_writer_<database_oid>, rather than the identity running our migrations. Therefore, setting default privileges for the migration identity does not appear to cover future synced tables.

We tried explicitly configuring defaults for the managed writer role:

ALTER DEFAULT PRIVILEGES
FOR ROLE "databricks_writer_16840"
IN SCHEMA target_schema
GRANT SELECT ON TABLES TO reader_role;

I am running this as an admin and database owner, but it fails with:

ERROR: permission denied to change default privileges

The same statement also fails when executed through Liquibase inside a PostgreSQL DO block:

ERROR: Exception Primary Class: PSQLException
ERROR: Exception Primary Reason:
ERROR: permission denied to change default privileges

Where: SQL statement
"ALTER DEFAULT PRIVILEGES FOR ROLE databricks_writer_16840
 IN SCHEMA target_schema GRANT SELECT ON TABLES TO reader_role"

PL/pgSQL function inline_code_block line 14 at EXECUTE

Thanks,
Pat.

2 REPLIES 2

ThomazNeto
Databricks Partner

Hi Pat,

That error is expected. ALTER DEFAULT PRIVILEGES FOR ROLE can only be run by members of that role.

The Lakebase docs say it in their own example: "only members of table_creator_role_name can run this command". databricks_writer_16840 is the internal role the sync pipeline uses to own synced tables, and your admin identity isn't a member of it. Default privileges are also tied to the role that creates the object, so defaults set for your migration identity will never apply to synced tables.

Two options that work within the documented model:

1. Grant pg_read_all_data to the reader role

databricks_superuser inherits pg_read_all_data, which is how it reads synced tables without per-table grants. Granting the same predefined role to your reader covers every current and future table, synced ones included:

GRANT pg_read_all_data TO reader_role;

The catch is scope. It applies to all schemas in the database, not just target_schema. If other schemas hold data the reader shouldn't see, it's too broad. Test it first with your admin role, since granting predefined roles needs CREATEROLE or admin rights.

2. Re-run the grant right after each synced table is created

Keep your current GRANT SELECT ON ALL TABLES IN SCHEMA target_schema as a step that runs right after the job or migration that creates the synced table. It's idempotent and cheap, and the access stays limited to target_schema. Since you already sync into a dedicated schema, this is the cleanest option if least privilege matters.

Docs:
Synced tables, ownership and granting access: https://docs.databricks.com/aws/en/oltp/projects/sync-tables
Default privileges in Lakebase: https://learn.microsoft.com/en-us/azure/databricks/oltp/projects/manage-roles-permissions
Postgres predefined roles: https://www.postgresql.org/docs/current/predefined-roles.html

Thomaz A. Rossito Neto
Principal Data Architect & AI Strategy — CI&T
thomazn@ciandt.com
linkedin.com/in/thomaz-antonio-rossito-neto | Databricks Solutions Architect Champion

LingeshK
Databricks Employee
Databricks Employee

Hello Pat,

Following up on the answer above from @ThomazNeto. I did a test run, particularly on one option the thread has not covered yet. Thank you for posting the exact statements and both errors, which made the case easy to reproduce

Short answer: Thomaz's diagnosis is right, and both options in that answer worked in my test. An event trigger gives you the behavior you asked for: it grants SELECT on each newly synced table when the sync pipeline creates it, so nothing needs to be rerun.

LingeshK_1-1793672711577.png

 

Why the statement fails:

Q1. "We want a reader role to automatically receive SELECT access on newly created synced tables, without having to rerun grants whenever another table is added."

All three worked in my test:

A view layer in a schema that your migration identity owns also works with ordinary default privileges. Each new synced table still needs its own CREATE VIEW, and a view built with * leaves out columns added to the table later.

-- Once, for the synced tables you have today
GRANT USAGE ON SCHEMA target_schema TO reader_role;
GRANT SELECT ON ALL TABLES IN SCHEMA target_schema TO reader_role;

-- From now on, grant SELECT on each table created in target_schema
CREATE OR REPLACE FUNCTION public.grant_select_on_new_synced_tables()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN
    SELECT * FROM pg_event_trigger_ddl_commands()
    WHERE schema_name = 'target_schema' AND object_type = 'table'
  LOOP
    BEGIN
      EXECUTE format('GRANT SELECT ON %s TO reader_role', obj.object_identity);
    EXCEPTION WHEN OTHERS THEN
      -- Warn and carry on, so the CREATE TABLE of the sync pipeline never fails
      RAISE WARNING 'grant on % failed: %', obj.object_identity, SQLERRM;
    END;
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER grant_select_on_new_synced_tables
  ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE')
  EXECUTE FUNCTION public.grant_select_on_new_synced_tables();

-- After the next synced table appears, this returns true
SELECT has_table_privilege('reader_role', 'target_schema.new_synced_table', 'SELECT');

Before you rely on this:

  • Testing: I ran every statement above on 3 November 2026 on a Lakebase Autoscaling project with Postgres 17.11, as the project owner, with Snapshot synced tables. I did not test Postgres 16, or the Triggered and Continuous modes. No doc page says that the CREATE TABLE of the sync pipeline fires your own triggers. That part rests on the test, so check it again after a platform upgrade.
  • Failure handling: If a ddl_command_end trigger fails, Postgres rolls back the DDL statement, here the CREATE TABLE of the pipeline. The function catches errors and raises a warning instead. I tested that path with a role that does not exist, and the synced table still came online.
  • One trigger per database: Event triggers are global to a particular database, so create one in each database that holds synced tables.

Happy to look at the output if you try the trigger on your side. Results on Postgres 16 or with Continuous mode would also help here.

LK