ACL cleanup - Mailing list pgsql-performance

This might not be 100% performance compilant but i guess its better than -hackers since
these day's there seem to be some big consern :)

So feel free to comment

[Abstract: Underlyin plpgsql should remove all public user ACL's from Function,Table Sequence,View ... ]

-elz

---------------------------------------------
---------------------------------------------

CREATE OR REPLACE FUNCTION cleanup_public_perm_on_function()
  RETURNS int4 AS
'
DECLARE
r_record  record;
v_record  record;
exec_string text;
argument_string text;
i int2;
BEGIN
    FOR r_record IN SELECT * FROM pg_proc WHERE proowner !=''1''  LOOP

 exec_string = '''';
 argument_string = '''';

 exec_string = ''REVOKE ALL ON FUNCTION '' || r_record.proname  || ''('';

 IF (r_record.pronargs > 0) THEN
  i = 0;
  WHILE (i < r_record.pronargs) LOOP
                  IF i > 0 THEN
    argument_string  = argument_string || '','' ;
                  END IF;
    FOR v_record IN SELECT * from pg_type WHERE oid=r_record.proargtypes[i] LOOP
    argument_string = argument_string || v_record.typname ;
    END LOOP;
   i = i+1;
  END LOOP;
        END IF;


 exec_string = exec_string || argument_string || '') FROM public;'';

    IF exec_string != '''' THEN

     RAISE NOTICE ''exec_string is %'', exec_string;
     EXECUTE exec_string;
    END IF;
    END LOOP;
    RETURN 1;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION cleaup_public_on_table_sequence_view()
  RETURNS int4 AS
'
DECLARE
r_record  record;
exec_string text;
BEGIN
    FOR r_record IN SELECT * FROM pg_class WHERE relowner !=''1'' LOOP

 exec_string = '''';
    IF (r_record.relkind::char = ''r''::char) THEN
 exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';
    END IF;
    IF (r_record.relkind::char = ''c''::char) THEN

 exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';

     END IF;
     IF (r_record.relkind::char = ''v''::char) THEN

 exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';

     END IF;

    IF (r_record.relkind::char = ''S''::char) THEN

        exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';
    END IF;

    IF exec_string != '''' THEN

     RAISE NOTICE ''exec_string is %'', exec_string;
 EXECUTE exec_string;
    END IF;
    END LOOP;
    RETURN 1;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;


SELECT * FROM cleanup_public_perm_on_function();
SELECT * FROM cleaup_public_on_table_sequence_view();
DROP FUNCTION cleanup_public_perm_on_function();
DROP FUNCTION cleaup_public_on_table_sequence_view();

AVERTISSEMENT CONCERNANT LA CONFIDENTIALITÉ

Le présent message est à l'usage exclusif du ou des destinataires mentionnés ci-dessus. Son contenu est confidentiel et
peutêtre assujetti au secret professionnel. Si vous avez reçu le présent message par erreur, veuillez nous en aviser
immédiatementet le détruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite. 

CONFIDENTIALITY NOTICE

This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and
maycontain privileged information. If you have received this communication by error, please notify the sender and
deletethe message without copying or disclosing it.