教學目標

初步了解如何解決 Windows 排程工作無法直接定期執行 PostgreSQL 之 SQL 指令操作的問題。

重點概念

首先雖然在 Windows 伺服器平台中我們能夠透過 psql 工具透過命令提示列的指令方式登入 PostgreSQL 資料庫執行 SQL 指令操作,但是需要手動輸入 PostgreSQL 帳號的密碼,此時將會無法設定 Windows 排程工作定期執行 PostgreSQL 的 SQL 指令操作。此外雖然能夠設定「PGPASSWORD」環境變數為 PostgreSQL 使用者預設密碼,但此密碼將會是明碼顯示,所以將會被企業或組織中資安單位所挑戰此方式非常不安全。

接著我們該如何先解決 PGPASSWORD 環境變數以明碼設定密碼的問題呢?最簡單的方式就是撰寫加解密的 程式解決此問題,其中程式主要有兩個執行步驟如下:

  1. 企業中的系統安全人員透過程式建立 pgpass 加密密碼檔案。
  2. 企業中的系統管理人員透過程式以 pgpass 加密密碼檔案直接執行 psql 工具。

再來我們開始需要撰寫程式,此篇主要以 Java 程式語言撰寫範例程式為主,但是為了避免 Oracle JAVA 授權的問題,所以建議透過 OpenJDK 進行程式開發,其中需要特別注意的是 CRYPT_KEY 加密金鑰需要為 16 個字元,否則加密結果會為 NULL,以及需要建立「psql.bat」批次檔主要執行 psql 工具。

撰寫 JAVA 範例程式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
import javax.crypto.Cipher;  
import javax.crypto.spec.SecretKeySpec;
import java.io.*;
public class PGSQL {
private static final String AES = "AES";
private static final String CRYPT_KEY = "LEOYEH1234567890";
public static byte[] encrypt(byte[] src, String key) throws Exception {
Cipher cipher = Cipher.getInstance(AES);
SecretKeySpec securekey = new SecretKeySpec(key.getBytes(), AES);
cipher.init(Cipher.ENCRYPT_MODE, securekey);
return cipher.doFinal(src);
}
public static byte[] decrypt(byte[] src, String key) throws Exception {
Cipher cipher = Cipher.getInstance(AES);
SecretKeySpec securekey = new SecretKeySpec(key.getBytes(), AES);
cipher.init(Cipher.DECRYPT_MODE, securekey);
return cipher.doFinal(src);
}
public static String byte2hex(byte[] b) {
String hs = "";
String stmp = "";
for (int n = 0; n < b.length; n++) {
stmp = (java.lang.Integer.toHexString(b[n] & 0XFF));
if (stmp.length() == 1)
hs = hs + "0" + stmp;
else
hs = hs + stmp;
}
return hs.toUpperCase();
}
public static byte[] hex2byte(byte[] b) {
if ((b.length % 2) != 0)
throw new IllegalArgumentException("");
byte[] b2 = new byte[b.length / 2];
for (int n = 0; n < b.length; n += 2) {
String item = new String(b, n, 2);
b2[n / 2] = (byte) Integer.parseInt(item, 16);
}
return b2;
}
public final static String decrypt(String data) {
try {
return new String(decrypt(hex2byte(data.getBytes()),CRYPT_KEY));
} catch (Exception e) {
System.out.println(e.toString());
}
return null;
}
public final static String encrypt(String data) {
try {
return byte2hex(encrypt(data.getBytes(), CRYPT_KEY));
} catch (Exception e) {
System.out.println(e.toString());
}
return null;
}
public static void main(String[] args) {
try {
if (args[0].equals("-c")) {
String pw = args[1];
System.out.println("PostgreSQL Default Password:" + pw);
String pwEncrypt = encrypt(pw);
System.out.println("PostgreSQL Encrypt Password:" + pwEncrypt);
OutputStream fos = new FileOutputStream("pgpass");
fos.write(pwEncrypt.getBytes());
fos.close();
} else if (args[0].equals("-r")) {
File keyfile = new File("pgpass");
FileInputStream fis = new FileInputStream("pgpass");
int length = (int)keyfile.length();
byte[] cipherText = new byte [length];
fis.read(cipherText);
fis.close();
String pwDecrypt = decrypt(new String(cipherText, "UTF-8"));
ProcessBuilder builder = new ProcessBuilder("cmd.exe", "/k", "SET PGPASSWORD=" + pwDecrypt + "& call psql.bat");
builder.redirectErrorStream(true);
Process p = builder.start();
BufferedReader r = new BufferedReader(new InputStreamReader(p.getInputStream()));
String line;
while (true) {
line = r.readLine();
if (line == null) { break; }
System.out.println(line);
}
}
} catch (Exception e) {
System.out.println(e.toString());
}
}
}

最後當我們根據企業或組織的需求撰寫完成 Java 範例程式,就能夠以 pgpass 加密密碼檔案直接執行 psql 工具,並且就能夠設定 Windows 排程工作定期執行 PostgreSQL 的 SQL 指令操作。此時就能夠不以明碼的方式設定 PostgreSQL 預設密碼至環境變數中,以利解決 Windows 排程工作無法直接定期執行 PostgreSQL 之 SQL 指令操作的問題。至於 pgpass 加密密碼檔案要如何進行保護呢?理應能夠透過企業或組織中 Windows 伺服器的檔案權限控管即可。

編譯 Java 範例程式

1
> javac PGSQL.java

透過程式建立 pgpass 加密密碼檔案

1
> java PGSQL -c [PostgreSQL 預設密碼]

透過程式以 pgpass 加密密碼檔案直接執行 psql 工具

1
> java PGSQL -r

總結 PostgreSQL 資料庫若需要定期進行資料清理時,將會面臨 Windows 排程工作無法直接定期執行 PostgreSQL 之 SQL 指令操作的問題,此時我們就能夠透過 JAVA 範例程式解決此問題。

相關資源