Le portail francophone de la géomatique

select relname, relacl from pg_class where relacl::text like '%pluvinet%' ;
ERROR:  cannot cast type aclitem[] to text
LINE 1: ...elect relname, relacl from pg_class where relacl::text like ...

CREATE SCHEMA acl_admin;

CREATE OR REPLACE FUNCTION acl_admin.grant_on_all(text, text, text)
RETURNS TEXT STRICT VOLATILE AS '
DECLARE
    usr ALIAS FOR $1;
    prv ALIAS FOR $2;
    own ALIAS FOR $3;
    rel record;
    sql text;
BEGIN
    FOR rel IN SELECT pg_catalog.quote_ident(schema_name) AS schema_name,
                      pg_catalog.quote_ident(relation_name) AS relation_name
        FROM acl_admin.schema_and_relation_view 

        where relation_name not in (''geometry_dump'',''plr_environ_type'',''r_typename'') 
         and relation_name  in 
(select  c.table_name
from  (select c.relnamespace, c.relname as table_name, pg_get_userbyid(c.relowner) as table_owner, c.relacl from pg_class as c) as c 
join pg_namespace n 
on n.oid = c.relnamespace and c.table_owner = own)
order by schema_name, relation_name
    LOOP sql := ''GRANT '' || prv || '' ON '' || rel.schema_name || ''.'' || rel.relation_name || '' TO '' || usr;
        RAISE NOTICE ''%'', sql;
        EXECUTE sql;
    END LOOP;
    RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';
COMMENT ON FUNCTION acl_admin.grant_on_all(text,text)
IS 'Given a user name and privilidge, execute GRANT privilidge ON all relations in all schemas TO user.';

CREATE OR REPLACE FUNCTION acl_admin.grant_on_all(text,text)
RETURNS TEXT STRICT VOLATILE AS
'SELECT acl_admin.grant_on_all($1, ''ALL''::text,$2);'
LANGUAGE 'sql';
COMMENT ON FUNCTION acl_admin.grant_on_all(text)
IS 'Given a user name, execute GRANT ALL ON all relations in all schemas TO user.';




 select acl_admin.grant_on_all('utilisateur B','utilisateur A');