ApsaraDB RDS:権限付与の管理

package org.example;

import com.alibaba.encdb.EncdbSDK;
import com.alibaba.encdb.crypto.EncdbSDKBuilder;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        Main main = new Main();
        main.testSelfBcl();
    }

    private String readPemFile(String filename) throws IOException {
        return new String(Files.readAllBytes(Paths.get(filename)));
    }

    public void testSelfBcl() {
        // Replace the parameter values with the actual information about the RDS instance.
        String hostname = "pgm-****.pg.rds.aliyuncs.com";
        String port = "5432";
        String dbname = "testdb";
        String username = "testdbuser";
        String password = "****";
        String mek = "00112233445566778899aabbccddeeff";  // This is a sample value. We recommend that you use a more complex master encryption key (MEK).
        String prikeyFilename = "D:\\test\\Issuer\\pri_key_pkcs8.pem"; // This is a sample value. You must replace the value with the path of the key file.
        String pubkeyFilename = "D:\\test\\Issuer\\pub_key.pem"; // This is a sample value. You must replace the value with the path of the key file.
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://" + hostname + ":" + port + "/" + dbname + "?binaryTransfer=true", username, password)) {
            System.out.println("connect success");
            String tblname = "tbl_alter_" + conn.getMetaData().getUserName();
            // Create a table.
            conn.createStatement().executeUpdate("DROP TABLE IF EXISTS " + tblname);
            conn.createStatement().executeUpdate("CREATE TABLE " + tblname + " (id int)");
            // Write plaintext data into the table.
            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO " + tblname + " VALUES(?)")) {
                stmt.setInt(1, 231);
                stmt.executeUpdate();
            }
            // Optional. Clear historical BCL records.
            conn.createStatement().executeUpdate("DELETE FROM encdb.encdb_internal_bcl_table");
            conn.createStatement().executeUpdate("DELETE FROM encdb.encdb_internal_brl_map_table");
            conn.createStatement().executeUpdate("DELETE FROM encdb.encdb_internal_brl_table");
            // Initialize the SDK and distribute the MEK.
            EncdbSDK sdk = EncdbSDKBuilder.newInstance()
                    .setDbConnection(conn)
                    .setMek(mek)
                    .build();
            System.out.println("init success");
            // Read the public and private key content from the key files.
            String userPrkPemString = readPemFile(prikeyFilename);
            String userPukPemString = readPemFile(pubkeyFilename);
            // Register the public and private keys.
            sdk.getKeyManager().registerCertificate(userPrkPemString, userPukPemString);
            System.out.println("register certificate success");

            // Use the specified DEK to convert the plaintext column into a ciphertext column. For example, you can use encdb.dek_generate to generate a DEK.
            // The default DEK is used. You can use encdb.keyname_generate to generate the required keyname.
            String keyname = "'|" + username + "|" + dbname + "|'";
            String alterStmString = "ALTER TABLE " + tblname + " ALTER COLUMN id SET DATA TYPE enc_int4 USING encdb.enc_int4_encrypt(id, " + keyname + ")";

            // The plaintext change fails before the authorization.
            try {
                conn.createStatement().executeUpdate(alterStmString);
            } catch (SQLException exception) {
                if (exception.getMessage().contains("fa030000") || exception.getMessage().contains("fa020000")) {
                    System.out.println("alter column to enc_int4 failed without authorized");
                } else {
                    throw exception; // Re-throw other exceptions.
                }
            }
            // Perform multi-user authorization.
            String bclBodyJsonString = """
                    {
                        "version": 1,
                        "serial_num": "fdbed057-7fe5-4c31-97ae-afdd615732fe",
                        "issuer_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "subject_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "validity": {
                            "not_after": "20250920111111+0800",
                            "not_before": "20240920111111+0800"
                        },
                        "policies": {
                            "issuer_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "93165651-609f-47db-91aa-c3a2ab7084c4"
                                }
                            ],
                            "result_dek": "SUBJECT",
                            "subject_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "93165651-609f-47db-91aa-c3a2ab7084c4"
                                }
                            ],
                            "operation": [
                                "*"
                            ],
                            "postproc": "NULL",
                            "preproc": "NULL"
                        }
                    }
                    """;
            // Issue the authorization.
            {
                bclBodyJsonString = sdk.getKeyManager().issueBCL(bclBodyJsonString, userPukPemString, userPrkPemString, true);
                bclBodyJsonString = sdk.getKeyManager().issueBCL(bclBodyJsonString, userPukPemString, userPrkPemString, false);
                System.out.println("issue bcl success");
            }
            // The plaintext change succeeds after the authorization.
            conn.createStatement().executeUpdate(alterStmString);
            System.out.println("alter column to enc_int4 success after authorized");
        } catch (SQLException |
                 IOException e) {
            e.printStackTrace(); // Print exceptions.
        }
    }
}

