Spring Boot启动时如何检查Postgres用户对Schema下所有表的增删改查权限？

Great question! Unfortunately, Spring Boot doesn’t include an out-of-the-box feature specifically built to validate that your database user has SELECT, INSERT, UPDATE, and DELETE permissions on all tables in a schema. The hibernate.hbm2ddl.auto=validate setting only checks schema structure (like missing tables/columns or data type mismatches), not user privileges.

That said, there are several straightforward, reliable ways to implement this check yourself—catching permission issues at startup or during testing instead of waiting for runtime failures. Here are the most practical approaches:

1. Custom Startup Check with ApplicationRunner #

You can create a Spring component that runs automatically on application startup, uses your DataSource to connect to PostgreSQL, and queries the system catalogs to verify permissions. This ensures any missing privileges block the app from starting entirely.

Here’s a sample implementation:

import org.springframework.boot.ApplicationRunner;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

@Configuration
public class DbPermissionValidator {

    // Replace with your target schema name
    private static final String TARGET_SCHEMA = "public";
    private static final Set<String> REQUIRED_PRIVILEGES = Set.of("SELECT", "INSERT", "UPDATE", "DELETE");

    @Bean
    public ApplicationRunner permissionChecker(DataSource dataSource) {
        return args -> {
            try (Connection conn = dataSource.getConnection()) {
                String currentUser = conn.getMetaData().getUserName();
                Map<String, Set<String>> tablePrivileges = getCurrentTablePrivileges(conn, currentUser);
                
                // Check each table in the schema has all required privileges
                List<String> missingPermissions = new ArrayList<>();
                for (String table : getTablesInSchema(conn)) {
                    Set<String> missing = new HashSet<>(REQUIRED_PRIVILEGES);
                    missing.removeAll(tablePrivileges.getOrDefault(table, Collections.emptySet()));
                    
                    if (!missing.isEmpty()) {
                        missingPermissions.add(
                            String.format("Table '%s' is missing privileges: %s", 
                                table, String.join(", ", missing))
                        );
                    }
                }

                // Fail startup if any permissions are missing
                if (!missingPermissions.isEmpty()) {
                    throw new RuntimeException(
                        "Database permission check failed:\n" + String.join("\n", missingPermissions)
                    );
                }
            } catch (SQLException e) {
                throw new RuntimeException("Failed to validate database permissions", e);
            }
        };
    }

    private List<String> getTablesInSchema(Connection conn) throws SQLException {
        String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ?";
        try (var stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, TARGET_SCHEMA);
            try (var rs = stmt.executeQuery()) {
                List<String> tables = new ArrayList<>();
                while (rs.next()) {
                    tables.add(rs.getString("table_name"));
                }
                return tables;
            }
        }
    }

    private Map<String, Set<String>> getCurrentTablePrivileges(Connection conn, String user) throws SQLException {
        String sql = """
            SELECT table_name, privilege_type 
            FROM information_schema.table_privileges 
            WHERE grantee = ? AND table_schema = ?
        """;
        try (var stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, user);
            stmt.setString(2, TARGET_SCHEMA);
            try (var rs = stmt.executeQuery()) {
                Map<String, Set<String>> privileges = new HashMap<>();
                while (rs.next()) {
                    String table = rs.getString("table_name");
                    String privilege = rs.getString("privilege_type");
                    privileges.computeIfAbsent(table, k -> new HashSet<>()).add(privilege);
                }
                return privileges;
            }
        }
    }
}

This code connects to your database, fetches all tables in the target schema, then checks that each table has all four required privileges. If any are missing, it throws an exception to prevent the app from starting.

2. Use Database Migration Tool Hooks (Flyway/Liquibase) #

If you’re using Flyway or Liquibase for database migrations, you can add a pre-migration or post-migration script that validates permissions. This integrates the check into your database change workflow.

For PostgreSQL, here’s a sample PL/pgSQL script you can run as a Flyway beforeMigrate hook:

DO $$
DECLARE
    table_rec record;
    missing_privs text[];
    target_schema text := 'public';
    required_privs text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE'];
BEGIN
    -- Iterate over all tables in the target schema
    FOR table_rec IN 
        SELECT table_name FROM information_schema.tables WHERE table_schema = target_schema
    LOOP
        -- Check each required privilege
        FOREACH priv IN ARRAY required_privs LOOP
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.table_privileges
                WHERE grantee = current_user
                  AND table_schema = target_schema
                  AND table_name = table_rec.table_name
                  AND privilege_type = priv
            ) THEN
                missing_privs := array_append(missing_privs, 
                    format('%s.%s: %s', target_schema, table_rec.table_name, priv));
            END IF;
        END LOOP;
    END LOOP;

    -- Raise exception if any privileges are missing
    IF array_length(missing_privs, 1) > 0 THEN
        RAISE EXCEPTION 'Missing database privileges: %', array_to_string(missing_privs, ', ');
    END IF;
END $$;

This script will fail the migration process if any permissions are missing, ensuring you catch issues before applying schema changes.

3. Integration Tests for CI/CD Pipelines #

Add an integration test to your build pipeline that validates permissions. This ensures permissions are checked every time you run tests, catching issues early in development.

Here’s a sample Spring Boot Test:

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
public class DbPermissionIntegrationTest {

    @Autowired
    private DataSource dataSource;

    @Test
    void testAllTablesHaveRequiredPermissions() throws SQLException {
        DbPermissionValidator validator = new DbPermissionValidator();
        try (Connection conn = dataSource.getConnection()) {
            String currentUser = conn.getMetaData().getUserName();
            var tablePrivileges = validator.getCurrentTablePrivileges(conn, currentUser);
            var tables = validator.getTablesInSchema(conn);

            List<String> missingPermissions = new ArrayList<>();
            for (String table : tables) {
                var missing = new HashSet<>(DbPermissionValidator.REQUIRED_PRIVILEGES);
                missing.removeAll(tablePrivileges.getOrDefault(table, Collections.emptySet()));
                
                if (!missing.isEmpty()) {
                    missingPermissions.add(
                        String.format("Table '%s' missing: %s", table, String.join(", ", missing))
                    );
                }
            }

            assertTrue(missingPermissions.isEmpty(), 
                "Missing database permissions:\n" + String.join("\n", missingPermissions));
        }
    }
}

This test will fail your build if any permissions are missing, preventing broken configurations from reaching production.

内容的提问来源于stack exchange，提问作者Kiran Pophale