PostgreSQL "permission denied for schema public": kāpēc tā notiek un kā to novērst

PostgreSQL kļūda "permission denied for schema public" parasti parādās pirmajā reizē, kad lietotne, migrāciju rīks vai izstrādātājs mēģina izveidot tabulu jaunā PostgreSQL 15+ datubāzē. Kopš 15. versijas parastie lietotāji pēc noklusējuma vairs nevar veidot objektus shēmā public. Labojums aizņem vienu komandu, taču tas, kuru komandu izvēlaties, nosaka, cik droša datubāze paliek.

appdb=> CREATE TABLE orders (id bigint PRIMARY KEY);
ERROR:  permission denied for schema public

Ziņojums nāk no tiesību pārbaudes aclchk.c, ar SQLSTATE 42501 (insufficient_privilege). Migrāciju rīki, piemēram, Flyway, Liquibase, Alembic, Django vai Rails, to parasti parāda ietītu savā izņēmumā, piemēram, kad pirmajā palaišanā mēģina izveidot savu vēstures tabulu.

Kas mainījās PostgreSQL 15

PostgreSQL 15 laidiena piezīmēs ir divas saistītas izmaiņas:

• PUBLIC zaudēja CREATE tiesības uz public shēmu. Pirms 15. versijas jebkura loma varēja veidot tabulas, funkcijas un citus objektus shēmā public. Tagad to var tikai shēmas īpašnieks un tie, kam tas piešķir CREATE. USAGE, kas ļauj lomām piekļūt objektiem shēmā, joprojām ir piešķirts visiem.
• Shēma public tagad pieder pg_database_owner. Šai iepriekš definētajai lomai vienmēr ir tieši viens netiešs dalībnieks, pašreizējās datubāzes īpašnieks (iepriekš definētās lomas). Tātad tas, kuram pieder datubāze, kontrolē arī tās public shēmu. Pirms 15. versijas tā piederēja sākotnējam superlietotājam.

Kāpēc? Tas ir drošas shēmu lietošanas modelis, ko dokumentācija iesaka kopš CVE-2018-1058. Ja katrs lietotājs var veidot objektus shēmā, kas ir visu meklēšanas ceļā, viens lietotājs var izveidot, piemēram, funkciju ar tādu pašu nosaukumu kā tā, ko izsauc citi, un mainīt to vaicājumu darbību. Shēmu nodaļa tagad veco iestatījumu sauc par "nekad ne drošu modeli", pieņemamu tikai ar vienu lietotāju vai dažiem lietotājiem, kas viens otram uzticas.

Kāpēc vienā serverī strādā, bet citā ne

Šī ir mulsinošā daļa. Jaunie noklusējumi attiecas tikai uz jauniem klasteriem un jaunām datubāzēm tajos. Saskaņā ar laidiena piezīmēm klastera atjaunināšana vai dampa atjaunošana saglabā esošās public tiesības un īpašumtiesības. Tātad:

• produkcijas datubāze, kas atjaunināta no 14 uz 16 ar pg_upgrade, joprojām ļauj visiem veidot objektus public;
• jauns 16. versijas klasteris testa vai jaunai videi to neļauj;
• tā pati migrācija produkcijā izdodas, bet jaunajā vidē ne.

Pārbaudiet, kas jums patiesībā ir:

-- kam pieder datubāze un shēma?
SELECT d.datname, pg_get_userbyid(d.datdba) AS db_owner,
       pg_get_userbyid(n.nspowner) AS schema_owner
FROM pg_database d, pg_namespace n
WHERE d.datname = current_database() AND n.nspname = 'public';

-- vai lietotnes loma var tur veidot un izmantot objektus?
SELECT has_schema_privilege('app', 'public', 'CREATE') AS can_create,
       has_schema_privilege('app', 'public', 'USAGE')  AS can_use;

psql komanda \dn+ public arī parāda shēmas īpašnieku un piekļuves tiesības.

Trīs tīri risinājumi

Izvēlieties atkarībā no tā, kā datubāze tiek izmantota.

1. Ļaujiet lietotnei piederēt sava datubāze

