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.

widgety

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?
Lupa.cz: Proč jsou firemní počítače pomalé?

Proč jsou firemní počítače pomalé?

Lupa.cz: Kde leží hardwarový pupek světa?

Kde leží hardwarový pupek světa?

Podnikatel.cz: „Lex Babiš“ Babišovi paradoxně pomůže

„Lex Babiš“ Babišovi paradoxně pomůže

DigiZone.cz: Rapl: seriál, který vás smíří s ČT

Rapl: seriál, který vás smíří s ČT

Lupa.cz: Blíží se konec Wi-Fi sítí bez hesla?

Blíží se konec Wi-Fi sítí bez hesla?

DigiZone.cz: Nova opět stahuje „milionáře“

Nova opět stahuje „milionáře“

DigiZone.cz: Digi Slovakia zařazuje stanice SPI

Digi Slovakia zařazuje stanice SPI

Vitalia.cz: Tradiční čínská medicína a rakovina

Tradiční čínská medicína a rakovina

DigiZone.cz: Numan Two: rozhlasový přijímač s CD

Numan Two: rozhlasový přijímač s CD

Vitalia.cz: Jak Ondra o astma přišel

Jak Ondra o astma přišel

Lupa.cz: Další Češi si nechali vložit do těla čip

Další Češi si nechali vložit do těla čip

Podnikatel.cz: Byla finanční manažerka, teď cvičí jógu

Byla finanční manažerka, teď cvičí jógu

DigiZone.cz: Technisat připravuje trojici DAB

Technisat připravuje trojici DAB

Root.cz: Hořící telefon Samsung Note 7 zapálil auto

Hořící telefon Samsung Note 7 zapálil auto

DigiZone.cz: Mordparta: trochu podchlazený 87. revír

Mordparta: trochu podchlazený 87. revír

DigiZone.cz: Parlamentní listy: kde končí PR...

Parlamentní listy: kde končí PR...

Podnikatel.cz: Nemá dluhy? Zjistíte to na poště

Nemá dluhy? Zjistíte to na poště

Vitalia.cz: Tesco nabízí desítky tun jídla zdarma

Tesco nabízí desítky tun jídla zdarma

Podnikatel.cz: Letáky? Lidi zuří, ale ony stále fungují

Letáky? Lidi zuří, ale ony stále fungují

Vitalia.cz: Voda z Vltavy před a po úpravě na pitnou

Voda z Vltavy před a po úpravě na pitnou