PostgreSQL - sharing table ownership for RDS or self-managed databases running on Amazon EC2 - Amazon Data Firehose
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Firehose supports database as a source in all Amazon Web Services Regions except China Regions, Amazon GovCloud (US) Regions, and Asia Pacific (Malaysia). This feature is in preview and is subject to change. Do not use it for your production workloads.

PostgreSQL - sharing table ownership for RDS or self-managed databases running on Amazon EC2

This procedure updates tables that you want to use with Firehose so that the ownership is shared between the original owner and the role being used by Firehose. This procedure needs to be called for each table that you want to use with Firehose. This procedure uses the group role that you created with the previous procedure.

Note

Some older database versions may not support the string IF NOT EXISTS in the CREATE PROCEDURE line. In such cases, remove IF NOT EXISTS from the CREATE PROCEDURE and use the rest of the procedure.

CREATE OR REPLACE PROCEDURE grant_shared_ownership(
     p_schema_name TEXT,
    p_table_name TEXT,
    p_group_owner_name TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    l_table_owner TEXT;
BEGIN

    -- Get the owner of the specified table
    SELECT pg_catalog.pg_get_userbyid(c.relowner)
    INTO l_table_owner
    FROM pg_catalog.pg_class c
    WHERE c.relname = p_table_name;

    IF l_table_owner IS NOT NULL THEN
    
        -- Add table owner to the group
        EXECUTE 'GRANT ' || quote_ident(p_group_owner_name) || ' TO ' || quote_ident(l_table_owner);
 
        -- Change ownership of table to group 
        EXECUTE 'ALTER TABLE ' || quote_ident(p_schema_name) || '.' || quote_ident(p_table_name) || ' OWNER TO ' || quote_ident(p_group_owner_name);
    ELSE
        RAISE EXCEPTION 'Table % not found', p_table_name;
    END IF;
END;
$$;

Usage

Call this procedure using an SQL Client.

CALL grant_shared_ownership('public', 'cx_table', 'group_role');