Future tables and permissions

This document explains how to configure permissions for future tables created by any user in Cloud SQL for PostgreSQL, ensuring Atlan can crawl metadata from newly created database objects.

Can Atlan crawl future tables created by any user?

Cloud SQL for PostgreSQL doesn't provide a single command to grant access to future tables created by any user on a global level.

You need to alter the default privileges of every current and future user that creates tables. This enables the database role you created for integrating with Atlan to have access to the tables created by those users by default. For example:

ALTER DEFAULT PRIVILEGES FOR USER <USER_CREATING_TABLES> IN SCHEMA <SCHEMA> GRANT SELECT, REFERENCES ON TABLES TO atlan_user_role;

However, altering the default privileges of every current and future user may not be sustainable or controlled from a single place. To automate the granting of permissions, you can:

Grant function

You can automate the granting of privileges to the database role you created for integrating with Atlan. Note that the function below is located in the public schema. You can use any schema you want to store this function:

• Set custom conditions using PL/pgSQL to skip or enable only certain schemas or tables:

  CREATE OR REPLACE FUNCTION public.grant_permissions_on_all_schemas()
  RETURNS void AS $$
  DECLARE
      schema_name text;
  BEGIN
      FOR schema_name IN (SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')
      LOOP
          EXECUTE format('GRANT USAGE ON SCHEMA %I TO atlan_user_role', schema_name);
          -- grant access to all tables, including views, materialized views
          EXECUTE format('GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA %I TO atlan_user_role', schema_name);
      END LOOP;
  END;
  $$ LANGUAGE plpgsql;

• Next, set up a periodic schedule and execute this function on a daily or hourly basis to enable the database role to have access to all new schemas or tables:

  select public.grant_permissions_on_all_schemas();

Event triggers

You can create an event trigger on any CREATE SCHEMA or CREATE TABLE command. This automation enables minimal lag, and you won't need to set up a schedule to run the grant function mentioned earlier.

Note that the event trigger only listens to new create event triggers. You still need to run the grant function mentioned earlier to enable the database role to have access to all current schemas or tables.

-- Function to grant permissions on a specific schema
CREATE OR REPLACE FUNCTION public.grant_permissions_on_schema()
RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE SCHEMA'
    LOOP
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO atlan_user_role', obj.object_identity);
        RAISE NOTICE 'Granted USAGE on schema % to atlan_user_role', obj.object_identity;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Event trigger for new schemas
CREATE EVENT TRIGGER grant_permissions_on_new_schema ON ddl_command_end
WHEN TAG IN ('CREATE SCHEMA')
EXECUTE FUNCTION public.grant_permissions_on_schema();

-- Function to grant permissions on a specific table
CREATE OR REPLACE FUNCTION public.grant_permissions_on_table()
RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE TABLE'
                                                                or command_tag = 'CREATE VIEW'
                                                                or command_tag = 'CREATE TABLE AS'
                                                                or command_tag = 'CREATE MATERIALIZED VIEW'
    LOOP
        EXECUTE format('GRANT SELECT, REFERENCES ON TABLE %s TO atlan_user_role', obj.object_identity);
        RAISE NOTICE 'Granted SELECT, REFERENCES on table % to atlan_user_role', obj.object_identity;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Event trigger for new tables, views, mat views
CREATE EVENT TRIGGER grant_permissions_on_new_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE VIEW', 'CREATE TABLE AS', 'CREATE MATERIALIZED VIEW')
EXECUTE FUNCTION public.grant_permissions_on_table();