Perbaikan grant PostGIS

Cabut hak dari PUBLIC dan anon, kunci search_path.
Berkas: alat/hari-3/Praktik 8/sql/01_perbaikan-grant-postgis.sql
-- =====================================================================
-- Praktik 8 - perbaikan hak akses PostGIS di Supabase
--
-- Modul Praktik 8 meminta menjalankan blok SQL berikut di SQL Editor Supabase:
--
--   GRANT EXECUTE ON FUNCTION public.addgeometrycolumn TO PUBLIC, postgres, anon, authenticated, service_role;
--
-- Baris itu memberi hak eksekusi ke PUBLIC, artinya SETIAP peran di database
-- bisa memanggil fungsi tersebut. Digabung dengan User Defined Function yang
-- ditulis tanpa SECURITY DEFINER, seorang pengguna anon (kunci anon Supabase
-- memang dirancang untuk dipakai di browser) bisa memanggil addgeometrycolumn
-- dan mengubah struktur tabel.
--
-- Berkas ini menggantikan blok tersebut dengan versi yang aman.
-- Jalankan di SQL Editor Supabase (butuh peran postgres / owner).
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. Fungsi wrapper: SECURITY INVOKER + search_path terkunci
--
-- EXISTS check membuat berkas ini aman dijalankan berulang kali.
-- ---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.addgeometrycolumn(
    catalog_name character varying,
    schema_name  character varying,
    table_name   character varying,
    column_name  character varying,
    new_srid     integer,
    new_type     character varying,
    new_dim      integer
) RETURNS text
LANGUAGE plpgsql
-- SECURITY INVOKER (bawaan) dipilih secara sadar: pemanggil hanya boleh
-- melakukan apa yang memang boleh dia lakukan. Jangan diubah jadi DEFINER
-- kecuali benar-benar paham konsekuensinya.
SECURITY INVOKER
-- search_path dikunci supaya nama fungsi tidak bisa dibajak lewat schema lain.
SET search_path = extensions, public, pg_temp
AS $$
BEGIN
    RETURN extensions.AddGeometryColumn(schema_name, table_name, column_name, new_srid, new_type, new_dim);
EXCEPTION WHEN OTHERS THEN
    RETURN public.AddGeometryColumn(schema_name, table_name, column_name, new_srid, new_type, new_dim);
END;
$$;

CREATE OR REPLACE FUNCTION gis.addgeometrycolumn(
    catalog_name character varying,
    schema_name  character varying,
    table_name   character varying,
    column_name  character varying,
    new_srid     integer,
    new_type     character varying,
    new_dim      integer
) RETURNS text
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = extensions, public, gis, pg_temp
AS $$
BEGIN
    RETURN extensions.AddGeometryColumn(schema_name, table_name, column_name, new_srid, new_type, new_dim);
EXCEPTION WHEN OTHERS THEN
    RETURN public.AddGeometryColumn(schema_name, table_name, column_name, new_srid, new_type, new_dim);
END;
$$;

-- ---------------------------------------------------------------------
-- 2. Cabut hak yang terlalu luas
-- ---------------------------------------------------------------------
REVOKE ALL ON FUNCTION public.addgeometrycolumn(
    character varying, character varying, character varying,
    character varying, integer, character varying, integer) FROM PUBLIC;
REVOKE ALL ON FUNCTION gis.addgeometrycolumn(
    character varying, character varying, character varying,
    character varying, integer, character varying, integer) FROM PUBLIC;

REVOKE ALL ON FUNCTION public.addgeometrycolumn(
    character varying, character varying, character varying,
    character varying, integer, character varying, integer) FROM anon, authenticated;
REVOKE ALL ON FUNCTION gis.addgeometrycolumn(
    character varying, character varying, character varying,
    character varying, integer, character varying, integer) FROM anon, authenticated;

-- ---------------------------------------------------------------------
-- 3. Berikan hak hanya kepada yang memang membutuhkannya
--
-- QGIS terhubung memakai peran `postgres`, jadi peran itu saja cukup.
-- Kalau nanti ada peran khusus untuk QGIS, tambahkan namanya di sini dan
-- jangan pakai PUBLIC.
-- ---------------------------------------------------------------------
GRANT EXECUTE ON FUNCTION public.addgeometrycolumn(
    character varying, character varying, character varying,
    character varying, integer, character varying, integer) TO postgres;
GRANT EXECUTE ON FUNCTION gis.addgeometrycolumn(
    character varying, character varying, character varying,
    character varying, integer, character varying, integer) TO postgres;

-- ---------------------------------------------------------------------
-- 4. Cek juga tabel spatial_ref_sys
--
-- PostGIS memasang tabel ini dan Supabase terkadang membuka hak tulisnya.
-- Sebaiknya baca-saja untuk semua peran aplikasi.
-- ---------------------------------------------------------------------
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'public' AND table_name = 'spatial_ref_sys') THEN
        EXECUTE 'REVOKE INSERT, UPDATE, DELETE ON public.spatial_ref_sys FROM anon, authenticated';
        EXECUTE 'GRANT SELECT ON public.spatial_ref_sys TO anon, authenticated';
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'gis' AND table_name = 'spatial_ref_sys') THEN
        EXECUTE 'REVOKE INSERT, UPDATE, DELETE ON gis.spatial_ref_sys FROM anon, authenticated';
        EXECUTE 'GRANT SELECT ON gis.spatial_ref_sys TO anon, authenticated';
    END IF;
END $$;

-- ---------------------------------------------------------------------
-- 5. Verifikasi
-- ---------------------------------------------------------------------
-- Hasil yang diharapkan: hanya postgres yang muncul pada kolom grantee.
SELECT routine_schema AS schema,
       routine_name   AS fungsi,
       grantee,
       privilege_type
FROM information_schema.routine_privileges
WHERE routine_name = 'addgeometrycolumn'
ORDER BY routine_schema, grantee;

-- Harapan: prosecdef = false untuk kedua fungsi (SECURITY INVOKER).
SELECT n.nspname AS schema,
       p.proname AS fungsi,
       p.prosecdef AS security_definer,
       p.proconfig AS search_path_terkunci
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'addgeometrycolumn'
ORDER BY n.nspname;