Qlik Replicate and PostgreSQL source endpoint: DDL operation fails with ERROR: permission denied for table attrep_ddl_audit

[john_wang]

The PostgreSQL source endpoint is set up with a privileged account (account with a superuser role) used in the source endpoint connection.  After the task's first successful run, all of the regular user (account without a superuser role) cannot perform any DDL operations in the database,regardless if the table is included in the replication task or not, or even create a new table. Below errors will show up upon any DDL operation.

In the meanwhile, DML operations continue to work; the INSERT/UPDATE/DELETE changing records can be captured and applied to target sides successfully.

Errors:

ERROR: permission denied for table attrep_ddl_audit. The SQL statement 'insert into public.attrep_ddl_audit values ...'.

Resolution:

There are two alternatives. Apply one. 

Pros and Cons:

• Update permissions. This is a straightforward but potentially work-intensive solution. Give all regular users who need to run tasks the required permissions.

• Execute the function definition with "security definer" option by a privileged account. While this option is simpler, it must be performed carefully as not to enable misuse. 

 

Update permissions

In this scenario Qlik Replicate is in fact using the default option "security invoker" in function definition.
Grant the following permissions to the non-privileged account, see Qlik Replicate User Guide:

GRANT INSERT ON attrep_ddl_audit to <non-privileged-user>;
GRANT DELETE ON attrep_ddl_audit to <non-privileged-user>;
GRANT USAGE ON attrep_ddl_audit_c_key_seq TO <non-privileged-user>;

 

Security definer and privileged account

WARNING! Writing SECURITY DEFINER Functions Safely

Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be misused. For security, search_path should be set to exclude any schemas writable by untrusted users.

Execute the function definition with "security definer" option by a privileged account, for example an account with superuser privilege. See the complete function definition code is as below.

The code is generated by Qlik Replicate 2023.5, only "security definer" option is added manually.

-- DROP FUNCTION public.attrep_intercept_ddl();

CREATE OR REPLACE FUNCTION public.attrep_intercept_ddl()

 RETURNS event_trigger

 LANGUAGE plpgsql security definer

AS $function$

declare _qry text;

BEGIN

  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE' or tg_tag='TRUNCATE') then

    SELECT current_query() into _qry;

    insert into public.attrep_ddl_audit

    values

    (

    default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry

    );

    delete from public.attrep_ddl_audit;

 end if;

END;

$function$

;

This customization is provided as is. Qlik Support cannot provide continued support for the solution. For assistance, reach out to your DBA and/or PostgreSQL related services.

Internal Investigation ID(s):

#00021532, #00123792

Environment:

• Qlik Replicate All versions
• PostgreSQL All versions ("security definer" was introduced from version 8.0)