87 lines
2.1 KiB
Bash
Executable file
87 lines
2.1 KiB
Bash
Executable file
#!/bin/sh
|
|
|
|
|
|
generate_dump() {
|
|
test -n "$1" || exit 1
|
|
cat <<EOF
|
|
--
|
|
-- PostgreSQL database cluster dump
|
|
--
|
|
|
|
\connect postgres
|
|
|
|
|
|
--
|
|
-- Database creation
|
|
--
|
|
|
|
CREATE DATABASE $1 WITH TEMPLATE = template0 OWNER = $1_admin;
|
|
REVOKE ALL ON DATABASE $1 FROM PUBLIC;
|
|
REVOKE ALL ON DATABASE $1 FROM $1_admin;
|
|
GRANT ALL ON DATABASE $1 TO $1_admin;
|
|
GRANT CONNECT ON DATABASE $1 TO $1;
|
|
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
|
|
REVOKE ALL ON DATABASE template1 FROM postgres;
|
|
GRANT ALL ON DATABASE template1 TO postgres;
|
|
|
|
|
|
\connect $1
|
|
|
|
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
SET statement_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SET check_function_bodies = false;
|
|
SET client_min_messages = warning;
|
|
|
|
--
|
|
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
|
|
--
|
|
|
|
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
|
|
|
|
|
|
--
|
|
-- Name: public; Type: ACL; Schema: -; Owner: postgres
|
|
--
|
|
|
|
REVOKE ALL ON SCHEMA public FROM PUBLIC;
|
|
REVOKE ALL ON SCHEMA public FROM postgres;
|
|
GRANT ALL ON SCHEMA public TO postgres;
|
|
GRANT ALL ON SCHEMA public TO $1_admin;
|
|
GRANT USAGE ON SCHEMA public TO $1;
|
|
|
|
|
|
SET search_path = public, pg_catalog;
|
|
|
|
--
|
|
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: public; Owner: $1_admin
|
|
--
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE $1_admin IN SCHEMA public REVOKE ALL ON SEQUENCES FROM PUBLIC;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE $1_admin IN SCHEMA public REVOKE ALL ON SEQUENCES FROM $1_admin;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE $1_admin IN SCHEMA public GRANT USAGE,UPDATE ON SEQUENCES TO $1;
|
|
|
|
|
|
--
|
|
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: public; Owner: $1_admin
|
|
--
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE $1_admin IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE $1_admin IN SCHEMA public REVOKE ALL ON TABLES FROM $1_admin;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE $1_admin IN SCHEMA public GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO $1;
|
|
EOF
|
|
}
|
|
|
|
generate_dump "$1"
|