W PostgreSQL spotkać możemy trzy pojęcia związane z użytkownikami : użytkownicy, role i grupy. Od wersji 8.4 pojęcia roli i użytkownika są w zasadzie tożsame. Różnica sprowadza się do tego, że tworząc konto za pomocą "create user", taki użytkownik będzie miał możliwość logowania się. Natomiast w przypadku "create role" takiej możliwości nie będzie miał. Użytkownik to rola z możliwością logowania. Pisząc w dalszej części "rola", będę miał na myśli konto - niezależnie od tego, czy ma możliwość logowania się czy nie. "create user" wykorzystywane jest do tworzenia faktycznych użytkowników, "create role" najczęściej do tworzenia tak zwanych ról grupowych (służących do masowego zarządzania uprawnieniami). "create group" działa ze względu na wsteczną kompatybilność. Grupy służyły kiedyś do masowego zarządzania uprawnieniami. Jednak zostały one zastąpione rolami grupowymi.

W chwili inicjalizacji klastra (initdb) tworzony jest jeden użytkownik o nazwie "postgres" będący jednocześnie superużytkownikiem. Tego użytkownika powinniśmy stosować tylko do administracji klastrem. Nie powinien być on wykorzystywany przez aplikacje korzystające z bazy danych ani użytkowników końcowych (ludzkich użytkowników). Użytkownik ten ma uprawnienia do zrobienia wszystkiego z klastrem PostgreSQL, stąd jego wykorzystanie jako zwykłego użytkownika naraża nas na niebezpieczeństwo jej uszkodzenia lub wrogiego przejęcia. Na potrzeby aplikacji i użytkowników tworzy się zwykle osobne konta i nadaje im niezbędne uprawnienia. Użytkownicy mają zasięg klastra - mogą więc logować się do wszystkich baz danych (o ile odpowiedni wpis w "pg_hba.conf" pozwala im na to). Nie ma możliwości stworzenia użytkownika istniejącego tylko w jednej bazie danych.

Użytkownicy

Na potrzeby przykładów utworzymy użytkownika “mapet”, jeśli jeszcze go nie mamy:

create user mapet with password 'jakie_sobie_chcesz';

Sprawdzanie informacji o użytkownikach (rolach)

Aby sprawdzić, jakie role mamy dostępne w systemie, wystarczy sprawdzić zawartość słownika "pg_user":

Co oznaczają poszczególne kolumny?

"usename" (tak - nie useRname tylko usename)- nazwa użytkownika

"usesysid" - id tego użytkownika (roli)

"usecreatedb"- określa, czy dana rola może tworzyć bazy danych

"usesuper" -określa, czy dana rola jest superużytkownikiem. Jeśli jest "true" w "usesuper", to niezaleznie od tego, że w kolumnie "usecreatedb" jest "false", rola ta może tworzyć bazy danych.

"userepl" -określa, czy dana rola może być użyta do replikacji

