Správa uživatelů a databázových objektů v PostgreSQL
14.11.2008 Autor: Pavel Stěhule
Určení vlastníků db objektů se v řadě projektů vůbec neřeší nebo řeší příliš pozdě (ve chvíli, kdy chaos ve vlastnictví začne komplikovat administraci). Tento článek se věnuje popisu problematice vlastnictví db objektů, plus obsahuje pár základních doporučení.
Vlastník objektu
V PostgreSQL se seznam uživatelů sdílí napříč všemi databázemi v clusteru (instanci). V tomto se PostgreSQL naprosto odlišuje od MySQL, kde se uživatelé registrují ke každé databázi. Většina projektů, které běží na webu si vystačí s jedním db účtem, v lepším případě se dvěma (obyčejný uživatel, a administrátor). I v takovém případě je dobré, když vlastníkem objektu je uživatel s vyššími právy (hlavně jiný než aplikační uživatel). Potom, v případě prolomení běžného aplikačního účtu Vám alespoň útočník nemůže smazat tabulky nebo pozměnit strukturu databáze. Totiž změnu db objektu (včetně jeho odstranění) může provést pouze vlastník objektu (v tomto článku vlastníkem objektu míním většinou vlastníka tabulky, nicméně vlastnit se dají i další db objekty - sekvence, funkce). Stejně jako v případě systémových účtů se snažíme omezovat použití účtu root, snažíme se omezovat použití účtu superuživatele - účet postgres.
postgres=# create role pst_sro nologin; CREATE ROLE postgres=# set role to pst_sro; SET postgres=> create table foo(a integer); CREATE TABLE postgres=# set role to pavels; SET postgres=> alter table foo add column b integer; ERROR: must be owner of relation foo
Vlastníkem tabulky je role pst_sro. V tomto případě zastupuje imaginární firmu PST s.r.o. Vždy doporučuji jako vlastníka použít virtuální roli (zkráceně roli) - s atributem nologin. Důvod je jednoduchý - reální uživatelé se občas mění (obyčejně častěji než název firmy). Pokud budeme všechny vytvářecí a pozměňující skripty spouštět pod tímto uživatelem, tak nikdy nebudeme mít chaos ve vlastnictví. Navíc automaticky jsou db objekty nepřístupné všem aplikačním účtům - což nás nutí zamyslet se nad bezpečnostní politikou a explicitně nastavit přístup. Osobně bych nikdy programátorům právo přepnutí se do role, která se používá jako vlastník nikdy nedal. Vede to totiž k tomu, že programátoři provedou změny pomocí GUI, a zapomenou na vytvoření pozměňujících (alter) skriptů. Ty se používají pro udržení minimálně dvou izolovaných prostředí (vývojového a produkce). Toto pozapomenutí je snadný způsob jak si znepříjemnit start nové verze aplikace. Pouze vybraní administrátoři nebo vývojáři by měli mít tuto možnost. V příkladě ti, co mohou používat roli power_developer. Všimněte si, že se snažím, co nejdéle používat role. Poznámka: v ANSI SQL se nijak zvlášť nerozlišuje mezi rolí a uživatelským účtem. V textu používám termín role pro ANSI SQL roli s atributem nologin a termín účet pro ANSI SQL roli s atributem login. Role s atributem nologin nelze použít pro přihlášení do databáze.
postgres=# grant pst_sro to power_developer; GRANT ROLE postgres=> set role to power_developer; SET postgres=> set role to power_developer; SET postgres=> alter table foo add column b integer; ERROR: must be owner of relation foo
Vypadá to jako zrada, power_developer má práva role pst_sro, a přesto nedokáže modifikovat tabulku. O zradu se nejedná. Za tímto chováním se skrývá atribut noinherit, který si vynucuje explicitní přepnutí do dané role.
postgres=> set role to pst_sro; SET postgres=> alter table foo add column b integer; ALTER TABLE
Proč si atributem noinherit komplikovat život? Prostě proto, abych zabránil vytváření db objektů rolí power_developer. Tím si omezím vlastnictví pouze na roli pst_sro.
Zkusím jako obyčejný uživatel vytvořit tabulku.
postgres=> set role to pavels; SET postgres=> create table tab(a integer); CREATE TABLE
Žádný problém - jenomže to je problém. Obyčejní uživatelé by neměli mít práva vytvářet tabulky. Kde bychom skončili. Zde je jádro pudla v nastavení práv schématu public (ne náhodou řada DBA jako první krok po instalaci zruší toto schéma).
Jako superuser musím povolit roli pst_sro vytvářet nová schémata (protože uživatel postgres je vlastníkem databáze postgres - v praxi by i vlastníkem databáze měl být vyčleněná role - v tomto případě pst_sro).
postgres=# grant create on database postgres to pst_sro; GRANT postgres=# set role to pst_sro; SET postgres=> create schema personalistika; CREATE SCHEMA postgres=> set role to pavels; SET postgres=> create table personalistika.foo(a integer); ERROR: permission denied for schema personalistika
Nyní chybějící oprávnění zabránilo uživateli pavels vytvořit tabulku. Ještě jednou zkusím vytvořit tabulku pod uživatelem power_developer.
postgres=> set role to power_developer; SET postgres=> CREATE table personalistika.foo(a integer); ERROR: permission denied for schema personalistika
Takže se musím explicitně přepnout do role pst_sro.
postgres=> set role to pst_sro; SET postgres=> CREATE table personalistika.foo(a integer); CREATE TABLE
Atribut noinherit si vynucuje explicitní přepnutí do role. Důvod byl naznačen - chci mít pouze jednoho vlastníka objektů. Jsou však případy, kdy vynucení explicitního přepínání rolí nechceme. Proto tu je atribut inherit. Při použití atributu inherit role získá práva všech rolí jejichž je členem - bez nutnosti explicitního přepínání.
Uživatel
Docela často se vedou spory o přístupu k db uživatelům. Řeší se otázka, zda řešit zabezpečení na aplikační nebo databázové úrovni. Pokud je pro Vás databáze obyčejným storage systémem, pak to asi řešit nebudete, prostě vše (včetně práv) budete řešit na aplikační úrovni. Pokud chcete ale lépe zabezpečit svojí aplikaci, pak se vyplatí uvažovat o zabezpečení i na úrovni databáze. Jednak máte jistotu, že toto zabezpečení Vám pravděpodobně nikdo neobejde (včetně Vás samotných - nejnepříjemnějším útočníkem je vlastní hloupost), jednak zabezpečení na úrovni db je hodně efektivně implementováno a konečně pokud používáte víc db účtu, pak se můžete z logů dozvědět dost zajímavé informace. Např. který uživatel spouští chybný nebo pomalý SQL příkaz. Pro malé systémy (do 100 uživatelů) si dovolím doporučit zabezpečení aplikace na úrovni databáze - dost si můžete zjednodušit kód. Pro větší systémy pak kombinovanou správu. Interní uživatelé používají skutečné db účty - ostatní uživatelé sdílí vybrané db účty a mají vlastní aplikační účet. Samozřejmě, pokud se duplikují aplikační a db účty, je těžko myslitelná efektivní správa účtu bez nějakého systému zajišťujícího synchronizaci účtů. Ohledně výkonu nemusíte mít strach - před pár lety jsem testoval databázi s padesáti tisíci uživateli.
Doporučení
To je v krátkosti základ toho, co lze s vlastnictvím a právy provádět. Z praxe mohu jen doporučit:
- vlastníkem všech objektů by měla být jedna role (vyjímkou jsou tabulky obsahující extrémně citlivé údaje - např. přihlašovací).
- přístup k db objektům opět nastavovat pouze rolím, nikoliv uživatelům - pak v dalším kroku nastavíte uživatelům potenciální role (jako uživatele beru i role, které slouží aplikacím).
postgres=# create role personalista inherit nologin; CREATE ROLE postgres=# grant usage on schema personalistika to personalista; GRANT postgres=# grant select on personalistika.foo to personalista; GRANT postgres=# grant personalista to pavels; GRANT ROLE postgres=# set role to pavels; SET postgres=> select * from personalistika.foo ; a --- (0 rows)
- Aplikačním účtům odeberte právo vytvářet funkce v PL/pgSQL
Přístup k informacím o rolích, vlastnictví
Vlastník objektu je zobrazený ve výpisu - v tomto případě výpisu tabulek:
postgres=# \dt personalistika.foo
List of relations
Schema | Name | Type | Owner
----------------+------+-------+---------
personalistika | foo | table | pst_sro
(1 row)
Členství uživatele v rolích zjistíme jednoduše - metapříkazem \du:
postgres=# \du pavels
List of roles
Role name | Attributes | Member of
-----------+------------+--------------------------
pavels | | {developer,personalista}
Pro zobrazení přístupových práv k objektům slouží příkaz '\dp:
postgres=# \dp personalistika.foo
Access privileges
Schema | Name | Type | Access privileges
----------------+------+-------+-------------------------
personalistika | foo | table | pst_sro=arwdDxt/pst_sro
: developer=r/pst_sro
: personalista=r/pst_sro
(1 row)
V konzole nemáme žádnou možnost, jak zjistit efektivní práva. K dispozici máme systémové funkce, které poskytují informaci o tom, zda konkrétní uživatel má nebo nemá určitá práva k tabulkám:
postgres=# select has_table_privilege('pavels','personalistika.foo','select');
has_table_privilege
---------------------
t
(1 row)
postgres=# select has_table_privilege('pavels','personalistika.foo','insert');
has_table_privilege
---------------------
f
(1 row)
pg_hba.conf
Kromě SQL příkazu GRANT ještě v případě PostgreSQL nesmíme zapomenout na konfigurační soubor pg_hba.conf ve kterém povolujeme (případně zakazujeme) konkrétním uživatelům přístup z určitých ip adres. Struktura souboru je poměrně jednoduchá - soustředím se pouze na omezení přístupu z ip.
host all postgres 192.10.1.20/32 md5 host all postgres 0.0.0.0/0 reject host template1 all 0.0.0.0/0 reject host all all 0.0.0.0/0 md5
Soubor obsahuje sloupce určující typ přístupu, určení databáze, určení uživatele, určení ip adresy a způsob autorizace, případně odmítnutí přístupu. Při rozhodování, zda uživatele přihlásit nebo nepřihlásit k databázi systém postupuje po řádcích, a aplikuje první metodu autorizace pro kterou najde shodu v určení způsobu způsobu přístupu, uživatele a databáze. Výše uvedené řádky si lze vyložit následujícím způsobem - uživatel postgres může přistupovat do všech databází, ale pouze z adresy 192.10.1.20 a to ještě se musí přihlášení potvrdit heslem. Ze všech ostatních ip adres se uživatel postgres nemůže připojit k žádné databázi. Ostatní uživatelé se mohou připojit (po zadání hesla) ke všem databázím vyjma databáze template1.
Při nastavování práv bychom se měli řídit jednoduchým pravidlem - žádný uživatel by neměl vidět více, než nezbytně potřebuje ke své práci. To se lehko říká, a poměrně snadno implementuje, pokud na bezpečnost myslíme již při návrhu aplikace - bezpečnostní hlediska jsou docela dobrým vodítkem při návrhu db modelu. Dodatečně to pak již moc nejde. V tomto článku se nesnažím duplikovat dokumentaci, šlo mi o to v krátkosti ukázat možnosti správy databáze. Pro detailní informace doporučuji otevřít dokumentaci - http://www.postgresql.org/docs/8.3/interactive/user-manag.html.