Simple procedure for setting defaults on new schemas

gistfile1.txt

/*
 * Assumes that applications connect as 'my_project__blah' and that you've done GRANT my_project__app to my_project__blah.
 * Also assumes that select_roles is a list of groups, like "my_project__dev", "my_project__read_only", etc.
 */
CREATE FUNCTION ddl_tools.schema__defaults(
    schema_name name
    , select_roles text
) RETURNS void LANGUAGE plpgsql AS $body$
DECLARE
    select_template CONSTANT text := $template$
GRANT USAGE ON SCHEMA %1$I TO %2$s;
ALTER DEFAULT PRIVILEGES IN SCHEMA %1$I GRANT SELECT ON TABLES TO %2$s;
$template$;
    template CONSTANT text := $template$
ALTER DEFAULT PRIVILEGES IN SCHEMA %1$I GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO my_project__app;
ALTER DEFAULT PRIVILEGES IN SCHEMA %1$I GRANT USAGE ON SEQUENCES TO my_project__app;
$template$;
BEGIN
    PERFORM tools.exec( format( select_template, schema_name, select_roles ) );
    PERFORM tools.exec( format( template, schema_name) );
END
$body$;