ApsaraDB RDS:Manage authorization

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 with your RDS instance details
        String hostname = "pgm-****.pg.rds.aliyuncs.com";
        String port = "5432";
        String dbname = "testdb";
        String username = "testdbuser";
        String password = "****";
        String mek = "00112233445566778899aabbccddeeff";  // Sample value. Use a more complex MEK in production.
        String prikeyFilename = "D:\\test\\Issuer\\pri_key_pkcs8.pem"; // Replace with your actual key file path
        String pubkeyFilename = "D:\\test\\Issuer\\pub_key.pem";       // Replace with your actual key file path

        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 and insert plaintext data
            conn.createStatement().executeUpdate("DROP TABLE IF EXISTS " + tblname);
            conn.createStatement().executeUpdate("CREATE TABLE " + tblname + " (id int)");
            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");

            // Load and register the key pair
            String userPrkPemString = readPemFile(prikeyFilename);
            String userPukPemString = readPemFile(pubkeyFilename);
            sdk.getKeyManager().registerCertificate(userPrkPemString, userPukPemString);
            System.out.println("register certificate success");

            // Build the ALTER statement to convert the plaintext column to ciphertext
            String keyname = "'|" + username + "|" + dbname + "|'";
            String alterStmString = "ALTER TABLE " + tblname +
                    " ALTER COLUMN id SET DATA TYPE enc_int4 USING encdb.enc_int4_encrypt(id, " + keyname + ")";

            // Verify that conversion fails before 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;
                }
            }

            // Issue a self-BCL (issuer and subject are the same account)
            String bclBodyJsonString = """
                    {
                        "version": 1,
                        "serial_num": "fdbed057-7fe5-4c31-97ae-afdd615732fe",
                        "issuer_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "subject_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "validity": {
                            "not_before": "20240920111111+0800",
                            "not_after": "20250920111111+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": ["*"],
                            "preproc": "NULL",
                            "postproc": "NULL"
                        }
                    }
                    """;

            // Sign the BCL as both issuer and subject using the same key pair
            bclBodyJsonString = sdk.getKeyManager().issueBCL(bclBodyJsonString, userPukPemString, userPrkPemString, true);
            bclBodyJsonString = sdk.getKeyManager().issueBCL(bclBodyJsonString, userPukPemString, userPrkPemString, false);
            System.out.println("issue bcl success");

            // Conversion succeeds after authorization
            conn.createStatement().executeUpdate(alterStmString);
            System.out.println("alter column to enc_int4 success after authorized");

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}

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 {
        String tblname = "tbl_" + conn.getMetaData().getUserName();
        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))");

        PreparedStatement stmt = conn.prepareStatement(
                "INSERT INTO " + tblname + " (id, name, price, miles, secret) VALUES(?,?,?,?,?)");
        stmt.setInt(1, 1);
        stmt.setString(2, "name");
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(3, cryptor.encrypt(tblname, "price", 1234));
        stmt.setBytes(4, cryptor.encrypt(tblname, "miles", 12.34f));
        stmt.setBytes(5, cryptor.encrypt(tblname, "secret", "aliyun"));
        stmt.execute();
    }

    private void validateResult(ResultSet rs, EncdbSDK sdk) throws SQLException {
        Cryptor cryptor = sdk.getCryptor();
        while (rs.next()) {
            Assertions.assertEquals(1, rs.getInt(1));
            Assertions.assertEquals("name", rs.getString(2));
            Assertions.assertEquals(1234, cryptor.decryptInt(rs.getBytes(3)));
            Assertions.assertEquals(12.34f, cryptor.decryptFloat(rs.getBytes(4)), 0.000001f);
            Assertions.assertEquals("aliyun", cryptor.decryptString(rs.getBytes(5)));
        }
    }

    private void simpleQuery(Connection conn, EncdbSDK sdk) throws SQLException {
        String tblname = "tbl_" + conn.getMetaData().getUserName();
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + tblname + " WHERE price > ?");
        stmt.setBytes(1, sdk.getCryptor().encrypt(tblname, "price", 100));
        validateResult(stmt.executeQuery(), sdk);
    }

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

        // Subject runs a JOIN query against the issuer's encrypted table
        String sqlCmd = "SELECT subject.id, subject.name, subject.price, subject.miles, subject.secret "
                + "FROM " + issuerTblname + " issuer, " + subjectTblname + " subject "
                + "WHERE issuer.price = subject.price AND subject.price > ?";

        PreparedStatement stmt = subjectConn.prepareStatement(sqlCmd);
        stmt.setBytes(1, subjectSdk.getCryptor().encrypt(subjectTblname, "price", 100));
        validateResult(stmt.executeQuery(), subjectSdk);
    }

    @Test
    public void testBcl() throws SQLException, IOException {
        // Replace with your RDS instance details
        String hostname = "pgm-****.pg.rds.aliyuncs.com";
        String port = "5432";
        String dbname = "testdb";

        Connection issuerConn;
        EncdbSDK issuerSdk;
        String issuerPriKeyPemString;
        String issuerPubKeyPemString;

        Connection subjectConn;
        EncdbSDK subjectSdk;
        String subjectPriKeyPemString;
        String subjectPubKeyPemString;

        // ── Initialize the issuer ──────────────────────────────────────────────
        {
            String username = "testdbuser";
            String password = "****";
            String mek = "00112233445566778899aabbccddeeff"; // Sample value. Use a more complex MEK in production.
            String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
            issuerConn = DriverManager.getConnection(dbUrl, username, password);
            issuerSdk = EncdbSDKBuilder.newInstance()
                    .setDbConnection(issuerConn)
                    .setMek(mek)
                    .setEncAlgo(EncAlgo.SM4_128_CBC)
                    .build();
            System.out.println("issuer init success");
        }

        // ── Initialize the subject ─────────────────────────────────────────────
        {
            String username = "testdbuser02";
            String password = "****";
            String mek = "ffeeddccbbaa99887766554433221100"; // Sample value. Use a more complex MEK in production.
            String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
            subjectConn = DriverManager.getConnection(dbUrl, username, password);
            subjectSdk = EncdbSDKBuilder.newInstance()
                    .setDbConnection(subjectConn)
                    .setMek(mek)
                    .setEncAlgo(EncAlgo.SM4_128_CBC)
                    .build();
            System.out.println("subject init success");
        }

        // ── Prepare test data ──────────────────────────────────────────────────
        {
            // Issuer writes and verifies own data
            prepareData(issuerConn, issuerSdk);
            Assertions.assertDoesNotThrow(() -> simpleQuery(issuerConn, issuerSdk));
            System.out.println("issuer query own data success");

            // Subject writes and verifies own data
            prepareData(subjectConn, subjectSdk);
            Assertions.assertDoesNotThrow(() -> simpleQuery(subjectConn, subjectSdk));
            System.out.println("subject query own data success");

            // Grant the subject table-level SELECT permission (data remains encrypted without a BCL)
            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 key pairs ─────────────────────────────────────────────────
        {
            // Issuer registers keys
            issuerPriKeyPemString = readPemFile("D:\\test\\Issuer\\pri_key_pkcs8.pem"); // Replace with your actual path
            issuerPubKeyPemString = readPemFile("D:\\test\\Issuer\\pub_key.pem");       // Replace with your actual path
            issuerSdk.getKeyManager().registerCertificate(issuerPriKeyPemString, issuerPubKeyPemString);
            System.out.println("issuer register certificate success");

            // Subject registers keys
            subjectPriKeyPemString = readPemFile("D:\\test\\Subject\\pri_key_pkcs8.pem"); // Replace with your actual path
            subjectPubKeyPemString = readPemFile("D:\\test\\Subject\\pub_key.pem");        // Replace with your actual path
            subjectSdk.getKeyManager().registerCertificate(subjectPriKeyPemString, subjectPubKeyPemString);
            System.out.println("subject register certificate success");
        }

        // ── Verify access is denied without a BCL ─────────────────────────────
        {
            final Connection tmpSubjectConn = subjectConn;
            final EncdbSDK tmpSubjectSdk = subjectSdk;
            final Connection tmpIssuerConn = issuerConn;
            SQLException exception = Assertions.assertThrows(SQLException.class,
                    () -> subjectJoinQuery(tmpSubjectConn, tmpSubjectSdk, tmpIssuerConn));
            Assertions.assertEquals("ERROR: encdb_get_hash_from_bytea: errno:fa030000", exception.getMessage());
            System.out.println("subject query issuer data failed without authorized");
        }

        // ── Issue the cross-user BCL ──────────────────────────────────────────
        {
            String bclBodyJsonString = """
                    {
                        "version": 1,
                        "serial_num": "a121bac0-5cb3-4463-a2b2-1155ff29f4c8",
                        "issuer_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "subject_pukid": "qIPPfgTJEEG/9WkjP0E5LLAijZ14h/Qgb2EfmBZCWSo=",
                        "validity": {
                            "not_before": "20240820111111+0800",
                            "not_after": "20250820111111+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": ["*"],
                            "preproc": "NULL",
                            "postproc": "NULL"
                        }
                    }
                    """;

            // Issuer signs first, then subject
            bclBodyJsonString = issuerSdk.getKeyManager().issueBCL(
                    bclBodyJsonString, issuerPubKeyPemString, issuerPriKeyPemString, true);
            System.out.println("issuer issue bcl success");

            bclBodyJsonString = subjectSdk.getKeyManager().issueBCL(
                    bclBodyJsonString, subjectPubKeyPemString, subjectPriKeyPemString, false);
            System.out.println("subject issue bcl success");
        }

        // ── Verify access succeeds after authorization ─────────────────────────
        {
            final Connection tmpSubjectConn = subjectConn;
            final EncdbSDK tmpSubjectSdk = subjectSdk;
            final Connection tmpIssuerConn = issuerConn;
            Assertions.assertDoesNotThrow(
                    () -> subjectJoinQuery(tmpSubjectConn, tmpSubjectSdk, tmpIssuerConn));
            System.out.println("subject query issuer data success after authorized");
        }
    }
}