作者
digoal
日期
2020-12-29
标签
PostgreSQL , TDE
背景
《PostgreSQL 14 preview - 支持TDE功能, 支持加密数据文件, 支持加密WAL日志文件》
二级密钥来自一个自定义的脚本cluster_key_command, 为了向脚本传输一些动态参数, PG采用通配符来支持. 和归档命令、restore command类似, 使用通配符变量代替WAL目录, WAL文件名等.
%R, %d, %p . 具体参考如下解释:
7824 <variablelist>
7825 <varlistentry id="guc-cluster-key-command" xreflabel="cluster_key_command">
7826 <term><varname>cluster_key_command</varname> (<type>string</type>)
7827 <indexterm>
7828 <primary><varname>cluster_key_command</varname> configuration parameter</primary>
7829 </indexterm>
7830 </term>
7831 <listitem>
7832 <para>
7833 This option specifies an external command to obtain the cluster-level
7834 key for cluster file encryption during server initialization and
7835 server start.
7836 </para>
7837 <para>
7838 The command must print the cluster key to the standard output as
7839 64 hexadecimal characters, and exit with code 0. The command
7840 can prompt for the passphrase or PIN from the terminal if
7841 <option>--authprompt</option> is used. In the parameter value,
7842 <literal>%R</literal> represents the file descriptor number opened
7843 to the terminal that started the server. A file descriptor is only
7844 available if enabled at server start. If <literal>%R</literal>
7845 is used and no file descriptor is available, the server will not
7846 start. Value <literal>%p</literal> is replaced by a pre-defined
7847 prompt string. Value <literal>%d</literal> is replaced by the
7848 directory containing the keys; this is useful if the command
7849 must create files with the keys, e.g., to store a cluster-level
7850 key encryped by a key stored in a hardware security module.
7851 (Write <literal>%%</literal> for a literal <literal>%</literal>.)
7852 Note that the prompt string will probably contain whitespace,
7853 so be sure to quote its use adequately. Newlines are stripped
7854 from the end of the output if present.
7855 </para>
7856 <para>
7857 This parameter can only be set by
7858 <application>initdb</application>, in the
7859 <filename>postgresql.conf</filename> file, or on the server
7860 command line.
7861 </para>
7862 </listitem>
7863 </varlistentry>
7864 </variablelist>
7865 </sect1>
initdb新增接收二级密钥的参数:
```
-
-
-
-
- This option specifies an external command to obtain the cluster-level
- key for cluster file encryption during server initialization and
- server start; see
-
-
-
- <varlistentry id="app-initdb-file-encryption-keylen"
- xreflabel="file encryption">
- Specifies the number of bits for the file encryption keys. The
- default is 128 bits.
-
-
- Allows the command
- to prompt for a passphrase or PIN.
-
-
- Copies cluster file encryption keys from another cluster; required
- when using
pg_upgrade on a cluster - with cluster file encryption enabled.
```
- printf(_(" -c --cluster-key-command=COMMAND\n"
- " enable cluster file encryption and set command\n"
- " to obtain the cluster key\n"));
- printf(_(" -K, --file-encryption-keylen=LENGTH\n"
- " bit length of the file encryption key\n"));
- printf(_(" -R, --authprompt prompt for a passphrase or PIN\n"));
- printf(_(" -u, --copy-encryption-keys=DATADIR\n"
- " copy the file encryption key from another cluster\n"));
例子
1、创建 “获取二级密钥脚本” 目录
mkdir ~/.ckey
chmod 700 ~/.ckey
2、使用用户终端输入脚本, 可以在sample中直接获取
vi ~/.ckey/ckey_direct.sh
ckey_direct.sh.sample
```
!/bin/sh
This uses a key supplied by the user
If OpenSSL is installed, you can generate a pseudo-random key by running:
openssl rand -hex 32
To get a true random key, run:
wget -q -O - 'https://www.random.org/cgi-bin/randbyte?nbytes=32&format=h' | tr -d ' \n'; echo
[ "$#" -lt 1 ] && echo "cluster_key_command usage: $0 %R [%p]" 1>&2 && exit 1
Supports environment variable PROMPT
FD="$1"
[ ! -t "$FD" ] && echo "file descriptor $FD does not refer to a terminal" 1>&2 && exit 1
[ "$2" ] && PROMPT="$2"
----------------------------------------------------------------------
[ ! "$PROMPT" ] && PROMPT='Enter cluster key as 64 hexadecimal characters: '
stty -echo <&"$FD"
echo 1>&"$FD"
echo -n "$PROMPT" 1>&"$FD"
read KEY <&"$FD"
stty echo <&"$FD"
if [ "$(expr "$KEY" : '[0-9a-fA-F]*$')" -ne 64 ]
then echo 'invalid; must be 64 hexadecimal characters' 1>&2
exit 1
fi
echo "$KEY"
exit 0
```
3、修改脚本权限
chmod 500 ~/.ckey/ckey_direct.sh
4、二级密钥可以使用openssl生成, 或者从random.org获取.
```
[root@iZbp1f0rp991nzzm4cec8sZ ~]# openssl rand -hex 32
47e9de4894ae93a153ac6c55fca027beac80377bfc4722a1a468792ea048fb23
[root@iZbp1f0rp991nzzm4cec8sZ ~]# openssl rand -hex 32
688216e1ed861b33fbe549ea30a59f14a711ff83865e62364ad0306e1f68f1ea
[root@iZbp1f0rp991nzzm4cec8sZ ~]# openssl rand -hex 32
60ab58211a0fec8a20df76a68013de4adc1d6723f6a9e0c0261a6b519f71ad22
[root@iZbp1f0rp991nzzm4cec8sZ ~]# openssl rand -hex 32
25973511b9f3698538b6c4b93cbfade16a43ee7c01362ec12bdbf6fdf4efe98c
[root@iZbp1f0rp991nzzm4cec8sZ ~]# openssl rand -hex 32
66a555c1c297eb9117a3eb49adaa204000914c31f6e7353062fe6c29c485054e
[root@iZbp1f0rp991nzzm4cec8sZ ~]# wget -q -O - 'https://www.random.org/cgi-bin/randbyte?nbytes=32&format=h' | tr -d ' \n'; echo
4b00b8df6842e9046243e92e9dbc1b19e4888043e30c6801569492ccf58486af
[root@iZbp1f0rp991nzzm4cec8sZ ~]# wget -q -O - 'https://www.random.org/cgi-bin/randbyte?nbytes=32&format=h' | tr -d ' \n'; echo
a0f48cd7c10c7c70d21454e9065acae2fcdd1bfedb53baa675f384b8a61f4808
```
5、初始化数据库集群, 二级密钥人为输入, 一定要记牢, 否则未来这个集群就启动不起来了.
initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.UTF8 -k -c="~/.ckey/ckey_direct.sh %R" -K 256 -R
6、修改配置文件, 设置获取二级密钥的脚本, 数据库启动时提示用户输入二级密钥. 如果未来想将二级密钥存储到远程密钥管理服务器, 可以修改这个配置.
```
vi $PGDATA/postgresql.conf
cluster_key_command='/home/digoal/.ckey/ckey_direct.sh %R' # 是$PGDATA 相对目录还是绝对目录, 建议上线后试一试
```
7、启动集群
pg_ctl start -R
8、按提示输入二级密钥, 完成启动.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
