PostgreSQL Unit 5 - PostgreSQL Schema explanation

Στο σημερινό δωρεάν μάθημα SQL θα εξηγήσουμε τι ακριβώς είναι το schema και πόσο χρήσιμο είναι για την εύκολη διαχείριση αλλά και ασφάλεια των δεδομένων μιας PostgreSQL βάσης.

Στο pgAdmin4 περιβάλλον, το οποίο χρησιμοποιούμε για να εκτελέσουμε τα Queries, μπορούμε να δούμε με γραφικό τρόπο ότι οι πίνακες της βάσης Northwind βρίσκονται κάτω από ένα schema με το όνομα public.

Σαν ορισμός, ένα schema είναι ένα namespace δηλαδή ένα όνομα μέσα στο οποίο περιέχονται όλα εκείνα τα αντικείμενα που μπορούμε να δημιουργήσουμε σε μια PostgreSQL βάση όπως tables, procedures, views, functions, κτλ. Για αρχή λοιπόν θα μπορούσατε να το δείτε σαν ένα τρόπο οργάνωσης των αντικειμένων της βάσης. Η βάση Northwind περιέχει ένα σύνολο από πίνακες που όμως όλοι αυτοί βρίσκονται μέσα στην κατηγορία tables μέσα στο schema public.

To schema public το δημιουργεί η PostgreSQL από μόνη της κάθε φορά που δημιουργούμε μια καινούργια βάση. Όμως ο λόγος ύπαρξης ενός schema είναι αποκλειστικά και μόνο για την οργάνωση αντικειμένων? Όχι, ο δεύτερος και πιο σημαντικός λόγος είναι ότι μέσω του schema μπορούμε να ελέγξουμε την πρόσβαση στα δεδομένα, στους πίνακες και γενικότερα ποιος έχει πρόσβαση σε τι. Ας τα πάρουμε όμως τα πράγματα από την αρχή.

Για να κάνουμε login στην PostgreSQL και κατά συνέπεια στην Northwind βάση χρησιμοποιούμε τον χρήστη με το όνομα postgres. Αφού τώρα ο χρήστης έχει κάνει login στην βάση, θα πρέπει εμείς να ορίσουμε το είδος πρόσβασης που θα έχει στα δεδομένα. Ο συγκεκριμένος χρήστης, μιας και θεωρείται και superuser, έχει πρόσβαση παντού. Όμως αυτή η πρόσβαση στους πίνακες της Northwind δεν προέρχεται από την ιδιότητα του superuser μόνο αλλά από το γεγονός ότι έχει πρόσβαση στο schema public. Αυτό μπορούμε να το επιβεβαιώσουμε αν κάνουμε δεξί κλικ επάνω στο schema public και επιλέξουμε properties. Η καρτέλα που θα εμφανιστεί μας παρουσιάζει τα δικαιώματα του postgres χρήστη στο schema public.

Οπότε, για να ανακεφαλαιώσουμε, έχουμε έναν χρήστη με το όνομα postgres ο οποίος εφόσον έχει δικαιώματα πρόσβασης στο schema public μπορεί να δει τους πίνακες της βάσης Northwind.

Το schema public είναι το default για όλα τα queries που εκτελούμε στην βάση μας. Μάλιστα, αν κάνετε δεξί κλικ επάνω στον πίνακα employees και επιλέξετε View/Edit Data -> All Rows θα δείτε ότι το τελικό query που δημιουργείται περιέχει την λέξη public μπροστά από το όνομα του πίνακα.

Για μπορέσουμε λοιπόν να αποκτήσουμε πρόσβαση σε ένα αντικείμενο μέσα σε κάποιο schema, εφόσον έχουμε τα απαραίτητα δικαιώματα, θα πρέπει να ορίσουμε πρώτα το schema και μετά το όνομα του αντικειμένου.

schema_name.object_name

Μια βάση μπορεί να περιέχει πολλά schemas όμως κάθε schema ανήκει μόνο σε μια βάση. Αφού λοιπόν ένα schema μας βοηθάει να οργανώσουμε τα αντικείμενα της βάσης (και ας αναφερόμαστε πιο συγκεκριμένα στους πίνακες για τώρα) αυτό σημαίνει ότι αν είχαμε ένα δεύτερο schema με διαφορετικό όνομα φυσικά, θα μπορούσε να περιέχει και αυτό έναν πίνακα employees που ίσως είχε διαφορετικά δεδομένα από αυτόν του schema public. Αυτό θα ήταν απόλυτα σωστό γιατί η πρόσβαση σε κάθε πίνακα θα προσδιοριζόταν από το όνομα του schema.

Ας πειραματιστούμε λίγο με το schema για να μπορέσουμε να καταλάβουμε περισσότερο την αξία του. Για αρχή ας δημιουργήσουμε ένα καινούργιο χρήστη με username: test και password: test. Κάνουμε δεξί κλικ επάνω στο Login/Group Roles -> Create -> login/Group role

Στην General καρτέλα γράφουμε το όνομα του χρήστη: test

Στην δεύτερη καρτέλα με το όνομα Definition ορίζουμε το password να είναι test.

Στην τρίτη καρτέλα με το όνομα Privileges ορίζουμε τα δικαιώματα που μπορεί να έχει ο χρήστης. Δεν θέλουμε να έχει superuser δικαιώματα οπότε αφήνουμε τις περισσότερες επιλογές να είναι ΝΟ εκτός από την πρώτη που πρέπει να είναι YES για να μπορέσει ο χρήστης να κάνει login. Πατάμε Save.

