zdsg
发布于

OushuDB用户权限查询-5.0

CREATE FUNCTION acl_array_to_text(aclarr aclitem[])
    RETURNS text as
$$
    declare
        result text;
    begin
        if (aclarr is null or array_lower(aclarr, 1) = 0) then
            return null;
        end if;

        result := textin(aclitemout(aclarr[1]));

        for i in array_lower(aclarr, 1) + 1 .. array_upper(aclarr, 1)
        loop
            result := result || ',' ||textin(aclitemout(aclarr[i]));
        end loop;
        return result;
    end
    $$ language plpgsql IMMUTABLE;

select relname,nspname,relacl
from pg_class join pg_namespace n on n.oid = relnamespace
where
    relacl is not null and relkind IN ('r', 'v') and
    (position('xxxl' in acl_array_to_text(relacl)) <> 0 or
     position(',=' in acl_array_to_text(relacl)) <> 0 or
     substring(acl_array_to_text(relacl) from 1 for 1)='=');
评论
    test