Postgres' SET ROLE usage example

create group G1;
create group G2;

create user U1 password 'u1';

alter group G1 add user U1;
alter group G2 add user U1;

create table T1( a int );
create table T2( a int );

grant select on T1 to G1;
grant select on T2 to G2;

\c - u1

select * from t1;
select * from t2;

set role g1;

/* Should throw error, since we have explicitly taken on the garb of G1, hence giving up permissions of group G2 */

select * from t2;

Postgres query to find if foreign-key columns match the data type of the referenced table

create or replace function find_array_element( el anyelement, arr anyarray  ) returns integer as $$
declare
    i int;
begin
    for i in 1..array_upper( arr, 1 ) loop
        if( el = arr[i] ) then
            return i;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;

select
    (select relname
        from pg_class
        where oid = conrelid) as table,
    (select relname
        from pg_class
        where oid = confrelid ) as parent,
    conname as foriegn_key,
    array(select attname
            from pg_attribute
            where attrelid = C.conrelid
                and attnum = ANY( C.conkey )
            order by find_array_element( attnum, C.conkey ) ) as table_columns,
    array(select (select typname
                    from pg_type
                    where oid = A.atttypid)
            from pg_attribute as A
            where attrelid = C.conrelid
                and attnum = ANY( C.conkey )
            order by find_array_element( attnum, C.conkey ) ) as table_datatypes,
    array(select attname
            from pg_attribute
            where attrelid = C.confrelid
                and attnum = ANY( C.confkey )
            order by find_array_element( attnum, C.confkey ) ) as parent_columns,
    array(select (select typname
                    from pg_type
                    where oid = A.atttypid)
            from pg_attribute as A
            where attrelid = C.confrelid
                and attnum = ANY( C.confkey )
            order by find_array_element( attnum, C.confkey ) ) as parent_datatypes
from pg_constraint as C
where contype = 'f'
and array(select (atttypid, attlen, atttypmod)::text
            from pg_attribute
            where attrelid = C.conrelid
                and attnum = any ( C.conkey )
            order by find_array_element( attnum, C.conkey ) )
    <> array(select (atttypid, attlen, atttypmod)::text
                from pg_attribute
                where attrelid = C.confrelid
                    and attnum = any ( C.confkey )
                    order by find_array_element( attnum, C.confkey ) );