定制模板数据库

pg_init
pg_replication_username
pg_replication_password
pg_monitor_username
pg_monitor_password
pg_admin_username
pg_admin_password
pg_default_roles
pg_default_privilegs
pg_default_schemas
pg_default_extensions
pg_hba_rules
pg_hba_rules_extra
pgbouncer_hba_rules
pgbouncer_hba_rules_extra

#!/usr/bin/env bash
set -uo pipefail

#==================================================================#
#                          System Template                         #
#==================================================================#
# system default template
psql template1 -qAXwtf /pg/tmp/pg-init-template.sql

# make postgres same as templated database (optional)
psql postgres  -qAXwtf /pg/tmp/pg-init-template.sql

#==================================================================#
#                          Customize Logic                         #
#==================================================================#
# add your template logic here

----------------------------------------------------------------------
-- File      :   pg-init-template.sql
-- Ctime     :   2018-10-30
-- Mtime     :   2020-12-17
-- Desc      :   init postgres cluster template schema
-- Path      :   /pg/tmp/pg-init-template.sql
-- Author    :   Vonng(fengruohang@outlook.com)
-- Copyright (C) 2018-2021 Ruohang Feng
----------------------------------------------------------------------

--==================================================================--
--                            executions                            --
--==================================================================--
-- psql template1 -AXtwqf /pg/tmp/pg-init-template.sql
-- this sql scripts is responsible for post-init procedure
-- it will
--    * create system users such as replicator, monitor user, admin user
--    * create system default role system
--    * create schema, extensions in template1 database
--    * create monitor views in template1 database

--==================================================================--
--                              Roles                               --
--==================================================================--
-- default roles
{% for user in pg_default_roles %}
CREATE ROLE "{{ user.username }}";
{% endfor %}

-- system users
CREATE USER "{{ pg_replication_username }}";
CREATE USER "{{ pg_monitor_username }}";
CREATE USER "{{ pg_admin_username }}";


{% for user in pg_default_roles %}
--------------------------
-- {{ user.username }}
--------------------------
{% if 'password' in user %}
{% if user.password == '' %}ALTER ROLE "{{ user.username }}" PASSWORD NULL;
{% else %}ALTER ROLE "{{ user.username }}" PASSWORD '{{ user.password }}';{% endif %}
{% endif %}
{% if 'options' in user %}ALTER ROLE "{{ user.username }}" {{ user.options }};{% endif %}

{% if 'comment' in user %}COMMENT ON ROLE "{{ user.username }}" IS '{{ user.comment }}';{% endif %}

{% if 'groups' in user %}
{% for group in user.groups %}
GRANT "{{ group }}" TO "{{ user.username }}";
{% endfor %}
{% endif %}

{% endfor %}

-- enforce password overwrite for system users
ALTER ROLE "{{ pg_replication_username }}" PASSWORD '{{ pg_replication_password }}';
ALTER ROLE "{{ pg_monitor_username }}" PASSWORD '{{ pg_monitor_password }}';
ALTER ROLE "{{ pg_admin_username }}" PASSWORD '{{ pg_admin_password }}';

--==================================================================--
--                          Default Privileges                      --
--==================================================================--
{% for priv in pg_default_privilegs %}
ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_dbsu }} {{ priv }};
{% endfor %}

{% for priv in pg_default_privilegs %}
ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_admin_username }} {{ priv }};
{% endfor %}

-- for business admin, they can set role to dbrole_admin
{% for priv in pg_default_privilegs %}
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" {{ priv }};
{% endfor %}

--==================================================================--
--                              Schemas                             --
--==================================================================--
{% for schema_name in pg_default_schemas %}
CREATE SCHEMA IF NOT EXISTS "{{ schema_name }}";
{% endfor %}

-- revoke public creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--==================================================================--
--                             Extensions                           --
--==================================================================--
{% for extension in pg_default_extensions %}
CREATE EXTENSION IF NOT EXISTS "{{ extension.name }}"{% if 'schema' in extension %}WITH SCHEMA "{{ extension.schema }}"{% endif %};
{% endfor %}


--==================================================================--
--                            Monitor Views                         --
--==================================================================--
-- ....