Troubleshooting PostgreSQL connectivity

Can Atlan crawl future tables created by any user?

PostgreSQL does not provide a single command to grant access to future tables created by any user on a global level.

You will have to alter the default privileges of every current and future user that creates tables. This is to ensure that the database role you created for integrating with Atlan has 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 allow 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 ensure that the database role has 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 will ensure minimal lag, and you will not have to set up a schedule to run the above grant function.

Note that the event trigger only listens to new create event triggers. You will still need to run the grant function above to ensure that the database role has 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();