package org.example;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;

import com.alibaba.encdb.Cryptor;
import com.alibaba.encdb.EncdbSDK;
import com.alibaba.encdb.common.Constants.EncAlgo;
import com.alibaba.encdb.crypto.EncdbSDKBuilder;

public class BlcTest {
    private String readPemFile(String filename) throws IOException {
        return new String(Files.readAllBytes(Paths.get(filename)));
    }

    private void prepareData(Connection conn, EncdbSDK sdk) throws SQLException {
        int id = 1;
        String name = "name";
        int price = 1234;
        float miles = 12.34f;
        String secret = "aliyun";

        String tblname = "tbl_" + conn.getMetaData().getUserName();
        // Create a table.
        conn.createStatement().executeUpdate("DROP TABLE IF EXISTS " + tblname);
        conn.createStatement().executeUpdate("CREATE TABLE " + tblname
                + " (id INTEGER, name VARCHAR, price enc_int4, miles enc_float4, secret enc_text, PRIMARY KEY (id))");

        // Write data.
        PreparedStatement stmt = conn.prepareStatement(
                "INSERT INTO " + tblname + " (id, name, price, miles, secret) VALUES(?,?,?,?,?)");
        stmt.setInt(1, id);
        stmt.setString(2, name);
        // Encrypt and write data.
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(3, cryptor.encrypt(tblname, "price", price));
        stmt.setBytes(4, cryptor.encrypt(tblname, "miles", miles));
        stmt.setBytes(5, cryptor.encrypt(tblname, "secret", secret));
        stmt.execute();
    }

    private void validateResult(ResultSet rs, EncdbSDK sdk) throws SQLException {
        int id = 1;
        String name = "name";
        int price = 1234;
        float miles = 12.34f;
        String secret = "aliyun";

        Cryptor cryptor = sdk.getCryptor();
        while (rs.next()) {
            int idRs = rs.getInt(1);
            Assertions.assertEquals(id, idRs);
            String nameRs = rs.getString(2);
            Assertions.assertEquals(name, nameRs);
            int priceRs = cryptor.decryptInt(rs.getBytes(3));
            Assertions.assertEquals(price, priceRs);
            float milesRs = cryptor.decryptFloat(rs.getBytes(4));
            Assertions.assertEquals(miles, milesRs, 0.000001f);
            String secretRs = cryptor.decryptString(rs.getBytes(5));
            Assertions.assertEquals(secret, secretRs);
        }
    }

    private void simpleQuery(Connection conn, EncdbSDK sdk) throws SQLException {
        String tblname = "tbl_" + conn.getMetaData().getUserName();
        String sqlCmd = "SELECT * FROM " + tblname + " WHERE  price > ?";
        PreparedStatement stmt = conn.prepareStatement(sqlCmd);
        // Encrypt the query content.
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(1, cryptor.encrypt(tblname, "price", 100));
        ResultSet rs = stmt.executeQuery();

        validateResult(rs, sdk);
    }

    private void subjectJoinQuery(Connection subjectConn, EncdbSDK subjectSdk, Connection issuerConn)
            throws SQLException {
        String issuerTblname = "tbl_" + issuerConn.getMetaData().getUserName();
        String subjectTblname = "tbl_" + subjectConn.getMetaData().getUserName();

        // Initiate a JOIN query as the subject to query data in the table of the issuer.
        String sqlCmd = "SELECT subject.id as id, subject.name as name, subject.price as price, subject.miles as miles, subject.secret as secret "
                + "FROM " + issuerTblname + " issuer, " + subjectTblname + " subject "
                + "WHERE issuer.price = subject.price and subject.price > ?";

        Connection conn = subjectConn;
        EncdbSDK sdk = subjectSdk;
        PreparedStatement stmt = conn.prepareStatement(sqlCmd);
        // Encrypt the query content.
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(1, cryptor.encrypt(subjectTblname, "price", 100));
        ResultSet rs = stmt.executeQuery();

        validateResult(rs, sdk);
    }

