Business Database

Customize PostgreSQL Business Database

您可以通过pg_userspg_databases为数据库集群创建新的业务用户与业务数据库。

注意

请尽可能通过声明的方式创建业务用户与业务数据库,而不是在数据库中手工创建。因为业务用户与业务数据库需要同时在数据库与连接池中进行变更。

配置

详情请参考:PG模板参数

#------------------------------------------------------------------------------
# BUSINESS TEMPLATE
#------------------------------------------------------------------------------
# - business - #
# users that are ad hoc to each cluster
pg_users:
  - username: dbuser_test
    password: DBUser.Test
    options: LOGIN NOINHERIT
    comment: business read-write user
    groups: [dbrole_readwrite]

pg_databases: # additional business database
  - name: test                                # one object for each database
    owner: dbuser_test
    schemas: [monitor, public]
    extensions: [{name: "postgis", schema: "public"}]
    parameters:
      search_path: 'yay,public,monitor'       # set default search path

原理

该模块的实现原理是:

这里User与DB的声明式定义将由模板 pg-init-business.sql 渲染成幂等的SQL脚本并执行。

/pg/tmp/pg-init-business.sql

如果您希望执行自定义的数据库初始化动作,可以考虑在pg_database.<database>.baseline中指定自定义SQL脚本的路径。该脚本将用于业务数据库的初始化。

pg-init-business

您可以通过修改pg-init-business的方式完成对数据库的额外定制工作

Pigsty强烈建议通过提供自定义的pg-init脚本完成复杂的定制,如无必要,尽量不要改动pg-init-business中的原有逻辑。

----------------------------------------------------------------------
-- File      :   pg-init-business.sql
-- Ctime     :   2020-12-21
-- Mtime     :   2020-12-21
-- Desc      :   business schema baseline
-- Path      :   /pg/tmp/pg-init-business.sql
-- Author    :   Vonng(fengruohang@outlook.com)
-- Copyright (C) 2018-2021 Ruohang Feng
----------------------------------------------------------------------

--==================================================================--
--                            executions                            --
--==================================================================--
-- psql template1 -AXtwqf /pg/tmp/pg-init-business.sql
-- this sql scripts is responsible for create business roles and databases


--==================================================================--
--                              Users                               --
--==================================================================--
-- default roles
{% for user in pg_users %}
CREATE USER "{{ user.username }}";
{% endfor %}

{% for user in pg_users %}
--------------------------
-- {{ 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 %}


--==================================================================--
--                            Databases                             --
--==================================================================--
{% for database in pg_databases %}
CREATE DATABASE "{{ database.name }}";

-- admin role have create privilege
REVOKE CREATE ON DATABASE "{{ database.name }}" FROM PUBLIC;
GRANT CREATE ON DATABASE "{{ database.name }}" TO "dbrole_admin";

-- if owner is set, revoke public connect privilege
{% if 'owner' in database %}
-- setup owner
ALTER DATABASE "{{ database.name }}" OWNER TO {{ database.owner }};

-- revoke public connect
REVOKE CONNECT ON DATABASE "{{ database.name }}" FROM PUBLIC;

-- replicator, monitor have connect privilege
GRANT CONNECT ON DATABASE "{{ database.name }}" TO "{{ pg_replication_username }}";
GRANT CONNECT ON DATABASE "{{ database.name }}" TO "{{ pg_monitor_username }}";

-- admin and dbowner have connect privilege with grant option
GRANT CONNECT ON DATABASE "{{ database.name }}" TO "{{ pg_admin_username }}" WITH GRANT OPTION;
GRANT CONNECT ON DATABASE "{{ database.name }}" TO "{{ database.owner }}" WITH GRANT OPTION;
{% endif %}

{% endfor %}


{% for database in pg_databases %}
--------------------------
-- database: {{ database.name }}
--------------------------
-- connect to database {{ database.name }}
\c {{ database.name }}

-- create schemas
{% if 'schemas' in database %}{% for schema_name in database.schemas %}
CREATE SCHEMA IF NOT EXISTS "{{ schema_name }}";
{% endfor %}{% endif %}

-- revoke public schema creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO "dbrole_admin"; -- admin can create objects

-- create extensions
{% if 'extensions' in database %}{% for extension in database.extensions %}
CREATE EXTENSION IF NOT EXISTS "{{ extension.name }}"{% if 'schema' in extension %}WITH SCHEMA "{{ extension.schema }}"{% endif %};
{% endfor %}{% endif %}

-- alter databaes parameters
{% if 'parameters' in database %}{% for key, value in database.parameters.items() %}
ALTER DATABASE "{{ database.name }}" SET {{ key }} = {{ value }};
{% endfor %}{% endif %}


{% endfor %}


Last modified 2021-02-08: update en deploy doc (c50b13b)