Postgres: To grant access to all partitions in the table

 DO $$

DECLARE
    v_schema text := 'cpnadmin';          -- << change schema name here
    v_table  text := 'coupons_active';    -- << change table name here
    v_user   text := 'cpnuser';           -- << change target user here
    r record;
BEGIN
    RAISE NOTICE 'Granting privileges for all partitions of %.%', v_schema, v_table;

    -- Loop through all child partitions of the given parent table

    FOR r IN
        SELECT child.relname AS partition_name
        FROM pg_inherits i
        JOIN pg_class parent ON i.inhparent = parent.oid
        JOIN pg_class child  ON i.inhrelid = child.oid
        JOIN pg_namespace n  ON n.oid = parent.relnamespace
        WHERE n.nspname = v_schema
          AND parent.relname = v_table
    LOOP
        RAISE NOTICE 'Granting privileges on %.% to %', v_schema, r.partition_name, v_user;

       EXECUTE format(

            'GRANT SELECT, INSERT, UPDATE, DELETE ON %I.%I TO %I;',
            v_schema, r.partition_name, v_user
        );
    END LOOP;

    -- Also grant on parent table

    EXECUTE format(
        'GRANT SELECT, INSERT, UPDATE, DELETE ON %I.%I TO %I;',
        v_schema, v_table, v_user
    );

    RAISE NOTICE 'All grants applied successfully for %.%', v_schema, v_table;

END $$;

 

------

same thing but shorter version:

DO $$

DECLARE

    r record;

BEGIN

    FOR r IN

        SELECT

            n.nspname AS schema_name,

            c.relname AS partition_name

        FROM pg_inherits i

        JOIN pg_class c ON i.inhrelid = c.oid

        JOIN pg_namespace n ON c.relnamespace = n.oid

        JOIN pg_class p ON i.inhparent = p.oid

        WHERE p.relname = 'coupons'

          AND n.nspname = 'cpnadmin'

    LOOP

        EXECUTE format(

            'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %I.%I TO cpnuser;',

            r.schema_name, r.partition_name

        );

    END LOOP;

END $$;