Hlavní navigace

Správa uživatelů a databázových objektů v PostgreSQL

Pavel Stěhule 19. 11. 2008

Určení vlastníků db objektů se v řadě projektů vůbec neřeší nebo řeší příliš pozdě. To už obvykle chaos ve vlastnictví začne komplikovat administraci. Tento článek se věnuje popisu problematice vlastnictví db objektů a jako bonus obsahuje pár užitečných 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 noinherit;
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 explicitní přepnutí role pouze zdržuje. Proto tu je atribut inherit. Ten zajišťuje, že všechny role (všichni uživatelé), kteří mají přístup (právo) k roli, získají její práva automaticky.

Bez dalšího nastavení přístupových práv nemá nikdo vyjma vlastníka a super uživatele přístup k vytvořené tabulce. Musím povolit přístup.

postgres=# create role developer nologin inherit;
CREATE ROLE
postgres=# grant usage on schema personalistika to developer;
GRANT
postgres=# grant select on personalistika.foo to developer;
GRANT

Nyní ještě jako uživatel pavels nemohu číst tabulku.

postgres=# set role to pavels;
SET
postgres=> select * from personalistika.foo ;
ERROR:  permission denied for schema personalistika
LINE 1: select * from personalistika.foo ;
                      ^

V dalším kroku přiřadím uživatele pavels do skupiny develeper, resp. uživateli pavels přiřadím práva role developer.

postgres=# grant developer to pavels;
GRANT ROLE
postgres=# set role to pavels;
SET
postgres=> select * from personalistika.foo ;
 a
---
(0 rows)

Nyní mám přístup k tabulce personalistika.foo, aniž bych se musel explicitně přepnout do role developer (o to se mi postará atribut inherit v definici role developer).

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 svou 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 účtů, 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í

Z praxe mohu jen doporučit:

  • vlastníkem všech objektů by měla být jedna role (výjimkou 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)

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 – www.postgresql­.org/docs/8.3/in­teractive/user-manag.html.

Našli jste v článku chybu?

21. 11. 2008 14:29

Něco podobného se řeší docela běžně - např. v systémových pohledech - jen se tomu neříká LBAC. V pg můžete použít modul Veil http://ftp.iasi.roedu.net/mirrors/ftp.postgresql.org/projects/pgFoundry/veil/

19. 11. 2008 22:30

Bohužel s tím nemám žádnou praktickou zkušenost - díval jsem se podporu ldapu v pg a ta je opravdu hodně minimalistická - v podstatě to dokáže jen ověřit heslo.
Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Měšec.cz: Nenechte se ošidit, když vám staví dům

Nenechte se ošidit, když vám staví dům

Vitalia.cz: Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Měšec.cz: Finančním poradcům hrozí vracení provizí

Finančním poradcům hrozí vracení provizí

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá

120na80.cz: Jak oddálit Alzheimera?

Jak oddálit Alzheimera?

Vitalia.cz: Dáte si jahody s plísní?

Dáte si jahody s plísní?

Měšec.cz: Air Bank zruší TOP3 garanci a zdražuje kurzy

Air Bank zruší TOP3 garanci a zdražuje kurzy

Lupa.cz: Insolvenční řízení kvůli cookies? Vítejte v ČR

Insolvenční řízení kvůli cookies? Vítejte v ČR

Vitalia.cz: Říká amoleta - a myslí palačinka

Říká amoleta - a myslí palačinka

Podnikatel.cz: EET: Totálně nezvládli metodologii projektu

EET: Totálně nezvládli metodologii projektu

120na80.cz: Pánové, pečujte o svoje přirození a prostatu

Pánové, pečujte o svoje přirození a prostatu

Lupa.cz: Co se dá měřit přes Internet věcí

Co se dá měřit přes Internet věcí

Podnikatel.cz: Udávání kvůli EET začalo

Udávání kvůli EET začalo

DigiZone.cz: ČT má dalšího zástupce v EBU

ČT má dalšího zástupce v EBU

Vitalia.cz: 9 největších mýtů o mase

9 největších mýtů o mase

Podnikatel.cz: 1. den EET? Problémy s pokladnami

1. den EET? Problémy s pokladnami

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA