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:
- API Gateway Security - Rate limiting, IP whitelisting, SSL termination
- Authentication - JWT tokens, API keys, OAuth2
- Authorization - Row Level Security (RLS), role-based access
- 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
- 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
- Performance:
- Implement connection pooling for database
- Use caching for frequently accessed data
- Monitor and optimize RLS policies
- Implement rate limiting appropriately
- Monitoring:
- Log all authentication attempts
- Monitor RLS policy performance
- Track API usage patterns
- Set up alerts for security events
- 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