Hardening Network Access, Extensions, SECURITY DEFINER, Search Path, Logging, and Patching

Learning outcomes

Security controls fail when they are treated as an installation checklist instead of a continuously verified system. This lesson converts Chapters 18–20 into a hardening baseline: minimize network reachability, require strong authentication and verified transport, eliminate ambient object-creation/search-path hazards, govern server-side code/extensions, capture useful security logs without leaking secrets, and stay on supported patched releases.

1. Start with exposure: listen_addresses + firewall + HBA

listen_addresses controls which local interfaces PostgreSQL binds. It is not a firewall and does not authenticate clients. Host firewalls/security groups decide which remote packets reach the port; HBA decides how matching PostgreSQL connection attempts authenticate. All three layers should agree.

SELECT name, setting, context, source, pending_restartFROM pg_settingsWHERE name IN (  'listen_addresses',  'port',  'ssl',  'password_encryption',  'hba_file')ORDER BY name;SELECT rule_number, type, database, user_name,       address, auth_method, options, errorFROM pg_hba_file_rulesORDER BY rule_number;

A secure baseline is not “set listen_addresses='*' and rely on HBA.” Bind only required interfaces when practical, filter source networks outside PostgreSQL, then use specific HBA records with strong authentication.

2. Validate password/TLS policy—not only server capability

SHOW password_encryption;SHOW ssl;SELECT rolname,       CASE         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM'         WHEN rolpassword LIKE 'md5%' THEN 'MD5-legacy'         WHEN rolpassword IS NULL THEN 'no-password'         ELSE 'other'       END AS verifier_kindFROM pg_authidWHERE rolcanloginORDER BY rolname;

This admin-only verifier classification identifies legacy password storage without printing verifiers. Combine it with HBA review and real client tests using TLS verify-full. A server with ssl=on can still accept unencrypted host connections unless HBA/network policy prevents them.

3. Remove writable schemas from untrusted search paths

Function/operator/type resolution follows search_path. If an attacker can create an object in a schema searched before a trusted object, another role can accidentally invoke the attacker's object. PostgreSQL's security guidance recommends removing untrusted-writable schemas from sensitive paths.

SELECT n.nspname,       EXISTS (         SELECT 1         FROM aclexplode(           COALESCE(n.nspacl, acldefault('n', n.nspowner))         ) AS x         WHERE x.grantee = 0           AND x.privilege_type = 'CREATE'       ) AS public_has_create,       EXISTS (         SELECT 1         FROM aclexplode(           COALESCE(n.nspacl, acldefault('n', n.nspowner))         ) AS x         WHERE x.grantee = 0           AND x.privilege_type = 'USAGE'       ) AS public_has_usageFROM pg_namespace AS nWHERE n.nspname = 'public';SHOW search_path;SELECT rolname, rolconfigFROM pg_rolesWHERE rolconfig IS NOT NULLORDER BY rolname;

BEGIN;REVOKE CREATE ON SCHEMA public FROM PUBLIC;SELECT n.nspname,       EXISTS (         SELECT 1         FROM aclexplode(           COALESCE(n.nspacl, acldefault('n', n.nspowner))         ) AS x         WHERE x.grantee = 0           AND x.privilege_type = 'CREATE'       ) AS public_has_create_afterFROM pg_namespace AS nWHERE n.nspname = 'public';ROLLBACK;

Do not run the revoke blindly on a legacy application that intentionally creates shared objects there. Inventory dependencies first. The security goal is that untrusted roles cannot create objects in schemas implicitly searched by privileged code.

4. Harden SECURITY DEFINER like privileged application code

A SECURITY DEFINER function runs with its owner privileges. It must use trusted, schema-qualified objects; constrain search_path; avoid dynamic-SQL injection; and revoke PostgreSQL's default PUBLIC EXECUTE before exposing it to the intended callers.

BEGIN;SET ROLE servicehub_owner;CREATE OR REPLACE FUNCTION app.ch20_my_open_ticket_count()RETURNS bigintLANGUAGE sqlSTABLESECURITY DEFINERSET search_path = pg_catalog, app, pg_tempAS $$  SELECT count(*)  FROM app.ch20_ticket  WHERE tenant_role = session_user    AND status = 'open'$$;REVOKE ALLON FUNCTION app.ch20_my_open_ticket_count()FROM PUBLIC;GRANT EXECUTEON FUNCTION app.ch20_my_open_ticket_count()TO ch20_tenant_a, ch20_tenant_b;RESET ROLE;COMMIT;

This example intentionally uses session_user: the authenticated connection identity is not replaced by the function owner's current_user. Its tenant check is therefore valid for direct tenant-login sessions. The chapter's SET ROLE simulations keep the administrator as session_user, so do not use those simulations to test this function's tenant result. A shared-login pooler or impersonation design must use a different non-forgeable identity contract and test it explicitly.

SELECT p.oid::regprocedure AS routine,       pg_get_userbyid(p.proowner) AS owner,       p.prosecdef,       p.proconfig,       has_function_privilege(         'ch20_tenant_a',         p.oid,         'EXECUTE'       ) AS tenant_a_executeFROM pg_proc AS pWHERE p.oid = 'app.ch20_my_open_ticket_count()'::regprocedure;

5. Extension and language inventory belongs in security review

