Topik ini membahas hal-hal yang berkaitan dengan pekerjaan seorang administrator seperti menambah user, user group, security, penyelamatan data, atau optimasi.
1 User
Pemberian hak kepada user lain dapat diberikan oleh user postgres.
$ psql -u template1
Username: postgres
Password:
template1=# CREATE USER fendy WITH PASSWORD ’a’ CREATEDB;
Gunakan \h untuk keterangan lebih lengkap perintah CREATE USER ini.
template1=# \h CREATE USER
Command: CREATE USER
Description: Creates a new database user
Syntax:
CREATE USER username
[ WITH
[ SYSID uid ]
[ PASSWORD ’password’ ] ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ IN GROUP groupname [, ...] ]
[ VALID UNTIL ’abstime’ ]
Berikut ini ada beberapa contoh perintah yang berkaitan dengan user:
Buat user tanto dan masukkan dalam grup dokter:
CREATE USER tanto PASSWORD ’a’ IN GROUP dokter;
Buat user kopra dan masukkan dalam grup apotek serta tu_medis:
CREATE USER kopra PASSWORD ’b’ IN GROUP apotek, tu_medis;
Setiap user diperkenankan mengubah password dengan cara:
ALTER USER kopra WITH PASSWORD ’c’;
Perubahan password dengan perintah SQL di atas hanya berfungsi jika file konfigurasi pg_hba.conf terkandung option password.
2 Grup
User group adalah kumpulan user PostgreSQL. Pengelompokan ini berguna untuk kemudahan pemberian otoritas (GRANT/REVOKE).
CREATE GROUP dokter;
Bisa juga memasukkan user dalam suatu grup dengan cara seperti ini:
ALTER GROUP dokter ADD USER tanto, kopra, kosud, dewi;
Menghapus user dari suatu grup:
ALTER GROUP dokter DROP USER dewi;
3 Perlindungan Konektivitas
Postgres meletakkan kebijaksanaan security-nya di file pg_hba.conf. 1
Formatnya adalah:
host DBNAME IP_ADDRESS ADDRESS_MASK USERAUTH [AUTH_ARGUMENT]
Secara default, Postgres membiarkan setiap user untuk login tanpa diharuskan mengisikan password:
local all trust host all 127.0.0.1 255.255.255.255 trust
Agar Postgres dapat diakses dari remote host dengan IP 192.168.1.x maka Anda perlu menambahkan baris berikut ini pada pg_hba.conf :
host template1 192.168.1.0 255.255.255.0 ident sameuser
Baris di atas berarti client manapun dengan IP Address 192.168.1.x dapat mengakses template1 tanpa proses otorisasi (option sameuser).
Gunakan option password dimana proses otorisasi akan diterapkan.
host all 192.168.1.0 255.255.255.0 password
3.1 Plain Text Password
Untuk security yang lebih ketat lagi gunakan plain text untuk menyimpan password:
host all 192.168.1.0 255.255.255.0 password kunci
Contoh di atas menunjukkan bahwa user dari host dengan alamat IP 192.168.1.x boleh terkoneksi ke server Postgres dengan kewajiban mengisikan username dan password yang terdapat dalam file kunci yang terletak di home directory
user Postgres.
$ cd /usr/local/pgsql/data
$ pg_passwd kunci
Username: owo
New password:
Re-enter new password:
Karena direktori dan file-nya milik user postgres maka setiap perubahan password hanya dapat dilakukan oleh user postgres ini. Password yang tertulis dalam filekunci akan di-encrypt agar lebih terjaga kerahasiannya.
4 Perlindungan Data
Sistem perlindungan terhadap data dilakukan pada level tabel dan bukan pada aplikasi, yaitu dengan cara pemberian hak (GRANT) atau pencabutan hak (REVOKE) pada perintah SQL. Sehingga perubahan hak tidak akan mempengaruhi kode program. Selain superuser postgres, yang berhak memberikanGRANT / REVOKE adalah user yang membuat database bersangkutan.
Ketika tabel dibuat, hanya pemiliknya yang dapat menggunakan. Jika user lain diperbolehkan juga, maka pembuatnya harus memberikan GRANT. Hak akses yang dapat diberikan adalah SELECT, INSERT, UPDATE, DELETE, danRULE.
CREATE TABLE test_izin (id INT);
Selama tabel tersebut belum diberikan hak apapun pada user lain, maka hanya pembuatnya yang dapat melakukan perubahan terhadap tabel tersebut.
GRANT SELECT ON test_izin TO anjas;
Namun sekali suatu tabel tercatat dapat diakses oleh user lain (pemberianGRANT), maka pembuatnya sendiri harus mendapat GRANT pula. Bila di-GRANT-kan ke grup:
GRANT ALL ON test_izin TO GROUP dokter;
ALL berarti seluruh hak akses diperbolehkan. Untuk mencabutnya, gunakanREVOKE:
REVOKE ALL ON test_izin FROM GROUP dokter;
Gunakan user PUBLIC jika Anda bermaksud meng-GRANT / REVOKE suatu tabel bagi seluruh user.
GRANT SELECT ON test_izin TO PUBLIC;
5 Backup
Seluruh objek (tabel, isi tabel, view, stored procedures, dll) dalam suatu database dapat “dikeluarkan” (baca: dibackup) dalam bentuk SQL script.
$ pg_dump -u -f rab.sql rab
rab.sql adalah nama output file, sedangkan rab adalah nama database. Untuk restore dapat menggunakan psql:
$ psql -u -f rab.sql rab
Atau bisa juga lewat prompt psql dengan perintah \i:
$ psql -u rab
Username: sugiana
Password:
rab=> \i rab.sql
Sebelum restore tentunya Anda harus memastikan bahwa database rab kosong, setidaknya tidak berisi objek yang akan di-restore. Mengosongkan database tidak harus dengan menghapus (DROP) tabel satu per satu, melainkan bisa dengan menghapus database dan membuatnya kembali. Jika aplikasi Anda memanfaatkan field OID yang dimiliki Postgres gunakan option -o agar nilai-nilai OID tidak
berubah:
$ pg_dump -o -u -f rab.sql rab
5.1 Dump Lewat Direktori
Perpindahan database dapat juga dilakukan dengan operasi file dan direktori. Hal seperti ini kadang dipakai manakala sistem crash dan kita belum sempat melakukan proses dump. Postgres menyimpan setiap database ke dalam suatu direktori.
Setiap objeknya (seperti tabel) disimpan dalam sebuah file. Secara default seluruh direktori tersebut tersimpan dalam /var/lib/pgsql/base untuk RedHat dan /var/lib/pgsql/data untuk SuSE, atau jika Postgres hasil kompilasi dari source biasanya diletakkan di /usr/local/pgsql/data.Jadi jika Anda ingin mem-backup
seluruh database maka Anda dapat melakukannya dengan utility kompresi biasa seperti tar.
$ cd /usr/local/pgsql
$ tar cfz backup.tgz data
Setelah server Postgres pulih, Anda tinggal meng-ekstrak-nya ke tempat semula :
$ cd /usr/local/pgsql
$ tar xfz backup.tgz
Perlu Anda ketahui bahwa Postgres menyimpan informasi seluruh nama database dalam database template1 pada tabel pg_database. Jadi meski Anda telah meng-copy direktori database ke tempat yang sudah ditentukan namun belum mendaftarkannya ke tabel tersebut maka database tersebut tetap saja belum bisa diakses. Perlu sedikit tindakan
pengakalan untuk mendaftarkannya.
INSERT INTO pg_database
VALUES(’rab’,100,’rab’);
Tentu saja tindakan di atas tidak perlu dilakukan jika direktori yang Anda restore termasuk direktori template1 dari sistem sebelumnya.
5.2 Seluruh Database
Sebuah sistem Postgres bisa terdiri dari beberapa database. Pada saat upgrade Anda tentu perlu mem-backup seluruh database tersebut. Untunglah postgres telah menyiapkan utility pg_dumpall untuk masalah ini:
$ pg_dumpall > db.out
Setelah diupgrade, lakukan restore.
$ psql -e template1 < db.out
6 Cleaning-up
Aktivitas query biasanya meninggalkan “sampah” dalam direktori database. Postgres menyediakan perintah VACUUM untuk membuang garbage yang tak perlu tersebut.
VACUUM;
Atau gunakan VACUUM ANALYZE untuk mempercepat hasil query karena perintah ini membuat statistik yang akan dimanfaatkan oleh query optimizer.
VACUUM ANALYZE
