Introduction
— , . , best practice . , PostgreSQL, , :
PostgreSQL , SSH psql. , PostgreSQL .
, , — , , , . , PostgreSQL TCP- 5432. . Linux iptables
- :
# Make sure not to drop established connections.
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
# Allow SSH.
iptables -A INPUT -p tcp -m state --state NEW --dport 22 -j ACCEPT
# Allow PostgreSQL.
iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -j ACCEPT
# Allow all outbound, drop everything else inbound.
iptables -A OUTPUT -j ACCEPT
iptables -A INPUT -j DROP
iptables -A FORWARD -j DROP
. iptables iptables-apply, , .
PostgreSQL 5432. , IP- :
# Only allow access to PostgreSQL port from the local subnet.
iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -s 192.168.1.0/24 -j ACCEPT
, 5432 , (-) PostgreSQL.
« ». . , , PostgreSQL:
ssh -f -N -T -R 5432:localhost:5432 user@<client-host>
, <client-host>
PostgreSQL SSH-. 5432 5432 , :
psql "host=localhost port=5432 user=postgres dbname=postgres"
, , listen_addresses
. , PostgreSQL, , , , , :
listen_addresses = 'localhost, 192.168.0.1'
, , (, , Kubernetes PostgreSQL, ), TCP . Unix:
listen_addresses = ''
HTTPS, , . PostgreSQL TLS ( - SSL , CLI legacy-) , .
TLS
, . Let's Encrypt X.509, , CLI certbot:
certbot certonly --standalone -d postgres.example.com
, certbot HTTP-01 ACME , DNS , , 80, .
- Let's Encrypt , openssl CLI:
# Make a self-signed server CA.
openssl req -sha256 -new -x509 -days 365 -nodes \
-out server-ca.crt \
-keyout server-ca.key
# Generate server CSR. Put the hostname you will be using to connect to
# the database in the CN field.
openssl req -sha256 -new -nodes \
-subj "/CN=postgres.example.com" \
-out server.csr \
-keyout server.key
# Sign a server certificate.
openssl x509 -req -sha256 -days 365 \
-in server.csr \
-CA server-ca.crt \
-CAkey server-ca.key \
-CAcreateserial \
-out server.crt
, , .
TLS
, , X.509, , (CA).
, CA :
# Make a self-signed client CA.
openssl req -sha256 -new -x509 -days 365 -nodes \
-out client-ca.crt \
-keyout client-ca.key
# Generate client CSR. CN must contain the name of the database role you
# will be using to connect to the database.
openssl req -sha256 -new -nodes \
-subj "/CN=alice" \
-out client.csr \
-keyout server.key
# Sign a client certificate.
openssl x509 -req -sha256 -days 365 \
-in client.csr \
-CA client-ca.crt \
-CAkey client-ca.key \
-CAcreateserial \
-out client.crt
, CommonName (CN) , . PostgreSQL .
TLS
, PostgreSQL TLS:
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/client-ca.crt'
# This setting is on by default but it’s always a good idea to
# be explicit when it comes to security.
ssl_prefer_server_ciphers = on
# TLS 1.3 will give the strongest security and is advised when
# controlling both server and clients.
ssl_min_protocol_version = 'TLSv1.3'
— host-based PostgreSQL (pg_hba.conf
), TLS X.509:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all ::/0 cert
hostssl all all 0.0.0.0/0 cert
, , , CA:
psql "host=postgres.example.com \
user=alice \
dbname=postgres \
sslmode=verify-full \
sslrootcert=/path/to/server-ca.crt \
sslcert=/path/to/client.crt \
sslkey=/path/to/client.key"
, psql , sslmode verify-full
verify-ca
, , PostgreSQL, , CN X.509.
TLS , , PostgreSQL. «», «».
~/.pg_service.conf
:
[example] host=postgres.example.com user=alice sslmode=verify-full sslrootcert=/path/to/server-ca.crt sslcert=/path/to/client.crt sslkey=/path/to/client.key
, :
psql "service=example dbname=postgres"
, PostgreSQL , , TLS. — , . .
PostgreSQL , . PostgreSQL (8.1 ) «» «», , , , psql (, «user = alice»), LOGIN
, . , SQL :
CREATE USER alice;
CREATE ROLE alice LOGIN;
, , (SUPERUSER
), (CREATEDB
), (CREATEROLE
) .
, , : . , .
:
CREATE TABLE server_inventory (
id int PRIMARY KEY,
description text,
ip_address text,
environment text,
owner text,
);
, PostgreSQL ( «postgres»), . «root» Linux, . , .
, / , « » ( ) . , , , , :
-- Create a group role that doesn't have ability to login by itself and
-- grant it SELECT privileged on the server inventory table.
CREATE ROLE developer;
GRANT SELECT ON server_inventory TO developer;
-- Create two user accounts which will inherit "developer" permissions upon
-- logging into the database.
CREATE ROLE alice LOGIN INHERIT;
CREATE ROLE bob LOGIN INHERIT;
-- Assign both user account to the "developer" group role.
GRANT developer TO alice, bob;
, «» .
SELECT
, . , , , IP-:
CREATE ROLE intern;
GRANT SELECT(id, description) ON server_inventory TO intern;
CREATE ROLE charlie LOGIN INHERIT;
GRANT intern TO charlie;
— INSERT
, UPDATE
, DELETE
TRUNCATE
SQL . , , . Privileges PostgreSQL, .
PostgreSQL , . , SELECT
, , INSERT
, UPDATE
DELETE
.
, : , .
, , . RLS :
ALTER TABLE server_inventory ENABLE ROW LEVEL SECURITY;
- PostgreSQL «», , ( , , ) .
— , PostgreSQL , . , SELECT
, , USING, , INSERT
, UPDATE
DELETE
, WITH CHECK
.
, , , «»:
CREATE POLICY select_all_servers
ON server_inventory FOR SELECT
USING (true);
CREATE POLICY update_own_servers
ON server_inventory FOR UPDATE
USING (current_user = owner)
WITH CHECK (current_user = owner);
, .
. — defence in depth, , , .
— , . , , , PostgreSQL.
, , , — . , SQL:
; Log successful and unsuccessful connection attempts. log_connections = on ; Log terminated sessions. log_disconnections = on ; Log all executed SQL statements. log_statement = all
, , self-hosted PostgreSQL . , , , «grepping».
PostgreSQL , pgAudit . self-hosted PostgreSQL, . , AWS RDS, « », .
pgAudit . , - , , SIEM .
, , .
best practices PostgreSQL , , , PostgreSQL.