Ja datubāze pieder vienai lietotnei, padariet tās lomu par datubāzes īpašnieku. Caur pg_database_owner tai tad pieder public, un tā var tur veidot objektus, kamēr citas lomas joprojām nevar. Tieši ap šādu uzstādījumu ir veidots PostgreSQL 15.

-- 1. variants: lietotnei pieder sava datubāze (un tātad public shēma)
CREATE DATABASE appdb OWNER app;
-- vai esošai datubāzei:
ALTER DATABASE appdb OWNER TO app;

Paturiet prātā, ka datubāzes īpašniekam ir liela vara pār datubāzi. Dokumentācija norāda, ka drošs shēmu lietošanas modelis nepastāv, ja datubāze pieder neuzticamam lietotājam. Lietotnes pašas servisa kontam tas ir labi, bet ne lomai, kas koplietota ar cilvēkiem, kuriem pilnībā neuzticaties.

2. Dodiet lietotnei savu shēmu

Šo modeli dokumentācija apraksta kā lietotāju ierobežošanu privātās shēmās: izveidojiet shēmu, kas pieder lietotnes lomai un ir nosaukta tāpat. Noklusējuma search_path sākas ar "$user", tāpēc loma šo shēmu izmanto pirmo bez papildu iestatīšanas. Objekti nonāk app, public paliek tīra, un tiesības ir viegli saprotamas.

-- 2. variants: atsevišķa shēma lietotnei
CREATE SCHEMA app AUTHORIZATION app;
-- noklusējuma search_path ir "$user", public: loma vispirms atrod savu shēmu

-- 3. variants: piešķiriet CREATE uz public tikai vienai lomai
GRANT CREATE ON SCHEMA public TO app;

Pēdējā rinda šajā blokā ir 3. variants: ja jums jāpaliek public, piemēram, tāpēc, ka kodā ir iekodēts public., piešķiriet CREATE vienai lomai, kurai tas vajadzīgs. Tas joprojām ir krietni stingrāk nekā noklusējums pirms 15. versijas, jo citas lomas tur objektus veidot nevar.

Nākamā kļūda: permission denied for table

Kad lietotne jau var veidot tabulas, otra loma, piemēram, atskaišu vai tikai lasīšanas lietotājs, bieži saņem "permission denied for table". Tās ir citas tiesības. USAGE uz shēmu ļauj lomai ieskatīties tajā, un katrai tabulai joprojām vajadzīga sava piešķiršana. ALTER DEFAULT PRIVILEGES aptver vēlāk izveidotās tabulas:

-- tikai lasīšanas loma: USAGE uz shēmu plus SELECT uz tabulām
GRANT USAGE ON SCHEMA app TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting;

-- un tabulām, ko lietotnes loma izveidos vēlāk
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting;

Noklusējuma tiesības attiecas uz objektiem, ko izveido FOR ROLE norādītā loma. No mūsu pieredzes, tieši no šejienes nāk lielākā daļa "vakar strādāja" tiesību pieteikumu: piešķiršanas reiz izdarīja ar roku, tad migrācija izveidoja jaunu tabulu, kurai neviens tiesības nepiešķīra.

Kā ar atjauninātām datubāzēm?

Ja atjauninājāt no 14. vai vecākas versijas, jums joprojām ir vecās, atvērtās tiesības. Apsveriet apzinātu pāreju uz jauno noklusējumu, kā iesaka laidiena piezīmes un shēmu dokumentācija, kā daļu no plašākas drošības stiprināšanas:

• atrodiet lomas, kas patiešām veido objektus public, un piešķiriet tām CREATE skaidri vai pārceliet tās uz savām shēmām;
• tad izpildiet REVOKE CREATE ON SCHEMA public FROM PUBLIC;;
• dokumentācija iesaka arī pārbaudīt public, vai tur nav objektu ar tādiem pašiem nosaukumiem kā pg_catalog objektiem.

Vispirms pārbaudiet to uz testa kopijas. Aizmirsts cron uzdevums vai rīks, kas public veido pagaidu darba tabulas, sāks krist tajā brīdī, kad atņemsiet tiesības.