lekss361
94cf199276
Some checks failed
Deploy Trade-In / changes (push) Successful in 5s
Deploy / changes (push) Successful in 5s
Deploy Trade-In / build-frontend (push) Successful in 26s
Deploy Trade-In / build-backend (push) Successful in 47s
Deploy / build-frontend (push) Successful in 29s
Deploy / build-backend (push) Successful in 1m24s
Deploy Trade-In / deploy (push) Successful in 40s
Deploy / build-worker (push) Successful in 2m57s
Deploy / deploy (push) Failing after 37s
Replaces tradein.cad_buildings snapshot with live postgres_fdw foreign table reading gendesign.v_tradein_cad_buildings. Fixes /trade-in/api/v1/geocode/reverse 500 (Nominatim ban) and address_not_geocoded for cadastre addresses (e.g. Хохрякова 81).
Security (deep-review fixes):
- 100_tradein_fdw_role.sql: passwordless CREATE ROLE; password set by deploy.yml ALTER ROLE bootstrap reading GENDESIGN_FDW_PASSWORD from backend/.env.runtime (via psql :'pw' var → format %L — injection-safe).
- core/fdw.py: regex whitelist [A-Za-z0-9_-]{32,256} on password, ValueError without echoing value, try/rollback on commit.
- 060_postgres_fdw_extension.sql: connect_timeout='3' on FOREIGN SERVER + ALTER ADD/SET fallback.
- geocoder.py: _cadastral_forward_sync / _cadastral_reverse_sync wrapped in asyncio.to_thread.
- 100_*.sql: REVOKE ALL ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA public; only GRANT SELECT on v_tradein_cad_buildings.
- pg_user_mappings query handles PUBLIC mapping (usename IS NULL).
Tests: 3 SQL-injection guards on ensure_fdw_user_mapping + rewritten cadastral suite.
45 lines
1.8 KiB
PL/PgSQL
45 lines
1.8 KiB
PL/PgSQL
-- tradein_fdw_reader role + v_tradein_cad_buildings view
|
|
-- Used by tradein-postgres via postgres_fdw to read live cad_buildings.
|
|
-- Password is set by .forgejo/workflows/deploy.yml ALTER ROLE bootstrap step
|
|
-- reading GENDESIGN_FDW_PASSWORD from backend/.env.runtime — never embed password in SQL.
|
|
BEGIN;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tradein_fdw_reader') THEN
|
|
CREATE ROLE tradein_fdw_reader LOGIN;
|
|
END IF;
|
|
END$$;
|
|
|
|
COMMENT ON ROLE tradein_fdw_reader IS
|
|
'FDW reader from tradein-postgres. Password set by .forgejo/workflows/deploy.yml from env GENDESIGN_FDW_PASSWORD post-migration step. Never embed password in SQL migrations.';
|
|
|
|
-- Defense-in-depth: explicit REVOKE perimeter (any existing PUBLIC grants ignored).
|
|
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM tradein_fdw_reader;
|
|
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM tradein_fdw_reader;
|
|
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM tradein_fdw_reader;
|
|
|
|
GRANT CONNECT ON DATABASE gendesign TO tradein_fdw_reader;
|
|
GRANT USAGE ON SCHEMA public TO tradein_fdw_reader;
|
|
|
|
CREATE OR REPLACE VIEW v_tradein_cad_buildings AS
|
|
SELECT
|
|
cad_num,
|
|
NULLIF(trim(readable_address), '') AS readable_address,
|
|
NULLIF(floors, 0) AS floors,
|
|
CASE WHEN year_built BETWEEN 1800 AND 2100 THEN year_built END AS year_built,
|
|
round(COALESCE(build_record_area, area), 2)::numeric AS area_m2,
|
|
purpose,
|
|
ST_Y(ST_Centroid(geom))::double precision AS lat,
|
|
ST_X(ST_Centroid(geom))::double precision AS lon
|
|
FROM cad_buildings
|
|
WHERE cad_num LIKE '66:41:%'
|
|
AND geom IS NOT NULL
|
|
AND readable_address IS NOT NULL;
|
|
|
|
GRANT SELECT ON v_tradein_cad_buildings TO tradein_fdw_reader;
|
|
|
|
COMMENT ON VIEW v_tradein_cad_buildings IS
|
|
'FDW source for tradein-mvp (postgres_fdw). Flat 8-col EKB-only slice of cad_buildings. tradein.gendesign_cad_buildings maps here.';
|
|
|
|
COMMIT;
|