Het gebruik van schemas in Oracle vs PostgreSQL.
Voor veel ervaren Oracle DBA’s is het gebruik van schema’s en users in Oracle gesneden koek, maar als ze in de PostgreSQL wereld worden losgelaten voelen ze zich stuurloos. Daarom in dit artikel een poging enige structuur te brengen in de PostgreSQL begrippen role en schema voor de Oracle DBA.
Laten we beginnen met een korte herhaling van een paar begrippen uit de Oracle wereld:
- user: binnen Oracle is een user het concept dat nodig is om te verbinden aan de database.
- role: een role is gedefinieerd als een verzameling van privileges en potentieel andere roles.
- schema: dit concept kent Oracle niet expliciet. De documentatie verwijst naar een schema als een verzameling objecten. En wijst de user aan als de exclusieve eigenaar van een schema met dezelfde naam.
Ondanks dat PostgreSQL, net als Oracle, een Relationele database is worden sommige concepten geheel anders uitgelegd. Daarom is het van belang te zorgen dat de concepten juist en passend bij het gekozen RDBMS worden toegepast. Het domein van user, rollen en schema’s is één van de gebieden waar Oracle en PostgreSQL sterk verschillen.
Laten we de begrippen aan de hand van een aantal demo’s uitleggen.
Roles:
Een role wordt in PostgreSQL uitgelegd als een concept dat objecten en rechten kan bezitten. Daarmee komt het redelijk overeen met het begrip user binnen Oracle. Het begrip user bestaat niet (meer) in PostgreSQL. Wel bestaan er nog create user scripts maar deze zijn een wrapper over het role concept;
$ psql demo
psql (14.4)
Type "help" for help.
demo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
demo=# CREATE ROLE demo01 WITH LOGIN ENCRYPTED PASSWORD 'password01';
CREATE ROLE
demo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+----------
demo01 | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Schema
Een schema is een namespace waarin objecten aangemaakt kunnen worden. Deze objecten zijn dan van een specifieke role. Het is mogelijk in de database een aantal schema’s aan te maken voor dezelfde role.
$ psql demo
psql (14.4)
Type "help" for help.
demo=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
Er is standaard een schema public dat voor eenieder toegankelijk is. Het lijkt verstandiger aparte schema’s voor applicatie objecten te maken.
demo=# create schema s1 authorization demo01;
CREATE SCHEMA
demo=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
s1 | demo01
(2 rows)
We zien hier dat het public schema van de postgres (is algemeen belang) is en het s1 schema bij de demo01 role hoort.
demo=# create schema authorization demo01;
CREATE SCHEMA
demo=# \dn
List of schemas
Name | Owner
--------+----------
demo01 | demo01
public | postgres
s1 | demo01
(3 rows)
En nu wordt er een schema gecreëerd waarvan de naam van het schema en dat van de role overeenkomen. Maar als ik nu een tabel wil creëren. In welk schema komt dat? Zeker nu de demo01 user de beschikking heeft over drie schema’s.
$ psql demo demo01
psql (14.4)
Type "help" for help.
demo=> create table emp(ename varchar(20), empno int);
CREATE TABLE
demo=> \d emp
Table "demo01.emp"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
ename | character varying(20) | | |
empno | integer | | |
Volgens deze demo komt het in het demo01 schema, omdat ik als demo01 ingelogd ben. Was het demo01 schema niet gecreëerd dan was het automatisch in het public schema gekomen. De vraag is: is dat altijd zo? Nee dat hangt er vanaf:
demo=> show search_path;
search_path
-----------------
"$user", public
(1 row)
Er bestaat een setting search_path, die bepaalt in welke schema’s er wordt gezocht naar een specifiek object. Door dit te wijzigen zie je standaard bepaalde schema’s (niet). Het is echter ook mogelijk een object specifiek in een bepaald schema te creëren.
demo=> create table s1.emp(ename varchar(20), empno int);
CREATE TABLE
demo=> create table public.emp(ename varchar(20), empno int);
CREATE TABLE
demo=> select schemaname, tablename, tableowner from pg_tables where tablename='emp';
schemaname | tablename | tableowner
------------+-----------+------------
public | emp | demo01
s1 | emp | demo01
demo01 | emp | demo01
(3 rows)
Deze demo is gemaakt in PostgreSQL 14. Vanaf PostgreSQL 15 zijn er speciale rechten nodig om in het public schema objecten te creëren.
Het effect van search_path is heel goed te zien als je data in de tabellen stopt:
demo=> insert into demo01.emp values ('bram' , 1);
INSERT 0 1
demo=> insert into s1.emp values ('david' , 2);
INSERT 0 1
demo=> insert into public.emp values ('erik' , 3);
INSERT 0 1
demo=> set search_path = s1, public, "$user";
SET
demo=> select * from emp;
ename | empno
-------+-------
david | 2
(1 row)
demo=> select * from public.emp;
ename | empno
-------+-------
erik | 3
(1 row)
We zien nu dat met het wijzigen van het search_path er naar een andere emp tabel wordt gewezen. Het is belangrijk te beseffen dat het search path op het niveau van de database, de role en de sessie gezet kan worden. Een verkeerde setting kan dus tot gevolg hebben dat een andere tabel bevraagd wordt.
Nu we schema’s bekeken hebben is het ook leuk om te kijken hoe één en ander werkt in combinatie met een andere role.
$ psql demo
psql (14.4)
Type "help" for help.
demo=# CREATE ROLE demo02 WITH LOGIN ENCRYPTED PASSWORD 'password02';
CREATE ROLE
demo=# exit
met de role demo02 kunnen we nu een tweede gebruiker naspelen:
$ psql demo demo02
psql (14.4)
Type "help" for help.
demo=> select * from public.emp;
ERROR: permission denied for table emp
demo=> select * from s1.emp;
ERROR: permission denied for schema s1
LINE 1: select * from s1.emp;
We zien nu dat er 2 verschillende fouten zijn voor het object in het algemeen toegankelijk public schema en die in het s1 schema. Door de demo01 role een grant op de public.emp tabel uit te laten voeren, kan deze door demo02 bevraagd worden.
$ psql demo demo01
psql (14.4)
Type "help" for help.
demo=> grant select on public.emp to demo02;
GRANT
demo=> exit
$ psql demo demo02
psql (14.4)
Type "help" for help.
demo=> select * from public.emp;
ename | empno
-------+-------
erik | 3
(1 row)
Echter voor de tabel in een ander schema dan publc is er een extra stap nodig.
$ psql demo demo01
psql (14.4)
Type "help" for help.
demo=> select * from s1.emp;
ename | empno
-------+-------
david | 2
(1 row)
demo=> grant select on s1.emp to demo02;
GRANT
demo=> grant usage on schema s1 to demo02;
GRANT
demo=> exit
$ psql demo demo02
psql (14.4)
Type "help" for help.
demo=> select * from s1.emp;
ename | empno
-------+-------
david | 2
(1 row)
Er is naast een grant op de tabel ook een grant op het schema nodig.
Bovenstaande simpele demo’s geven aan dat het schema en rechten verhaal tussen Oracle en PostgreSQL sterk verschilt. Het is daarom goed na te denken over je ‘design’ als je een PostgreSQL database gaat opzetten. Enkele algemene tips van onze kant zijn:
- Gebruik het public schema zo min mogelijk. De wijzigingen in PostgreSQL 15 geven aan dat ook de PostgreSQL community anders is gaan denken over het gebruik van ‘public’.
- Let op de settings van search_path. Het gebruik van fully qualified names is aan te raden om verwarring te voorkomen.
- Let op het eigenaarschap van een schema, laat dit niet bij default postgres zijn
- Gebruik schema’s maar overdrijf niet.
We hopen dat we je met dit artikel, “Het gebruik van schemas in Oracle vs PostgreSQL”, op weg bent geholpen. Mocht je hulp nodig hebben of je hebt vragen n.a.v. dit artikel, laat het ons dan weten.