POJOK ADMIN DALAM SQL

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 sugiana 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 file kunci 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 memberikan GRANT / 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, dan RULE.

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 (pemberian GRANT), 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, gunakan REVOKE:

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;