ACL cleanup

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édiatement et 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 may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it.