"userbypassrls" -określa, czy rola omija wszystkie zasady zabezpieczeń na poziomie wierszy (funkcjonalność wprowadzona w wersji 9.6.

Więcej: https://www.postgresql.org/docs/current/ddl-rowsecurity.html).

"passwd"- zawsze ukryte pod "********" hasło

"validuntil"- termin, do którego dana rola będzie funkcjonować. Domyślnie nie jest zakładane żadne ograniczenie.

"useconfig" - ustawienia na poziomie roli. Dla przykładu dla użytkownika "mapet" ustawiamy parametr "random_page_cost" na 1:

alter user mapet set random_page_cost=1;

Ta komenda będzie tożsama z:

alter role mapet set random_page_cost=1;

Zobaczmy teraz, co widać w kolumnie "useconfig" dla roli "mapet":

select usename,useconfig from pg_user where usename='mapet';

Nieco więcej informacji o użytkownikach znajdziemy w "pg_authid". W słowniku tym znajdą się nie tylko użytkownicy, ale też grupy.

select * from pg_authid where rolname='mapet';

Co oznaczają poszczególne kolumny?

"oid"- ID użytkownika

"rolname"- nazwa użytkownika

"rolsuper"- określa, czy dany użytkownik ma uprawnienia superusera. Jeśli użytkownik ma "true" w "rolsuper", to niezależnie od tego, że ma on ustawione "false" w kolumnach "rolcreaterole" i "rolcreatedb", może on tworzyć użytkowników i bazy danych.

"rolinherit" -określa, czy dany użytkownik automatycznie dziedziczy uprawnienia grup, których jest członkiem

"rolcreaterole" -definiuje, czy użytkownik może tworzyć innych użytkowników

"rolcreadetedb" -dotyczy możliwości tworzenia baz danych przez użytkownika

"rolcanlogin"- powie nam, czy dany użytkownik może się logować do bazy danych. Niekiedy tworzymy użytkowników, których jedynym zadaniem jest bycie właścicielem obiektów i wtedy tworzymy użytkownika bez możliwości logowania - ze względów bezpieczeństwa.

"rolreplication" -określa, czy użytkownik może zostać użyty do replikacji

"rolbypassrls" - podobnie jak w przypadku "pg_user", określa, czy użytkownik omija wszystkie zasady zabezpieczeń na poziomie wierszy

"rolconnlimit"- określa limit połączeń dla danego użytkownika. Domyślna wartość "-1" określa, że taki limit nie jest nakładany.

"rolpassword" - to zazwyczaj zaszyfrowane hasło, może być też puste, jeśli użytkownik takiego nie posiada

"rolvaliduntil", podobnie jak w przypadku "pg_users", to termin, do którego dany użytkownik będzie funkcjonował

Tworzenie użytkownika (roli) i jego własności

Tworzyć roli możemy za pomocą zarówno komendy "create user" jak i "create role". Wszystkie te komendy w tej chwili są aliasami. Jedyna różnica sprowadza się do tego, że użytkownik stworzony za pomocą "create user" będzie mógł się logować domyślnie do klastra, a ten stworzony za pomocą "create role" tego robić nie będzie mógł.

Ogólna konstrukcja tworzenia użytkownika wygląda w ten sposób:

CREATE USER / ROLE / GROUP NAZWA_UŻYTKOWNIKA
SUPERUSER / NOSUPERUSER
CREATEDB / NOCREATEDB
CREATEROLE / NOCREATEROLE
INHERIT / NOINHERIT
LOGIN / NOLOGIN
REPLICATION / NOREPLICATION
BYPASSRLS / NOBYPASSRLS
CONNECTION LIMIT X
PASSWORD 'HASŁO'
VALID UNTIL "May 4 12:00:00 2022 +1"

Co oznaczają poszczególne elementy?

Superuser/nosuperuser określa, czy rola jest superużytkownikiem. Superużytkownik może z bazą zrobić wszystko, tj. np.tworzyć i kasować bazy (nawet jeśli nie jest ich właścicielem), modyfikować lub kasować obiekty bazy danych - włącznie z tymi, których nie jest właścicielem, tworzyć i zarządzać użytkownikami (w tym tworzyć innych superużytkowników), a także zmieniać parametry klastra poprzez komendę: "alter system".Superużytkownik może czytać i zmieniać dane we wszystkich bazach i tabelach. Domyślna wartość to "nosuperuser".

Createdb/nocreatedb określa, czy rola może tworzyć bazy danych. Domyślna wartość "nocreatedb". Createrole/nocreaterole określa, czy rola może tworzyć inne role (role grupowe lub użytkowników). Domyślna wartość "nocreatedb".

Inherit/noinherit określa, czy tworzona rola będzie dziedziczyła uprawnienia z ról grupowych, których jest członkiem. Domyślnie każda rola należy do roli grupowej "public". Domyślna wartość to "inherit". Ustawienie na "inherit" (czyli domyślne) powoduje, że uprawnienia wynikające z ról grupowych, do których należy dana rola, będą ładowane od razu bez potrzeby wywoływania polecenia "set role".

Login/nologin określa, czy rola może się logować do klastra. W przypadku tworzenia roli za pomocą "create user" wartość domyślna będzie "login", w przypadku tworzenia za pomocą "create role" wartością domyślną będzie "nologin".

Replication/noreplication określa, czy dana rola może zostać użyta do replikacji. Domyślna wartość "noreplication".

Bypassrls/nobypassrls określa, czy dana rola omija ograniczenia wynikające z zabezpieczeń na poziomie wierszy. Domyślna wartość "nobypassrls".

Connection limit określa maksymalną liczbę sesji, jaką może nawiązać tworzona rola. Domyślna wartość to "-1", co oznacza, że nie ma limitu połączeń dla tej roli.

Password określa hasło użytkownika. Można go nie podawać, ale wtedy zalogować się na tego użytkownika będziemy mogli tylko z hosta, dla którego mamy ustawienie "trust" w pg_hba.conf.

Valid until określa termin ważności konta. Domyślna wartość "infinity" - czyli bez ograniczeń czasowych.

W zasadzie wszystkie te przełączniki są fakultatywne. Najprościej moglibyśmy stworzyć użytkownika (rolę mającą uprawnienia do logowania się) w ten sposób:

create user nazwa;

Na tę rolę będziemy się mogli zalogować jedynie z hosta, dla którego w pg_hba.conf mamy ustawiony "trust" z racji braku hasła. Minimum dla "normalnego" użytkownika:

create user nazwa password 'jakies_haslo';

Do wersji 14 włącznie nowo stworzony użytkownik mógł tworzyć swoje obiekty w schemacie “public”. Od wersji 15 trzeba w tym celu dodać mu jeszcze uprawnienie:

grant create on schema public to mapet;

Pozostałe parametry możemy stosować wybiórczo. Możemy stworzyć użytkownika np. w ten sposób:

create user prawie_admin password 'hiszpanska_inkwizycja' createdb createrole;

Zmiana własności użytkownika (roli)

Wszystkie własności roli (createdb, createrole,login etc), które wykorzystywaliśmy przy tworzeniu roli, możemy również modyfikować. Służy do tego komenda "alter user" lub "alter role" - zadziałają tak samo. Mamy w bazie użytkownika "prawie_admin". Ma on możliwość tworzenia baz danych:

select usename,usecreatedb from pg_user where usename='prawie_admin';

Zmieniamy mu teraz tę własność:

alter user prawie_admin nocreatedb;

Ponownie sprawdzamy:

Własności roli można też modyfikować zbiorczo:

alter user prawie_admin nocreatedb nocreaterole;

Zmiana hasła użytkownika

Zmienić hasło innemu użytkownikowi możemy np. w ten sposób:

Alter user mapet password 'nowe_haslo';

A jeśli jesteśmy zalogowani w psql na dane konto:

\password

Zostaniemy wtedy poproszeni o podanie nowego hasła.

Kasowanie użytkownika (roli)

Użytkownika, który nie jest twórcą i właścicielem żadnych obiektów, skasować łatwo, ponieważ wystarczy wykonać komendę :

drop user nazwauzytkownika;

Sprawa jednak nieco się komplikuje, gdy stworzy on jakieś obiekty albo zostaną one mu przypisane - stanie się ich właścicielem. Na potrzeby przykładu tworzymy użytkownika "użytkownik" i dodajemy mu też uprawnienia do tworzenia obiektów w schemacie public (nadawanie uprawnienia do tworzenia obiektów w schemacie public jest potrzebne od wersji 15):

create user uzytkownik password 'andrzej';
grant create on schema public to uzytkownik;

Następnie logujemy się jako "uzytkownik" do "psql" i tworzymy z tego poziomu tabelę. Tabela ta będzie oczywiście należeć do użytkownika "uzytkownik":

psql -U uzytkownik -d postgres
create table moja_tabelka(x integer);

Spróbujemy teraz skasować użytkownika tak jak wcześniej:

drop user uzytkownik;

Ta próba się nie powiodła, ponieważ użytkownik, którego próbujemy skasować, jest właścicielem tabeli. PostgreSQL nawet nam ładnie powiedział, o jaką tabelę chodzi:

PostgreSQL nie pozwala nam na skasowanie tego użytkownika, ponieważ jest on właścicielem tabeli "tabela_andrzeja". Możemy zmienić właściciela dla każdego obiektu pojedynczo z pomocą komendy:

alter table moja_tabelka owner to postgres;

Możemy też przepisać własność wszystkich obiektów należących do wybranego użytkownika za pomocą takiej komendy:

reassign owned by uzytkownik to postgres;

Ponowna próba skasowania kończy się kolejnym błędem:

Jeśli korzystamy z wersji 14 lub wcześniejszej, użytkownik zostanie skasowany. Jeśli używamy wersji 15 lub późniejszej, PostgreSQL informuje, że użytkownik ma nadane uprawnienia do tworzenia obiektów w schemacie public. Aby usunąć użytkownika, trzeba jeszcze mu to uprawnienie odebrać i dopiero skasować:

revoke create on schema public from uzytkownik;
drop user uzytkownik;