    @Test
    public void testBcl() throws SQLException, IOException {
        // Configure the hostname, port, and dbname parameters based on the information about the RDS instance.
        String hostname = "pgm-****.pg.rds.aliyuncs.com";
        String port = "5432";
        String dbname = "testdb";

        // Information about the issuer.
        Connection issuerConn;
        EncdbSDK issuerSdk;
        String issuerPriKeyPemString;
        String issuerPubKeyPemString;
        // Information about the subject.
        Connection subjectConn;
        EncdbSDK subjectSdk;
        String subjectPriKeyPemString;
        String subjectPubKeyPemString;

        // Initialize the database connection and complete MEK distribution. For more information, see EncDB SDK.
        {
            // Initialize the issuer information.
            {
                // Configure the username and password parameters based on the information about the RDS instance.
                String username = "testdbuser";
                String password = "****";
                String mek = "00112233445566778899aabbccddeeff"; // This is a sample value. We recommend that you use a more complex MEK.

                // Establish a database connection.
                String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
                issuerConn = DriverManager.getConnection(dbUrl, username, password);
                System.out.println("issuer connect success");

                // Initialize the SDK and distribute the MEK. For more information, see EncDB SDK.
                issuerSdk = EncdbSDKBuilder.newInstance()
                        .setDbConnection(issuerConn)
                        .setMek(mek)
                        .setEncAlgo(EncAlgo.SM4_128_CBC)
                        .build();
                System.out.println("issuer init success");
            }
            // Initialize the subject information.
            {
                // Configure the username and password parameters based on the information about the RDS instance.
                String username = "testdbuser02";
                String password = "****";
                String mek = "ffeeddccbbaa99887766554433221100"; // This is a sample value. We recommend that you use a more complex MEK.

                // Establish a database connection.
                String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
                subjectConn = DriverManager.getConnection(dbUrl, username, password);
                System.out.println("subject connect success");

                // Initialize the SDK and distribute the MEK. For more information, see EncDB SDK.
                subjectSdk = EncdbSDKBuilder.newInstance()
                        .setDbConnection(subjectConn)
                        .setMek(mek)
                        .setEncAlgo(EncAlgo.SM4_128_CBC)
                        .build();
                System.out.println("subject init success");
            }

            // Insert test data and verify that the data owner can access and view the plaintext data after decryption.
            {
                // Write data as the issuer and verify the data.
                {
                    Connection tmpConnection = issuerConn;
                    EncdbSDK tmpSdk = issuerSdk;

                    // Prepare test data as the issuer and encrypt specific fields.
                    prepareData(tmpConnection, tmpSdk);
                    System.out.println("issuer prepare data success");

                    // Query data as the issuer to check whether the issuer can access and view the plaintext data after decryption.
                    Assertions.assertDoesNotThrow(() -> {
                        simpleQuery(tmpConnection, tmpSdk);
                    });
                    System.out.println("issuer query own data success");
                }
                // Write data as the subject and verify the data.
                {
                    Connection tmpConnection = subjectConn;
                    EncdbSDK tmpSdk = subjectSdk;

                    // Prepare test data as the subject and encrypt specific fields.
                    prepareData(tmpConnection, tmpSdk);
                    System.out.println("subject prepare data success");

                    // Query data as the subject to check whether the subject can access and view the plaintext data after decryption.
                    Assertions.assertDoesNotThrow(() -> {
                        simpleQuery(tmpConnection, tmpSdk);
                    });
                    System.out.println("subject query own data success");
                }
                // Grant access permissions on the data table. The data content in the table cannot be accessed.
                {
                    String tblname = "tbl_" + issuerConn.getMetaData().getUserName();
                    issuerConn.createStatement().execute(
                            "GRANT SELECT ON TABLE " + tblname + " TO " + subjectConn.getMetaData().getUserName());

                    // Clear historical BCL records.
                    subjectConn.createStatement().execute("DELETE FROM encdb.encdb_internal_bcl_table");
                    subjectConn.createStatement().execute("DELETE FROM encdb.encdb_internal_brl_map_table");
                    subjectConn.createStatement().execute("DELETE FROM encdb.encdb_internal_brl_table");
                }
            }
        }

        // Register the public and private keys of the subject and the issuer. You need to perform this operation only once during initialization.
        {
            // Register the public and private keys as the issuer.
            {
                // Read the public and private key content from the key files.
                String prikeyFilename = "D:\\test\\Issuer\\pri_key_pkcs8.pem"; // This is a sample value. You must replace the value with the path of the key file.
                String pubkeyFilename = "D:\\test\\Issuer\\pub_key.pem"; // This is a sample value. You must replace the value with the path of the key file.
                issuerPriKeyPemString = readPemFile(prikeyFilename);
                issuerPubKeyPemString = readPemFile(pubkeyFilename);
                // Register the public and private keys.
                issuerSdk.getKeyManager().registerCertificate(issuerPriKeyPemString, issuerPubKeyPemString);
                System.out.println("issuer register certificate success");

            }

            // Register the public and private key as the subject.
            {
                // Read the public and private key content from the key files.
                String prikeyFilename = "D:\\test\\Subject\\pri_key_pkcs8.pem"; // This is a sample value. You must replace the value with the path of the key file.
                String pubkeyFilename = "D:\\test\\Subject\\pub_key.pem"; // This is a sample value. You must replace the value with the path of the key file.
                subjectPriKeyPemString = readPemFile(prikeyFilename);
                subjectPubKeyPemString = readPemFile(pubkeyFilename);
                // Register the public and private keys.
                subjectSdk.getKeyManager().registerCertificate(subjectPriKeyPemString, subjectPubKeyPemString);
                System.out.println("subject register certificate success");
            }
        }

        // Before the multi-user data authorization, the subject attempts to access the data written by the issuer. An error message indicating that access is denied is displayed.
        {
            Connection tmpConnection = subjectConn;
            EncdbSDK tmpSdk = subjectSdk;
            Connection tmpIssuerConn = issuerConn;
            SQLException exception = Assertions.assertThrows(SQLException.class, () -> {
                subjectJoinQuery(tmpConnection, tmpSdk, tmpIssuerConn);
            });
            Assertions.assertEquals("ERROR: encdb_get_hash_from_bytea: errno:fa030000", exception.getMessage());
            System.out.println("subject query issuer data failed without authorized");
        }

        // Perform multi-user data authorization. Make sure that the authorization content is correct.
        {
            // Authorize the subject to access the DEKs of the columns specified by the issuer.
            // The DEK of the issuer is specified by issuer_dek_group. The DEK of the subject is specified by subject_dek_group.
            String bclBodyJsonString = """
                    {
                        "version": 1,
                        "serial_num": "a121bac0-5cb3-4463-a2b2-1155ff29f4c8",
                        "issuer_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "subject_pukid": "qIPPfgTJEEG/9WkjP0E5LLAijZ14h/Qgb2EfmBZCWSo=",
                        "validity": {
                            "not_after": "20250820111111+0800",
                            "not_before": "20240820111111+0800"
                        },
                        "policies": {
                            "issuer_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "e4a92b05-f64d-4665-aadd-cd1336d0c0cc"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "1010b43b-50da-4473-81ac-ce84657eb4f9"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "b99e70cf-c6b0-444f-a404-81a721e38734"
                                }
                            ],
                            "result_dek": "SUBJECT",
                            "subject_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "d1cbe5a6-49f0-42e0-ba07-572e2a5e2f5f"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "9c772fa3-4712-4034-9447-0f1e9e18fbeb"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "e2541c31-891e-4f8d-8389-09c5631f1e66"
                                }
                            ],
                            "operation": [
                                "*"
                            ],
                            "postproc": "NULL",
                            "preproc": "NULL"
                        }
                    }
                    """;

            // The sequence of the following steps can be changed: The issuer actively performs authorization. The subject requests authorization.
            // The issuer issues a BCL to allow the subject to access data that is encrypted by using the DEK specified in the BCL.
            {
                bclBodyJsonString = issuerSdk.getKeyManager().issueBCL(bclBodyJsonString, issuerPubKeyPemString,
                        issuerPriKeyPemString, true);
                System.out.println("issuer issue bcl success");
            }
            // The subject issues a request to access to data that is encrypted by using the DEK specified in the BCL.
            {
                bclBodyJsonString = subjectSdk.getKeyManager().issueBCL(bclBodyJsonString, subjectPubKeyPemString,
                        subjectPriKeyPemString, false);
                System.out.println("subject issue bcl success");
            }
        }

        // After the multi-user data authorization is complete, the subject attempts to access the data written by the issuer. The subject successfully accesses and views the decrypted content.
        {
            Connection tmpConnection = subjectConn;
            EncdbSDK tmpSdk = subjectSdk;
            Connection tmpIssuerConn = issuerConn;
            Assertions.assertDoesNotThrow(() -> {
                subjectJoinQuery(tmpConnection, tmpSdk, tmpIssuerConn);
            });
            System.out.println("subject query issuer data success after authorized");
        }
    }
}