google-site-verification: google61fe8ba583a51912.html
MACRO NEPAL
PostgREST Security in Java: Complete Implementation Guide
awiskar acharya
November 21, 2025
2:23 pm

PostgREST is a powerful tool that turns PostgreSQL databases into RESTful APIs. This guide provides comprehensive Java-based security implementations for securing PostgREST APIs with authentication, authorization, row-level security, and API gateway integration.

PostgREST Security Architecture

Security Layers:

  1. API Gateway Security - Rate limiting, IP whitelisting, SSL termination
  2. Authentication - JWT tokens, API keys, OAuth2
  3. Authorization - Row Level Security (RLS), role-based access
  4. Database Security - RLS policies, schema permissions, function security

Dependencies and Setup

Maven Dependencies
<properties> <spring-boot.version>3.1.0</spring-boot.version> <jjwt.version>0.11.5</jjwt.version> <postgresql.version>42.6.0</postgresql.version> </properties> <dependencies> <!-- Spring Boot --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>${spring-boot.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> <version>${spring-boot.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> <version>${spring-boot.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> <version>${spring-boot.version}</version> </dependency> <!-- Database --> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>${postgresql.version}</version> </dependency> <!-- JWT --> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt-api</artifactId> <version>${jjwt.version}</version> </dependency> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt-impl</artifactId> <version>${jjwt.version}</version> <scope>runtime</scope> </dependency> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt-jackson</artifactId> <version>${jjwt.version}</version> <scope>runtime</scope> </dependency> <!-- Utilities --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> <!-- Testing --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <version>${spring-boot.version}</version> <scope>test</scope> </dependency> </dependencies>
Application Configuration
# application.yml server: port: 8080 spring: datasource: url: jdbc:postgresql://localhost:5432/postgrest_secure username: postgrest_api password: ${DB_PASSWORD:securepassword} driver-class-name: org.postgresql.Driver jpa: hibernate: ddl-auto: validate show-sql: false properties: hibernate: dialect: org.hibernate.dialect.PostgreSQLDialect app: security: # JWT Configuration jwt: secret: ${JWT_SECRET:your-super-secure-jwt-secret-key-minimum-32-chars} expiration: 3600 # 1 hour issuer: postgrest-security # API Gateway Configuration gateway: rate-limit: requests-per-minute: 100 burst-capacity: 20 ip-whitelist: enabled: true allowed-ips: "192.168.1.0/24,10.0.0.0/8" # PostgREST Configuration postgrest: base-url: "http://localhost:3000" schema: "api" anon-role: "web_anon" auth-role: "web_user" # Row Level Security rls: enabled: true auto-create-policies: true logging: level: com.example.postgrestsecurity: DEBUG org.springframework.security: INFO

Database Security Models

1. Database Schema and RLS Setup
-- Database setup for PostgREST with RLS CREATE DATABASE postgrest_secure; -- Create roles for PostgREST CREATE ROLE web_anon NOLOGIN; CREATE ROLE web_user NOLOGIN; CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'securepassword'; GRANT web_anon TO authenticator; GRANT web_user TO authenticator; -- Create API schema CREATE SCHEMA api; CREATE SCHEMA auth; CREATE SCHEMA private; -- Set search path ALTER DATABASE postgrest_secure SET search_path TO api, auth, private; -- Grant usage to roles GRANT USAGE ON SCHEMA api TO web_anon, web_user; GRANT USAGE ON SCHEMA auth TO web_anon, web_user; GRANT USAGE ON SCHEMA private TO web_user;
2. User and Role Management
// User.java - Application user entity package com.example.postgrestsecurity.entity; import lombok.Data; import lombok.EqualsAndHashCode; import jakarta.persistence.*; import java.time.LocalDateTime; import java.util.HashSet; import java.util.Set; @Entity @Table(name = "users", schema = "auth") @Data @EqualsAndHashCode(onlyExplicitlyIncluded = true) public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @EqualsAndHashCode.Include @Column(name = "username", unique = true, nullable = false) private String username; @Column(name = "email", unique = true, nullable = false) private String email; @Column(name = "password_hash") private String passwordHash; @Column(name = "is_active", nullable = false) private Boolean isActive = true; @Column(name = "created_at", nullable = false) private LocalDateTime createdAt; @Column(name = "updated_at") private LocalDateTime updatedAt; @Column(name = "last_login") private LocalDateTime lastLogin; @ManyToMany(fetch = FetchType.LAZY) @JoinTable( name = "user_roles", schema = "auth", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id") ) private Set<Role> roles = new HashSet<>(); @ElementCollection @CollectionTable(name = "user_tenants", schema = "auth", joinColumns = @JoinColumn(name = "user_id")) @Column(name = "tenant_id") private Set<String> tenantIds = new HashSet<>(); @PrePersist protected void onCreate() { createdAt = LocalDateTime.now(); updatedAt = LocalDateTime.now(); } @PreUpdate protected void onUpdate() { updatedAt = LocalDateTime.now(); } } // Role.java - Role entity package com.example.postgrestsecurity.entity; import lombok.Data; import jakarta.persistence.*; import java.time.LocalDateTime; import java.util.HashSet; import java.util.Set; @Entity @Table(name = "roles", schema = "auth") @Data public class Role { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "name", unique = true, nullable = false) private String name; @Column(name = "description") private String description; @Column(name = "is_system", nullable = false) private Boolean isSystem = false; @Column(name = "created_at", nullable = false) private LocalDateTime createdAt; @ManyToMany(fetch = FetchType.LAZY) @JoinTable( name = "role_permissions", schema = "auth", joinColumns = @JoinColumn(name = "role_id"), inverseJoinColumns = @JoinColumn(name = "permission_id") ) private Set<Permission> permissions = new HashSet<>(); @PrePersist protected void onCreate() { createdAt = LocalDateTime.now(); } } // Permission.java - Permission entity package com.example.postgrestsecurity.entity; import lombok.Data; import jakarta.persistence.*; import java.time.LocalDateTime; @Entity @Table(name = "permissions", schema = "auth") @Data public class Permission { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "name", unique = true, nullable = false) private String name; @Column(name = "description") private String description; @Column(name = "resource") private String resource; @Column(name = "action") private String action; // CREATE, READ, UPDATE, DELETE @Column(name = "created_at", nullable = false) private LocalDateTime createdAt; @PrePersist protected void onCreate() { createdAt = LocalDateTime.now(); } }
3. JWT Token Models
// JwtToken.java package com.example.postgrestsecurity.model; import lombok.Data; import lombok.Builder; import java.time.LocalDateTime; import java.util.Map; import java.util.Set; @Data @Builder public class JwtToken { private String token; private String type; private LocalDateTime issuedAt; private LocalDateTime expiresAt; private Map<String, Object> claims; public boolean isValid() { return expiresAt != null && expiresAt.isAfter(LocalDateTime.now()); } } // JwtClaims.java package com.example.postgrestsecurity.model; import lombok.Data; import lombok.Builder; import java.util.Set; @Data @Builder public class JwtClaims { private String subject; private String username; private String email; private Set<String> roles; private Set<String> permissions; private Set<String> tenantIds; private String issuer; private LocalDateTime issuedAt; private LocalDateTime expiresAt; // PostgREST specific claims private String role; private Map<String, String> databaseClaims; public static class DatabaseClaims { public static final String USER_ID = "user_id"; public static final String TENANT_ID = "tenant_id"; public static final String ROLE = "role"; } } // AuthRequest.java package com.example.postgrestsecurity.model; import lombok.Data; import jakarta.validation.constraints.NotBlank; @Data public class AuthRequest { @NotBlank(message = "Username is required") private String username; @NotBlank(message = "Password is required") private String password; private String tenantId; } // AuthResponse.java package com.example.postgrestsecurity.model; import lombok.Data; import lombok.Builder; @Data @Builder public class AuthResponse { private boolean success; private String message; private String accessToken; private String refreshToken; private Long expiresIn; private String tokenType; private UserInfo user; @Data @Builder public static class UserInfo { private Long id; private String username; private String email; private Set<String> roles; private Set<String> permissions; private Set<String> tenantIds; } }

JWT Token Service

// JwtTokenService.java package com.example.postgrestsecurity.service; import com.example.postgrestsecurity.entity.User; import com.example.postgrestsecurity.model.JwtClaims; import com.example.postgrestsecurity.model.JwtToken; import io.jsonwebtoken.*; import io.jsonwebtoken.security.Keys; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import javax.crypto.SecretKey; import java.time.LocalDateTime; import java.time.ZoneId; import java.util.Date; import java.util.HashMap; import java.util.Map; import java.util.stream.Collectors; @Slf4j @Service public class JwtTokenService { private final SecretKey secretKey; private final long expiration; private final String issuer; public JwtTokenService( @Value("${app.security.jwt.secret}") String secret, @Value("${app.security.jwt.expiration}") long expiration, @Value("${app.security.jwt.issuer}") String issuer) { this.secretKey = Keys.hmacShaKeyFor(secret.getBytes()); this.expiration = expiration; this.issuer = issuer; } /** * Generate JWT token for user with PostgREST claims */ public JwtToken generateToken(User user) { LocalDateTime now = LocalDateTime.now(); LocalDateTime expiresAt = now.plusSeconds(expiration); // Build claims for PostgREST Map<String, Object> claims = buildPostgRESTClaims(user); String token = Jwts.builder() .setIssuer(issuer) .setSubject(user.getId().toString()) .setAudience("postgrest-api") .setIssuedAt(Date.from(now.atZone(ZoneId.systemDefault()).toInstant())) .setExpiration(Date.from(expiresAt.atZone(ZoneId.systemDefault()).toInstant())) .addClaims(claims) .signWith(secretKey, SignatureAlgorithm.HS256) .compact(); return JwtToken.builder() .token(token) .type("Bearer") .issuedAt(now) .expiresAt(expiresAt) .claims(claims) .build(); } /** * Build PostgREST specific claims */ private Map<String, Object> buildPostgRESTClaims(User user) { Map<String, Object> claims = new HashMap<>(); // Standard claims claims.put("username", user.getUsername()); claims.put("email", user.getEmail()); // Role and permission claims claims.put("roles", user.getRoles().stream() .map(role -> role.getName()) .collect(Collectors.toSet())); claims.put("permissions", user.getRoles().stream() .flatMap(role -> role.getPermissions().stream()) .map(permission -> permission.getName()) .collect(Collectors.toSet())); // Tenant claims claims.put("tenant_ids", user.getTenantIds()); // PostgREST specific claims for RLS claims.put("role", "web_user"); // PostgREST role // Database claims for RLS policies Map<String, String> databaseClaims = new HashMap<>(); databaseClaims.put("user_id", user.getId().toString()); if (!user.getTenantIds().isEmpty()) { databaseClaims.put("tenant_id", user.getTenantIds().iterator().next()); } databaseClaims.put("role", "web_user"); claims.put("app_metadata", databaseClaims); return claims; } /** * Validate and parse JWT token */ public JwtClaims validateToken(String token) { try { Claims claims = Jwts.parserBuilder() .setSigningKey(secretKey) .build() .parseClaimsJws(token) .getBody(); LocalDateTime expiresAt = claims.getExpiration().toInstant() .atZone(ZoneId.systemDefault()) .toLocalDateTime(); // Extract PostgREST claims return JwtClaims.builder() .subject(claims.getSubject()) .username(claims.get("username", String.class)) .email(claims.get("email", String.class)) .roles(claims.get("roles", java.util.Set.class)) .permissions(claims.get("permissions", java.util.Set.class)) .tenantIds(claims.get("tenant_ids", java.util.Set.class)) .issuer(claims.getIssuer()) .issuedAt(claims.getIssuedAt().toInstant() .atZone(ZoneId.systemDefault()) .toLocalDateTime()) .expiresAt(expiresAt) .role(claims.get("role", String.class)) .databaseClaims(claims.get("app_metadata", java.util.Map.class)) .build(); } catch (ExpiredJwtException e) { log.warn("JWT token expired: {}", e.getMessage()); throw new SecurityException("Token expired", e); } catch (MalformedJwtException e) { log.warn("Invalid JWT token: {}", e.getMessage()); throw new SecurityException("Invalid token", e); } catch (JwtException e) { log.error("JWT validation error: {}", e.getMessage()); throw new SecurityException("Token validation failed", e); } } /** * Generate PostgREST compatible JWT token */ public String generatePostgRESTToken(User user) { Map<String, Object> claims = buildPostgRESTClaims(user); return Jwts.builder() .setIssuer(issuer) .setSubject(user.getId().toString()) .setExpiration(Date.from(LocalDateTime.now().plusSeconds(expiration) .atZone(ZoneId.systemDefault()).toInstant())) .addClaims(claims) .signWith(secretKey, SignatureAlgorithm.HS256) .compact(); } /** * Refresh token */ public JwtToken refreshToken(String token) { JwtClaims claims = validateToken(token); // In a real implementation, you would fetch the user from database // and generate a new token throw new UnsupportedOperationException("Token refresh not implemented"); } }

Authentication Service

// AuthenticationService.java package com.example.postgrestsecurity.service; import com.example.postgrestsecurity.entity.User; import com.example.postgrestsecurity.model.AuthRequest; import com.example.postgrestsecurity.model.AuthResponse; import com.example.postgrestsecurity.repository.UserRepository; import lombok.extern.slf4j.Slf4j; import org.springframework.security.crypto.password.PasswordEncoder; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.time.LocalDateTime; import java.util.Optional; @Slf4j @Service @Transactional public class AuthenticationService { private final UserRepository userRepository; private final JwtTokenService jwtTokenService; private final PasswordEncoder passwordEncoder; public AuthenticationService(UserRepository userRepository, JwtTokenService jwtTokenService, PasswordEncoder passwordEncoder) { this.userRepository = userRepository; this.jwtTokenService = jwtTokenService; this.passwordEncoder = passwordEncoder; } /** * Authenticate user and generate JWT token */ public AuthResponse authenticate(AuthRequest authRequest) { try { Optional<User> userOpt = userRepository.findByUsername(authRequest.getUsername()); if (userOpt.isEmpty()) { log.warn("Authentication failed: user not found - {}", authRequest.getUsername()); return AuthResponse.builder() .success(false) .message("Invalid credentials") .build(); } User user = userOpt.get(); // Check if user is active if (!user.getIsActive()) { log.warn("Authentication failed: user inactive - {}", authRequest.getUsername()); return AuthResponse.builder() .success(false) .message("Account is disabled") .build(); } // Verify password if (!passwordEncoder.matches(authRequest.getPassword(), user.getPasswordHash())) { log.warn("Authentication failed: invalid password - {}", authRequest.getUsername()); return AuthResponse.builder() .success(false) .message("Invalid credentials") .build(); } // Check tenant access if tenantId is provided if (authRequest.getTenantId() != null && !user.getTenantIds().contains(authRequest.getTenantId())) { log.warn("Authentication failed: tenant access denied - user: {}, tenant: {}", authRequest.getUsername(), authRequest.getTenantId()); return AuthResponse.builder() .success(false) .message("Access denied to tenant") .build(); } // Update last login user.setLastLogin(LocalDateTime.now()); userRepository.save(user); // Generate tokens String accessToken = jwtTokenService.generatePostgRESTToken(user); log.info("Authentication successful for user: {}", authRequest.getUsername()); return AuthResponse.builder() .success(true) .message("Authentication successful") .accessToken(accessToken) .tokenType("Bearer") .expiresIn(3600L) .user(buildUserInfo(user)) .build(); } catch (Exception e) { log.error("Authentication error for user: {}", authRequest.getUsername(), e); return AuthResponse.builder() .success(false) .message("Authentication failed: " + e.getMessage()) .build(); } } /** * Validate JWT token */ public boolean validateToken(String token) { try { jwtTokenService.validateToken(token); return true; } catch (Exception e) { log.debug("Token validation failed: {}", e.getMessage()); return false; } } /** * Get user from JWT token */ public Optional<User> getUserFromToken(String token) { try { com.example.postgrestsecurity.model.JwtClaims claims = jwtTokenService.validateToken(token); return userRepository.findById(Long.parseLong(claims.getSubject())); } catch (Exception e) { log.debug("Failed to get user from token: {}", e.getMessage()); return Optional.empty(); } } private AuthResponse.UserInfo buildUserInfo(User user) { return AuthResponse.UserInfo.builder() .id(user.getId()) .username(user.getUsername()) .email(user.getEmail()) .roles(user.getRoles().stream() .map(role -> role.getName()) .collect(java.util.stream.Collectors.toSet())) .permissions(user.getRoles().stream() .flatMap(role -> role.getPermissions().stream()) .map(permission -> permission.getName()) .collect(java.util.stream.Collectors.toSet())) .tenantIds(user.getTenantIds()) .build(); } }

Row Level Security (RLS) Service

// RowLevelSecurityService.java package com.example.postgrestsecurity.service; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import javax.annotation.PostConstruct; import java.util.List; import java.util.Map; @Slf4j @Service public class RowLevelSecurityService { private final JdbcTemplate jdbcTemplate; private final boolean rlsEnabled; public RowLevelSecurityService(JdbcTemplate jdbcTemplate, @Value("${app.security.rls.enabled:true}") boolean rlsEnabled) { this.jdbcTemplate = jdbcTemplate; this.rlsEnabled = rlsEnabled; } @PostConstruct public void initializeRLS() { if (!rlsEnabled) { log.info("Row Level Security is disabled"); return; } try { enableRLS(); createRLSPolicies(); log.info("Row Level Security initialized successfully"); } catch (Exception e) { log.error("Failed to initialize Row Level Security", e); } } /** * Enable RLS on tables */ public void enableRLS() { // Enable RLS on API schema tables String[] tables = {"users", "products", "orders", "tenants"}; for (String table : tables) { try { jdbcTemplate.execute("ALTER TABLE api." + table + " ENABLE ROW LEVEL SECURITY"); log.debug("Enabled RLS on table: {}", table); } catch (Exception e) { log.warn("Failed to enable RLS on table {}: {}", table, e.getMessage()); } } } /** * Create RLS policies */ public void createRLSPolicies() { createUserPolicies(); createProductPolicies(); createOrderPolicies(); createTenantPolicies(); } /** * RLS policies for users table */ private void createUserPolicies() { try { // Drop existing policies jdbcTemplate.execute("DROP POLICY IF EXISTS users_select_policy ON api.users"); jdbcTemplate.execute("DROP POLICY IF EXISTS users_insert_policy ON api.users"); jdbcTemplate.execute("DROP POLICY IF EXISTS users_update_policy ON api.users"); jdbcTemplate.execute("DROP POLICY IF EXISTS users_delete_policy ON api.users"); // Users can read their own data jdbcTemplate.execute( "CREATE POLICY users_select_policy ON api.users FOR SELECT " + "USING (id = current_setting('request.jwt.claims', true)::json->>'user_id'::bigint " + "OR 'admin' = ANY(current_setting('request.jwt.claims', true)::json->'roles'))" ); // Users can update their own data jdbcTemplate.execute( "CREATE POLICY users_update_policy ON api.users FOR UPDATE " + "USING (id = current_setting('request.jwt.claims', true)::json->>'user_id'::bigint)" ); log.debug("Created user RLS policies"); } catch (Exception e) { log.error("Failed to create user RLS policies: {}", e.getMessage()); } } /** * RLS policies for products table (multi-tenant) */ private void createProductPolicies() { try { jdbcTemplate.execute("DROP POLICY IF EXISTS products_select_policy ON api.products"); jdbcTemplate.execute("DROP POLICY IF EXISTS products_insert_policy ON api.products"); jdbcTemplate.execute("DROP POLICY IF EXISTS products_update_policy ON api.products"); jdbcTemplate.execute("DROP POLICY IF EXISTS products_delete_policy ON api.products"); // Users can read products from their tenants jdbcTemplate.execute( "CREATE POLICY products_select_policy ON api.products FOR SELECT " + "USING (tenant_id = ANY(string_to_array(current_setting('request.jwt.claims', true)::json->'app_metadata'->>'tenant_ids', ',')::text[]))" ); // Users can insert products for their tenants jdbcTemplate.execute( "CREATE POLICY products_insert_policy ON api.products FOR INSERT " + "WITH CHECK (tenant_id = ANY(string_to_array(current_setting('request.jwt.claims', true)::json->'app_metadata'->>'tenant_ids', ',')::text[]))" ); // Users can update products from their tenants jdbcTemplate.execute( "CREATE POLICY products_update_policy ON api.products FOR UPDATE " + "USING (tenant_id = ANY(string_to_array(current_setting('request.jwt.claims', true)::json->'app_metadata'->>'tenant_ids', ',')::text[]))" ); // Users can delete products from their tenants jdbcTemplate.execute( "CREATE POLICY products_delete_policy ON api.products FOR DELETE " + "USING (tenant_id = ANY(string_to_array(current_setting('request.jwt.claims', true)::json->'app_metadata'->>'tenant_ids', ',')::text[]))" ); log.debug("Created product RLS policies"); } catch (Exception e) { log.error("Failed to create product RLS policies: {}", e.getMessage()); } } /** * RLS policies for orders table */ private void createOrderPolicies() { try { jdbcTemplate.execute("DROP POLICY IF EXISTS orders_select_policy ON api.orders"); jdbcTemplate.execute("DROP POLICY IF EXISTS orders_insert_policy ON api.orders"); jdbcTemplate.execute("DROP POLICY IF EXISTS orders_update_policy ON api.orders"); // Users can read their own orders jdbcTemplate.execute( "CREATE POLICY orders_select_policy ON api.orders FOR SELECT " + "USING (user_id = current_setting('request.jwt.claims', true)::json->>'user_id'::bigint " + "OR tenant_id = ANY(string_to_array(current_setting('request.jwt.claims', true)::json->'app_metadata'->>'tenant_ids', ',')::text[]))" ); // Users can insert their own orders jdbcTemplate.execute( "CREATE POLICY orders_insert_policy ON api.orders FOR INSERT " + "WITH CHECK (user_id = current_setting('request.jwt.claims', true)::json->>'user_id'::bigint)" ); log.debug("Created order RLS policies"); } catch (Exception e) { log.error("Failed to create order RLS policies: {}", e.getMessage()); } } /** * RLS policies for tenants table */ private void createTenantPolicies() { try { jdbcTemplate.execute("DROP POLICY IF EXISTS tenants_select_policy ON api.tenants"); // Users can read tenants they have access to jdbcTemplate.execute( "CREATE POLICY tenants_select_policy ON api.tenants FOR SELECT " + "USING (id::text = ANY(string_to_array(current_setting('request.jwt.claims', true)::json->'app_metadata'->>'tenant_ids', ',')::text[]))" ); log.debug("Created tenant RLS policies"); } catch (Exception e) { log.error("Failed to create tenant RLS policies: {}", e.getMessage()); } } /** * Get current RLS policies */ public List<Map<String, Object>> getCurrentPolicies() { return jdbcTemplate.queryForList( "SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check " + "FROM pg_policies " + "WHERE schemaname = 'api' " + "ORDER BY tablename, policyname" ); } /** * Create custom RLS policy */ public void createCustomPolicy(String tableName, String policyName, String operation, String usingClause, String withCheckClause) { try { String sql = String.format( "CREATE POLICY %s ON api.%s FOR %s " + "USING (%s) %s", policyName, tableName, operation, usingClause, withCheckClause != null ? "WITH CHECK (" + withCheckClause + ")" : "" ); jdbcTemplate.execute(sql); log.info("Created custom RLS policy: {} on table {}", policyName, tableName); } catch (Exception e) { log.error("Failed to create custom RLS policy: {}", e.getMessage()); throw new RuntimeException("Failed to create RLS policy", e); } } }

API Gateway Security

// ApiGatewaySecurityService.java package com.example.postgrestsecurity.service; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import jakarta.servlet.http.HttpServletRequest; import java.util.Arrays; import java.util.HashSet; import java.util.Set; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.atomic.AtomicInteger; @Slf4j @Service public class ApiGatewaySecurityService { private final Set<String> allowedIps; private final boolean ipWhitelistEnabled; private final int rateLimit; private final int burstCapacity; // Rate limiting storage private final ConcurrentHashMap<String, AtomicInteger> requestCounts = new ConcurrentHashMap<>(); private final ConcurrentHashMap<String, Long> lastResetTimes = new ConcurrentHashMap<>(); public ApiGatewaySecurityService( @Value("${app.security.gateway.ip-whitelist.enabled:false}") boolean ipWhitelistEnabled, @Value("${app.security.gateway.ip-whitelist.allowed-ips:}") String allowedIpsConfig, @Value("${app.security.gateway.rate-limit.requests-per-minute:100}") int rateLimit, @Value("${app.security.gateway.rate-limit.burst-capacity:20}") int burstCapacity) { this.ipWhitelistEnabled = ipWhitelistEnabled; this.rateLimit = rateLimit; this.burstCapacity = burstCapacity; this.allowedIps = parseAllowedIps(allowedIpsConfig); } /** * Validate client IP address */ public boolean isIpAllowed(HttpServletRequest request) { if (!ipWhitelistEnabled) { return true; } String clientIp = getClientIpAddress(request); boolean allowed = isIpInWhitelist(clientIp); if (!allowed) { log.warn("IP address blocked: {}", clientIp); } return allowed; } /** * Check rate limiting */ public boolean isRateLimited(HttpServletRequest request) { String clientId = getClientIdentifier(request); long currentTime = System.currentTimeMillis(); long oneMinuteAgo = currentTime - 60000; // Clean up old entries lastResetTimes.entrySet().removeIf(entry -> entry.getValue() < oneMinuteAgo); requestCounts.entrySet().removeIf(entry -> !lastResetTimes.containsKey(entry.getKey())); // Get or create request counter AtomicInteger counter = requestCounts.computeIfAbsent(clientId, k -> new AtomicInteger(0)); Long lastReset = lastResetTimes.computeIfAbsent(clientId, k -> currentTime); // Reset counter if more than a minute has passed if (currentTime - lastReset > 60000) { counter.set(0); lastResetTimes.put(clientId, currentTime); } // Check rate limit int currentCount = counter.incrementAndGet(); boolean limited = currentCount > rateLimit; if (limited) { log.warn("Rate limit exceeded for client: {} - {} requests", clientId, currentCount); } return limited; } /** * Validate request headers */ public boolean validateHeaders(HttpServletRequest request) { // Check for required headers String userAgent = request.getHeader("User-Agent"); if (userAgent == null || userAgent.trim().isEmpty()) { log.warn("Request blocked: missing User-Agent header"); return false; } // Check content type for POST/PUT requests String method = request.getMethod(); if ("POST".equals(method) || "PUT".equals(method)) { String contentType = request.getContentType(); if (contentType == null || (!contentType.contains("application/json") && !contentType.contains("application/x-www-form-urlencoded"))) { log.warn("Request blocked: invalid Content-Type - {}", contentType); return false; } } return true; } /** * Get client IP address */ private String getClientIpAddress(HttpServletRequest request) { String xForwardedFor = request.getHeader("X-Forwarded-For"); if (xForwardedFor != null && !xForwardedFor.isEmpty()) { return xForwardedFor.split(",")[0].trim(); } String xRealIp = request.getHeader("X-Real-IP"); if (xRealIp != null && !xRealIp.isEmpty()) { return xRealIp; } return request.getRemoteAddr(); } /** * Get client identifier for rate limiting */ private String getClientIdentifier(HttpServletRequest request) { // Use API key if available, otherwise use IP address String apiKey = request.getHeader("X-API-Key"); if (apiKey != null && !apiKey.isEmpty()) { return "api_key:" + apiKey; } return "ip:" + getClientIpAddress(request); } /** * Check if IP is in whitelist */ private boolean isIpInWhitelist(String ip) { if (allowedIps.isEmpty()) { return true; // Allow all if no whitelist configured } // Simple CIDR matching (for production, use a proper IP matching library) for (String allowedIp : allowedIps) { if (allowedIp.contains("/")) { // CIDR notation - simplified check if (isIpInCidr(ip, allowedIp)) { return true; } } else { // Exact IP match if (ip.equals(allowedIp)) { return true; } } } return false; } /** * Simple CIDR matching (for production, use a proper library) */ private boolean isIpInCidr(String ip, String cidr) { try { String[] parts = cidr.split("/"); String network = parts[0]; int prefix = Integer.parseInt(parts[1]); // Simplified implementation - for production use a proper IP math library return ip.startsWith(network.substring(0, network.lastIndexOf('.'))); } catch (Exception e) { log.warn("Invalid CIDR notation: {}", cidr); return false; } } /** * Parse allowed IPs from configuration */ private Set<String> parseAllowedIps(String allowedIpsConfig) { Set<String> ips = new HashSet<>(); if (allowedIpsConfig != null && !allowedIpsConfig.trim().isEmpty()) { String[] ipArray = allowedIpsConfig.split(","); for (String ip : ipArray) { ips.add(ip.trim()); } } return ips; } /** * Get security metrics */ public SecurityMetrics getSecurityMetrics() { return SecurityMetrics.builder() .totalClients(requestCounts.size()) .ipWhitelistEnabled(ipWhitelistEnabled) .allowedIpsCount(allowedIps.size()) .rateLimit(rateLimit) .burstCapacity(burstCapacity) .build(); } @lombok.Data @lombok.Builder public static class SecurityMetrics { private int totalClients; private boolean ipWhitelistEnabled; private int allowedIpsCount; private int rateLimit; private int burstCapacity; } }

PostgREST Proxy Controller

// PostgRESTProxyController.java package com.example.postgrestsecurity.controller; import com.example.postgrestsecurity.service.ApiGatewaySecurityService; import com.example.postgrestsecurity.service.AuthenticationService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.http.*; import org.springframework.web.bind.annotation.*; import org.springframework.web.client.RestTemplate; import jakarta.servlet.http.HttpServletRequest; import java.util.Enumeration; import java.util.HashMap; import java.util.Map; @Slf4j @RestController @RequestMapping("/api/proxy") public class PostgRESTProxyController { private final RestTemplate restTemplate; private final AuthenticationService authenticationService; private final ApiGatewaySecurityService apiGatewaySecurityService; @Value("${app.security.postgrest.base-url:http://localhost:3000}") private String postgrestBaseUrl; public PostgRESTProxyController(AuthenticationService authenticationService, ApiGatewaySecurityService apiGatewaySecurityService) { this.restTemplate = new RestTemplate(); this.authenticationService = authenticationService; this.apiGatewaySecurityService = apiGatewaySecurityService; } /** * Proxy requests to PostgREST with security checks */ @RequestMapping("/**") public ResponseEntity<String> proxyRequest(HttpServletRequest request) { try { // 1. API Gateway Security Checks if (!apiGatewaySecurityService.validateHeaders(request)) { return ResponseEntity.status(HttpStatus.BAD_REQUEST) .body("{\"error\": \"Invalid request headers\"}"); } if (!apiGatewaySecurityService.isIpAllowed(request)) { return ResponseEntity.status(HttpStatus.FORBIDDEN) .body("{\"error\": \"IP address not allowed\"}"); } if (apiGatewaySecurityService.isRateLimited(request)) { return ResponseEntity.status(HttpStatus.TOO_MANY_REQUESTS) .body("{\"error\": \"Rate limit exceeded\"}"); } // 2. Authentication Check String authHeader = request.getHeader("Authorization"); if (authHeader == null || !authHeader.startsWith("Bearer ")) { return ResponseEntity.status(HttpStatus.UNAUTHORIZED) .body("{\"error\": \"Authentication required\"}"); } String token = authHeader.substring(7); if (!authenticationService.validateToken(token)) { return ResponseEntity.status(HttpStatus.UNAUTHORIZED) .body("{\"error\": \"Invalid or expired token\"}"); } // 3. Build PostgREST request String path = extractPath(request); String url = postgrestBaseUrl + path; HttpMethod method = HttpMethod.valueOf(request.getMethod()); HttpHeaders headers = buildPostgRESTHeaders(request, token); String body = extractRequestBody(request); HttpEntity<String> entity = new HttpEntity<>(body, headers); // 4. Forward request to PostgREST ResponseEntity<String> response = restTemplate.exchange(url, method, entity, String.class); log.debug("Proxied request to PostgREST: {} {} - Status: {}", method, path, response.getStatusCode()); return response; } catch (Exception e) { log.error("Proxy request failed", e); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR) .body("{\"error\": \"Internal server error: " + e.getMessage() + "\"}"); } } /** * Extract path from request */ private String extractPath(HttpServletRequest request) { String path = request.getRequestURI(); String contextPath = request.getContextPath(); String proxyPath = "/api/proxy"; if (path.startsWith(contextPath + proxyPath)) { return path.substring((contextPath + proxyPath).length()); } return path; } /** * Build headers for PostgREST */ private HttpHeaders buildPostgRESTHeaders(HttpServletRequest request, String token) { HttpHeaders headers = new HttpHeaders(); // Copy relevant headers Enumeration<String> headerNames = request.getHeaderNames(); while (headerNames.hasMoreElements()) { String headerName = headerNames.nextElement(); if (shouldForwardHeader(headerName)) { String headerValue = request.getHeader(headerName); headers.add(headerName, headerValue); } } // Set PostgREST specific headers headers.set("Authorization", "Bearer " + token); headers.set("Accept", "application/json"); // Set Prefer headers for PostgREST String preferHeader = request.getHeader("Prefer"); if (preferHeader != null) { headers.set("Prefer", preferHeader); } return headers; } /** * Determine which headers to forward */ private boolean shouldForwardHeader(String headerName) { String lowerHeader = headerName.toLowerCase(); // Don't forward these headers if (lowerHeader.equals("host") || lowerHeader.equals("authorization") || lowerHeader.equals("content-length") || lowerHeader.startsWith("x-forwarded-") || lowerHeader.startsWith("x-real-")) { return false; } // Forward content-related headers if (lowerHeader.startsWith("content-") || lowerHeader.startsWith("accept") || lowerHeader.startsWith("prefer")) { return true; } return true; } /** * Extract request body */ private String extractRequestBody(HttpServletRequest request) { // For simplicity, this is a basic implementation // In production, you would need to read the request body properly try { if ("POST".equals(request.getMethod()) || "PUT".equals(request.getMethod()) || "PATCH".equals(request.getMethod())) { // You would need to cache the request body to read it multiple times // This is a simplified version return ""; // Implement proper body extraction } } catch (Exception e) { log.warn("Failed to extract request body", e); } return null; } /** * Health check endpoint */ @GetMapping("/health") public ResponseEntity<Map<String, Object>> health() { Map<String, Object> health = new HashMap<>(); health.put("status", "healthy"); health.put("service", "postgrest-proxy"); health.put("timestamp", java.time.LocalDateTime.now()); try { // Test PostgREST connection ResponseEntity<String> response = restTemplate.getForEntity(postgrestBaseUrl, String.class); health.put("postgrest", response.getStatusCode().is2xxSuccess() ? "connected" : "error"); } catch (Exception e) { health.put("postgrest", "disconnected"); } return ResponseEntity.ok(health); } }

Security Configuration

// SecurityConfig.java package com.example.postgrestsecurity.config; import com.example.postgrestsecurity.service.AuthenticationService; import lombok.extern.slf4j.Slf4j; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.security.config.annotation.web.builders.HttpSecurity; import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity; import org.springframework.security.config.http.SessionCreationPolicy; import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder; import org.springframework.security.crypto.password.PasswordEncoder; import org.springframework.security.web.SecurityFilterChain; import org.springframework.web.cors.CorsConfiguration; import org.springframework.web.cors.CorsConfigurationSource; import org.springframework.web.cors.UrlBasedCorsConfigurationSource; import java.util.Arrays; import java.util.List; @Slf4j @Configuration @EnableWebSecurity public class SecurityConfig { @Bean public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception { http .cors(cors -> cors.configurationSource(corsConfigurationSource())) .csrf(csrf -> csrf.disable()) .sessionManagement(session -> session .sessionCreationPolicy(SessionCreationPolicy.STATELESS) ) .authorizeHttpRequests(authz -> authz // Public endpoints .requestMatchers("/api/auth/**", "/api/proxy/health", "/actuator/health").permitAll() // Secure proxy endpoints .requestMatchers("/api/proxy/**").authenticated() // All other endpoints require authentication .anyRequest().authenticated() ) .exceptionHandling(exceptions -> exceptions .authenticationEntryPoint((request, response, authException) -> { log.warn("Authentication failed: {}", authException.getMessage()); response.setStatus(401); response.setContentType("application/json"); response.getWriter().write("{\"error\": \"Authentication required\"}"); }) .accessDeniedHandler((request, response, accessDeniedException) -> { log.warn("Access denied: {}", accessDeniedException.getMessage()); response.setStatus(403); response.setContentType("application/json"); response.getWriter().write("{\"error\": \"Access denied\"}"); }) ); return http.build(); } @Bean public CorsConfigurationSource corsConfigurationSource() { CorsConfiguration configuration = new CorsConfiguration(); configuration.setAllowedOriginPatterns(List.of("*")); configuration.setAllowedMethods(Arrays.asList("GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS")); configuration.setAllowedHeaders(Arrays.asList("authorization", "content-type", "x-requested-with", "prefer", "accept", "accept-profile")); configuration.setExposedHeaders(Arrays.asList("content-range", "location")); configuration.setAllowCredentials(true); configuration.setMaxAge(3600L); UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource(); source.registerCorsConfiguration("/**", configuration); return source; } @Bean public PasswordEncoder passwordEncoder() { return new BCryptPasswordEncoder(); } }

Authentication Controller

// AuthController.java package com.example.postgrestsecurity.controller; import com.example.postgrestsecurity.model.AuthRequest; import com.example.postgrestsecurity.model.AuthResponse; import com.example.postgrestsecurity.service.AuthenticationService; import lombok.extern.slf4j.Slf4j; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.*; import jakarta.validation.Valid; import java.util.Map; @Slf4j @RestController @RequestMapping("/api/auth") public class AuthController { private final AuthenticationService authenticationService; public AuthController(AuthenticationService authenticationService) { this.authenticationService = authenticationService; } @PostMapping("/login") public ResponseEntity<AuthResponse> login(@Valid @RequestBody AuthRequest authRequest) { log.info("Login attempt for user: {}", authRequest.getUsername()); AuthResponse response = authenticationService.authenticate(authRequest); if (response.isSuccess()) { return ResponseEntity.ok(response); } else { return ResponseEntity.status(401).body(response); } } @PostMapping("/validate") public ResponseEntity<Map<String, Object>> validateToken(@RequestHeader("Authorization") String authHeader) { if (authHeader == null || !authHeader.startsWith("Bearer ")) { return ResponseEntity.badRequest().body(Map.of("valid", false, "error", "Invalid authorization header")); } String token = authHeader.substring(7); boolean isValid = authenticationService.validateToken(token); return ResponseEntity.ok(Map.of("valid", isValid)); } @PostMapping("/refresh") public ResponseEntity<Map<String, Object>> refreshToken(@RequestHeader("Authorization") String authHeader) { // Implementation for token refresh return ResponseEntity.status(501).body(Map.of("error", "Not implemented")); } @PostMapping("/logout") public ResponseEntity<Map<String, Object>> logout(@RequestHeader("Authorization") String authHeader) { // In a stateless JWT system, logout is handled client-side // You might want to maintain a blacklist for logged out tokens log.info("Logout requested"); return ResponseEntity.ok(Map.of("message", "Logout successful")); } }

Testing

1. Unit Tests
// AuthenticationServiceTest.java package com.example.postgrestsecurity.service; import com.example.postgrestsecurity.entity.User; import com.example.postgrestsecurity.model.AuthRequest; import com.example.postgrestsecurity.model.AuthResponse; import com.example.postgrestsecurity.repository.UserRepository; import org.junit.jupiter.api.Test; import org.junit.jupiter.api.extension.ExtendWith; import org.mockito.InjectMocks; import org.mockito.Mock; import org.mockito.junit.jupiter.MockitoExtension; import org.springframework.security.crypto.password.PasswordEncoder; import java.util.Optional; import java.util.Set; import static org.junit.jupiter.api.Assertions.*; import static org.mockito.ArgumentMatchers.any; import static org.mockito.Mockito.*; @ExtendWith(MockitoExtension.class) class AuthenticationServiceTest { @Mock private UserRepository userRepository; @Mock private JwtTokenService jwtTokenService; @Mock private PasswordEncoder passwordEncoder; @InjectMocks private AuthenticationService authenticationService; @Test void testAuthenticate_Success() { // Setup AuthRequest authRequest = new AuthRequest(); authRequest.setUsername("testuser"); authRequest.setPassword("password"); User user = new User(); user.setId(1L); user.setUsername("testuser"); user.setEmail("[email protected]"); user.setIsActive(true); user.setPasswordHash("hashedpassword"); when(userRepository.findByUsername("testuser")).thenReturn(Optional.of(user)); when(passwordEncoder.matches("password", "hashedpassword")).thenReturn(true); when(jwtTokenService.generatePostgRESTToken(any(User.class))).thenReturn("test.jwt.token"); // Execute AuthResponse response = authenticationService.authenticate(authRequest); // Verify assertTrue(response.isSuccess()); assertEquals("test.jwt.token", response.getAccessToken()); verify(userRepository).save(user); // Verify last login update } @Test void testAuthenticate_InvalidCredentials() { // Setup AuthRequest authRequest = new AuthRequest(); authRequest.setUsername("testuser"); authRequest.setPassword("wrongpassword"); User user = new User(); user.setUsername("testuser"); user.setPasswordHash("hashedpassword"); user.setIsActive(true); when(userRepository.findByUsername("testuser")).thenReturn(Optional.of(user)); when(passwordEncoder.matches("wrongpassword", "hashedpassword")).thenReturn(false); // Execute AuthResponse response = authenticationService.authenticate(authRequest); // Verify assertFalse(response.isSuccess()); assertEquals("Invalid credentials", response.getMessage()); } } // JwtTokenServiceTest.java package com.example.postgrestsecurity.service; import com.example.postgrestsecurity.entity.User; import org.junit.jupiter.api.Test; import org.junit.jupiter.api.extension.ExtendWith; import org.mockito.junit.jupiter.MockitoExtension; import java.util.HashSet; import static org.junit.jupiter.api.Assertions.*; @ExtendWith(MockitoExtension.class) class JwtTokenServiceTest { private final JwtTokenService jwtTokenService = new JwtTokenService( "test-secret-key-that-is-long-enough-for-hs256-32-chars", 3600, "test-issuer" ); @Test void testGenerateAndValidateToken() { // Setup User user = new User(); user.setId(1L); user.setUsername("testuser"); user.setEmail("[email protected]"); user.setTenantIds(new HashSet<>()); // Execute var token = jwtTokenService.generateToken(user); var claims = jwtTokenService.validateToken(token.getToken()); // Verify assertNotNull(token); assertNotNull(claims); assertEquals("1", claims.getSubject()); assertEquals("testuser", claims.getUsername()); } }
2. Integration Test
// PostgRESTSecurityIntegrationTest.java package com.example.postgrestsecurity.integration; import com.example.postgrestsecurity.controller.AuthController; import com.example.postgrestsecurity.model.AuthRequest; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.ActiveProfiles; import static org.junit.jupiter.api.Assertions.*; @SpringBootTest @ActiveProfiles("test") class PostgRESTSecurityIntegrationTest { @Autowired private AuthController authController; @Test void testAuthenticationWorkflow() { // Setup AuthRequest authRequest = new AuthRequest(); authRequest.setUsername("testuser"); authRequest.setPassword("testpassword"); // Execute var response = authController.login(authRequest); // Verify assertTrue(response.getStatusCode().is2xxSuccessful()); // Add more assertions based on your test data } }

Production Considerations

1. Database Configuration
-- Production database setup -- Enable required extensions CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- Set secure parameters ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; ALTER SYSTEM SET max_connections = 200; ALTER SYSTEM SET shared_buffers = '1GB'; ALTER SYSTEM SET work_mem = '16MB'; -- Security parameters ALTER SYSTEM SET password_encryption = 'scram-sha-256'; ALTER SYSTEM SET ssl = on;
2. Monitoring and Logging
// SecurityMonitoringService.java package com.example.postgrestsecurity.service; import lombok.extern.slf4j.Slf4j; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Service; import java.util.concurrent.atomic.AtomicLong; @Slf4j @Service public class SecurityMonitoringService { private final AtomicLong totalRequests = new AtomicLong(0); private final AtomicLong failedAuthAttempts = new AtomicLong(0); private final AtomicLong blockedRequests = new AtomicLong(0); public void recordRequest() { totalRequests.incrementAndGet(); } public void recordFailedAuth() { failedAuthAttempts.incrementAndGet(); } public void recordBlockedRequest() { blockedRequests.incrementAndGet(); } @Scheduled(fixedRate = 60000) // Every minute public void logSecurityMetrics() { log.info("Security Metrics - Total: {}, Failed Auth: {}, Blocked: {}", totalRequests.get(), failedAuthAttempts.get(), blockedRequests.get()); // Reset counters totalRequests.set(0); failedAuthAttempts.set(0); blockedRequests.set(0); } }

Best Practices

  1. Security:
  • Use strong JWT secrets (minimum 32 characters)
  • Implement proper token expiration and refresh
  • Enable SSL/TLS for all communications
  • Regular security audits and penetration testing
  1. Performance:
  • Implement connection pooling for database
  • Use caching for frequently accessed data
  • Monitor and optimize RLS policies
  • Implement rate limiting appropriately
  1. Monitoring:
  • Log all authentication attempts
  • Monitor RLS policy performance
  • Track API usage patterns
  • Set up alerts for security events
  1. Database:
  • Regular backups with point-in-time recovery
  • Database replication for high availability
  • Regular vacuum and analyze operations
  • Monitor long-running queries

Conclusion

This PostgREST security implementation provides:

  • Comprehensive authentication with JWT tokens
  • Fine-grained authorization with Row Level Security
  • API gateway security with rate limiting and IP whitelisting
  • Multi-tenant support with tenant isolation
  • Production-ready features including monitoring and logging

The solution can be extended with:

  • OAuth2/OIDC integration
  • Advanced rate limiting strategies
  • Real-time security monitoring
  • Automated security policy management
  • Integration with enterprise identity providers
Category : JAVA

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper