PGSQL业务用户与数据库

- name: dbuser_meta               # required, `name` is the only mandatory field of a user definition
  password: md5d3d10d8cad606308bdb180148bf663e1  # md5 salted password of 'DBUser.Meta'
  # optional, plain text and md5 password are both acceptable (prefixed with `md5`)
  login: true                     # optional, can login, true by default  (new biz ROLE should be false)
  superuser: false                # optional, is superuser? false by default
  createdb: false                 # optional, can create database? false by default
  createrole: false               # optional, can create role? false by default
  inherit: true                   # optional, can this role use inherited privileges? true by default
  replication: false              # optional, can this role do replication? false by default
  bypassrls: false                # optional, can this role bypass row level security? false by default
  pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  connlimit: -1                   # optional, user connection limit, default -1 disable limit
  expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  comment: pigsty admin user      # optional, comment string for this user/role
  roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  # search_path: public         # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path)

bin/createuser <pg_cluster> <username>    # <pg_cluster> 为集群名称，<user.name> 是新用户名。必须先定义，再执行脚本进行创建
bin/createuser pg-meta dbuser_meta        # 例：在pg-meta集群中创建dbuser_meta用户
./pgsql-createuser.yml -l <pg_cluster> -e pg_user=<user.name>  # 该脚本实际上调用了以下Ansible剧本完成对应任务

# 紧急情况下可以使用该命令手工添加用户，用法：pgbouncer-create-user <username> [password]
/pg/bin/pgbouncer-create-user

pgbouncer-create-user dbp_vonng Test.Password # 明文密码
pgbouncer-create-user dbp_vonng md596bceae83ba2937778af09adf00ae738 # md5密码
pgbouncer-create-user dbp_vonng auto          # 从数据库查询获取密码
pgbouncer-create-user dbp_vonng null          # 使用空密码

- name: meta                      # required, `name` is the only mandatory field of a database definition
  baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
  owner: postgres                 # optional, database owner, postgres by default
  template: template1             # optional, which template to use, template1 by default
  encoding: UTF8                  # optional, database encoding, UTF8 by default. (MUST same as template database)
  locale: C                       # optional, database locale, C by default.  (MUST same as template database)
  lc_collate: C                   # optional, database collate, C by default. (MUST same as template database)
  lc_ctype: C                     # optional, database ctype, C by default.   (MUST same as template database)
  tablespace: pg_default          # optional, default tablespace, 'pg_default' by default.
  allowconn: true                 # optional, allow connection, true by default. false will disable connect at all
  revokeconn: false               # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
  comment: pigsty meta database   # optional, comment string for this database
  connlimit: -1                   # optional, database connection limit, default -1 disable limit
  schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
  extensions:                     # optional, additional extensions to be installed: array of schema definition `{name,schema}`
    - {name: adminpack, schema: pg_catalog}    # install adminpack to pg_catalog and install postgis to public
    - {name: postgis, schema: public}          # if schema is omitted, extension will be installed according to search_path.

bin/createdb <pg_cluster>  <database.name> # <pg_cluster> 为集群名称，<database.name> 是新数据库的name。
bin/createdb pg-meta meta                  # 例：在pg-meta集群中创建meta数据库
./pgsql-createdb.yml -l <pg_cluster> -e pg_database=<dbname>  # 该脚本实际上调用了以下Ansible剧本完成对应任务

# 数据库名 = 实际目标连接信息
meta = host=/var/run/postgresql
grafana = host=/var/run/postgresql
prometheus = host=/var/run/postgresql

# 特殊情况下可以使用该命令手工添加数据库
# pgbouncer-create-user <dbname> [connstr] [dblist=/etc/pgbouncer/database.txt]
/pg/bin/pgbouncer-create-db
pgbouncer-create-db meta                     # 创建meta数据库，指向本机同名数据库
pgbouncer-create-db test host=10.10.10.13    # 创建test数据库并将其指向10.10.10.13上的同名数据库