Κάνουμε έναν γρήγορο έλεγχο για να βεβαιωθούμε ότι ο χρήστης έχει δημιουργηθεί σωστά. Το όνομα του χρήστη πρέπει να εμφανίζεται κάτω από το Login/Group Roles.

Ας δημιουργήσουμε τώρα ένα καινούργιο schema στην βάση μας. Κάνουμε δεξί κλικ επάνω στο εικονίδιο schemas -> create -> Schema

Ονομάζουμε το schema sales και δίνουμε δικαιώματα πρόσβασης στον test. Πατάμε Save.

Μην ξεχνάτε, ότι αυτή την στιγμή που κάνουμε όλες αυτές τις διαδικασίες, είμαστε σαν postgres superuser ενωμένοι στην βάση. Συνεχίζουμε τώρα να δημιουργήσουμε έναν καινούργιο πίνακα μέσα στο schema sales. Κάντε δεξί κλικ επάνω στα tables του sales -> Create – Table

Στην πρώτη καρτέλα γράφουμε staff για όνομα του πίνακα και επιλέγουμε test τον διαχειριστή του πίνακα ο οποίος βρίσκεται στο schema sales.

Πατάμε στην δεύτερη καρτέλα να ορίσουμε μερικές στήλες για τον πίνακα. Ακόμα δεν έχουμε μιλήσει για την δημιουργία αντικειμένων, όμως σαν γρήγορη αναφορά μπορούμε να πούμε ότι κάθε πίνακας περιέχει στήλη ή στήλες που η κάθε μια δέχεται συγκεκριμένο τύπο δεδομένων που ορίζεται από το data type της στήλης. Εδώ λοιπόν θα ορίσουμε τέσσερις στήλες που εκτός από την πρώτη που είναι το primary key όλες οι άλλες απλά δέχονται χαρακτήρες. Μην ανησυχείτε αν δεν καταλαβαίνετε την έννοια του Primary key, των στηλών ή των data type. Θα τα αναλύσουμε όλα σε μελλοντικές ενότητες. Για τώρα, το μόνο που πρέπει να καταλάβετε είναι ότι κάτω από το schema sales δημιουργούμε έναν πίνακα με το όνομα staff στον οποίο έχει πρόσβαση ο χρήστης test. Όταν το δικό σας configuration μοιάζει με αυτό που ακολουθεί πατήστε Save για να δημιουργηθεί ο πίνακας.

Χωρίς να κάνετε logout και ενώ ακόμα είσαστε συνδεδεμένοι σαν postgres ας γράψουμε το παρακάτω query:

Το μήνυμα μας λέει ότι δεν μπορεί να βρεθεί ο πίνακας staff. Ο λόγος? Πολύ απλά γιατί η default συμπεριφορά της PostgreSQL είναι να ψάχνει για αντικείμενα στο public schema. Αυτό μπορούμε εύκολα να το βεβαιώσουμε αν τρέξουμε το εξής query:

Οπότε για να μπορέσει ο χρήστης postgres να δει τα δεδομένα του πίνακα staff θα πρέπει να ορίσει και το schema μπροστά από τό όνομα του πίνακα.

Ο χρήστης postgres, έχει πρόσβαση σε όλα τα schemas και τους πίνακες τους αφού είναι ο κατά ουσία admin όλης της βάσης. Ας δούμε όμως τι μπορεί να κάνει ο test χρήστης τον οποίο ορίσαμε να έχει πρόσβαση μόνο στους πίνακες του schema sales.

Θα πρέπει να δημιουργήσουμε ένα καινούργιο connection με τα credentials του χρήστη test. Για να το πετύχουμε αυτό, κάνουμε δεξί κλικ επάνω στο εικονίδιο servers -> create -> Server

Στην πρώτη καρτέλα με το όνομα General, δίνουμε το όνομα test σαν όνομα του connection.

Στη δεύτερη καρτέλα με το όνομα Connection, ορίζουμε τα στοιχεία του χρήστη και πατάμε Save.

Τώρα από τα αριστερά θα παρατηρήσετε ότι δημιουργήθηκε ένα καινούργιο connection. Πατήστε μια φορά επάνω στη Northwind βάση για να την επιλέξετε και μετά πατήστε το εικονίδιο για τον query editor. Ότι query αιτήματα γράψετε θα εκτελεστούν κάτω από τον χρήστη test.

Για να δούμε τι περιέχει ο πίνακας staff πρέπει να γράψουμε:

Αν όμως προσπαθήσουμε να δούμε δεδομένα από το schema public εκεί η βάση θα μας το απαγορεύσει γιατί ο χρήστης ορίστηκε εξ αρχής να έχει δικαιώματα μόνο στο schema sales. Φυσικά μπορούμε αυτό να το αλλάξουμε ανά πάσα στιγμή κάνοντας δεξί κλικ επάνω στο schema public και δίνοντας δικαιώματα πρόσβασης στους πίνακες και στον χρήστη test. Το security των δεδομένων είναι κάτι που θα μας απασχολήσει αργότερα. Για τώρα ας δούμε απλά ότι ο χρήστης test δεν μπορεί να δει τους πίνακες από το schema public αν και βρίσκονται στην ίδια βάση.