SELECT e.extname, e.extversion,       n.nspname AS schema_name,       pg_get_userbyid(e.extowner) AS ownerFROM pg_extension AS eJOIN pg_namespace AS n ON n.oid = e.extnamespaceORDER BY e.extname;SELECT lanname, lanpltrusted,       pg_get_userbyid(lanowner) AS ownerFROM pg_languageORDER BY lanname;

Chapter 19's rule carries forward: an extension or untrusted language is server-code/package/runtime surface. Remove unused components, review provenance/version compatibility, and install the same dependencies on failover/restore targets. Do not grant superuser simply to satisfy an extension setup wizard.

6. Logging: collect security evidence without logging every secret

PostgreSQL 18 makes connection logging more granular. Failed authentication is logged regardless; log_connections can log receipt, authentication identity, authorization, and setup durations. Statement logging is more sensitive because SQL text can contain credentials, tokens, personal data, or application secrets.

SELECT name, setting, context, sourceFROM pg_settingsWHERE name IN (  'log_destination',  'logging_collector',  'log_connections',  'log_disconnections',  'log_line_prefix',  'log_error_verbosity',  'log_statement',  'log_min_duration_statement',  'log_min_duration_sample')ORDER BY name;

log_connections = 'authentication,authorization'log_disconnections = onlog_line_prefix = '%m [%p] %q%u@%d/%a '# Do not blindly set log_statement = 'all' in a secret-rich workload.

Choose log retention/access controls as carefully as database permissions. Logs are operational data that can themselves become sensitive.

7. Secrets belong outside SQL and source control

Use interactive password changes, secret managers, OS-protected service files, or equivalent deployment mechanisms. For libpq password files on Unix, permissions must prevent group/world access. Rotate credentials after exposure and make revocation part of incident response.

# Avoid:# psql "postgresql://user:RealSecret@example/db"# Prefer a protected service/password mechanism or secret injection:psql "service=servicehub-prod application_name=servicehub-api" 

A shell process list, CI log, crash report, SQL history, or Git diff is not an acceptable password vault.

8. OS account isolation and file permissions are database security

PostgreSQL server files are protected by the operating-system account running the server. A user that can replace database binaries, shared libraries, configuration, TLS private keys, or read unrestricted data files can bypass many SQL controls. Limit login/shell access to the PostgreSQL service account and secure backups, WAL archives, certificate keys, and extension libraries.

SHOW data_directory;SHOW config_file;SHOW hba_file;SHOW ident_file;SHOW shared_preload_libraries;SHOW local_preload_libraries;SHOW session_preload_libraries;

9. Patch discipline: minor updates are part of the security boundary

PostgreSQL supports each major for a limited lifecycle and recommends running the current minor release for that major. Minor releases contain bug and security fixes and normally do not require dump/reload, though their release notes can contain required follow-up actions. Before a production patch, verify the current official versioning/security pages, test the package on staging/replicas, read release notes, and preserve rollback/recovery capability.

SELECT version();SELECT current_setting('server_version') AS server_version,       current_setting('server_version_num') AS server_version_num;

psql --versionpg_config --version

Server, psql/libpq, extensions, OS packages, and drivers can have different versions. “We run PostgreSQL 18” is not enough for a vulnerability or compatibility assessment.

10. One evidence-driven hardening query

SELECT now() AS observed_at,       current_user,       current_setting('server_version') AS server_version,       current_setting('listen_addresses') AS listen_addresses,       current_setting('ssl') AS ssl_enabled,       current_setting('password_encryption') AS password_encryption,       current_setting('log_connections') AS log_connections,       current_setting('log_disconnections') AS log_disconnections;SELECT count(*) FILTER (WHERE error IS NOT NULL) AS hba_parse_errorsFROM pg_hba_file_rules;SELECT count(*) AS superuser_login_rolesFROM pg_rolesWHERE rolsuper AND rolcanlogin;SELECT count(*) AS bypassrls_login_rolesFROM pg_rolesWHERE rolbypassrls AND rolcanlogin;

This is not a compliance score. It is a timestamped starting snapshot for human review. Firewall rules, certificates, secrets, package CVEs, file permissions, backups, and application authorization live outside these catalog rows and must be reviewed separately.

11. Cleanup the disposable Chapter 20 database objects

DROP VIEW IF EXISTS app.ch20_open_ticket;DROP FUNCTION IF EXISTS app.ch20_my_open_ticket_count();DROP TABLE IF EXISTS app.ch20_ticket CASCADE;DROP SCHEMA IF EXISTS ch20_app CASCADE;DROP ROLE IF EXISTS ch20_tenant_a;DROP ROLE IF EXISTS ch20_tenant_b;DROP ROLE IF EXISTS ch20_api_login;DROP ROLE IF EXISTS ch20_report_login;DROP ROLE IF EXISTS ch20_deploy_login;DROP ROLE IF EXISTS ch20_reader;DROP ROLE IF EXISTS ch20_writer;DROP ROLE IF EXISTS ch20_schema_owner;

HBA/TLS files and any passwords created in Lessons 1–2 must be removed or restored separately according to the lab topology. Do not delete production authentication records as part of a generic SQL cleanup script.

12. Checkpoint

Authoritative references

Authentication, TLS, authorization, and policy behavior is security- and version-sensitive. The lesson uses these PostgreSQL 18 primary sources.

• PostgreSQL 18 — Function Security
• PostgreSQL 18 — Error Reporting and Logging
• PostgreSQL — Versioning Policy
• PostgreSQL — Security Information
• PostgreSQL 18 — Server Setup and Operation