Postgres Roles dan Privileges

Postgres Roles dan Privileges

Pendahuluan

Mengontrol akses ke data di Postgres sangat penting untuk keamanan data. Postgres menyediakan model permissions yang robust dan fleksibel bagi pengguna untuk mengelola akses ke data mereka. Model permissions ini didasarkan pada konsep object, privilege, dan role yang familiar, namun memiliki kehalusan yang harus dipahami oleh database administrator untuk menciptakan akses yang aman. Dalam artikel ini kita akan melihat secara detail bagaimana roles dan permissions bekerja di Postgres.

Konsep Dasar

Mari kita pahami terlebih dahulu beberapa konsep dasar yang akan digunakan di seluruh artikel ini.

Database Object

Database object adalah entitas apapun yang dibuat di dalam database. Tables, foreign tables, views, materialized views, types, domains, operators, functions, triggers, dan lain-lain adalah database objects. Objects memungkinkan operasi pada mereka yang bervariasi untuk setiap object. Misalnya, Anda dapat melakukan select data dari table dan Anda dapat mengeksekusi sebuah function.

Privilege

Privilege mengontrol operasi apa yang diizinkan untuk dijalankan pada database object. Misalnya, privilege select pada table mengontrol kemampuan untuk membaca data dari table tersebut. Demikian pula, privilege execute mengontrol kemampuan untuk mengeksekusi sebuah function. Privileges diberikan kepada roles. Sebuah role harus memiliki permission untuk operasi yang dilakukannya pada sebuah object.

Role

Role adalah user atau group. User adalah seseorang yang dapat login ke database. Group adalah kumpulan users untuk memudahkan pengelolaan privileges bagi users. Berbeda dengan user, group tidak dapat login ke database. Perbedaan antara user dan group sebagian besar tidak penting bagi Postgres karena keduanya adalah roles, tetapi tetap berguna untuk menganggap mereka sebagai konsep yang terpisah untuk kemudahan pemahaman.

Owner

Setiap database object memiliki owner. Owner memiliki kontrol penuh atas object tersebut. Mereka dapat memodifikasi atau menghapus object atau memberikan privileges kepada users dan groups lain. Ketika user membuat object baru, mereka menjadi owner dari object tersebut. Owner juga dapat mentransfer kepemilikan objects ke roles lain. Sebuah role tidak dapat dihapus sebelum semua kepemilikan objects yang dimilikinya ditransfer ke role lain.

Dengan konsep dasar ini sudah didefinisikan, mari kita lihat model permissions di Postgres secara mendalam. Sisa artikel ini akan lebih seperti tutorial, jadi Anda dapat mengikutinya. Saya akan menggunakan hosted Supabase project, tetapi Anda bebas menggunakan instalasi Postgres apapun.

Setting Up

Buat Supabase project baru (atau gunakan yang sudah ada) dan salin connection string URI-nya dari halaman Database Settings. URI-nya terlihat seperti berikut:

postgres://[USER].[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@[REGION-SUBDOMAIN]/postgres

Di mana USER adalah user untuk koneksi, YOUR-PROJECT-REF adalah string yang secara unik mengidentifikasi project Anda, YOUR-PASSWORD adalah password database untuk user USER, dan REGION-SUBDOMAIN adalah subdomain di mana database Anda di-host.

Gunakan command line tool psql untuk terhubung ke database:

➜ psql postgres://postgres.[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@[REGION-SUBDOMAIN]/postgres

Setelah terhubung, konfirmasi bahwa Anda terhubung sebagai user postgres dengan menjalankan command select current_role:

# as postgres
postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ postgres     │
└──────────────┘
(1 row)

Membuat Roles

Sekarang, mari kita buat dua users bernama junior_dev dan senior_dev.

Database role dapat dibuat dengan command create role. Karena user adalah role yang dapat login, gunakan parameter login:

# as postgres
postgres=> create role junior_dev login password 'a long and secure password';
CREATE ROLE
postgres=> create role senior_dev login password 'another long and secure password';
CREATE ROLE

Anda sekarang dapat mengkonfirmasi bahwa users junior_dev dan senior_dev dapat login ke database:

➜ psql postgres://junior_dev.[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@[REGION-SUBDOMAIN]/postgres

postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ junior_dev   │
└──────────────┘
(1 row)

postgres=> exit

➜ psql postgres://senior_dev.[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@[REGION-SUBDOMAIN]/postgres

postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ senior_dev   │
└──────────────┘
(1 row)

Untuk sisa artikel ini, buka tiga terminal dan login masing-masing dengan junior_dev, senior_dev, dan postgres untuk dengan mudah beralih di antara mereka. Setiap command yang dieksekusi akan mencantumkan di awal user yang harus mengeksekusinya, misalnya:

# as junior_dev
postgres=> this command should be executed from the junior_dev user

Membuat Objects dan Memberikan Privileges

Mari kita coba membuat table sebagai junior_dev:

# as junior_dev
postgres=> create table public.apps(id serial primary key, name text);
ERROR: permission denied for schema public
LINE 1: create table public.apps(id serial primary key, name text);
                     ^

Apa yang terjadi? Error permission denied for schema public memberitahu kita bahwa junior_dev tidak memiliki beberapa permission pada schema public. Kita dapat memeriksa permissions yang ada pada schema menggunakan command \dn+ <schema> di psql:

# as junior_dev
postgres=> \dn+ public
List of schemas
┌────────┬───────────────────┬──────────────────────────────────────┐
│ Name   │ Owner             │ Access privileges                    │
├────────┼───────────────────┼──────────────────────────────────────┤
│ public │ pg_database_owner │ pg_database_owner=UC/pg_database_owner│
│        │                   │ =U/pg_database_owner                 │
│        │                   │ postgres=U/pg_database_owner         │
│        │                   │ anon=U/pg_database_owner             │
│        │                   │ authenticated=U/pg_database_owner    │
│        │                   │ service_role=U/pg_database_owner     │
└────────┴───────────────────┴──────────────────────────────────────┘
(1 row)

Memang, kolom Access privileges tidak mencantumkan role junior_dev di mana pun, yang berarti ia tidak memiliki permission apapun pada schema public. Bagaimana kita memperbaiki ini? User postgres di Supabase hosted databases adalah role yang powerful dengan lebih banyak privileges daripada banyak roles lainnya. Anggap role postgres sebagai role admin, meskipun ia bukan superuser. Kita dapat menggunakan role ini untuk memberikan permissions yang sesuai.

Jadi, mari kita beralih ke koneksi user postgres dan berikan junior_dev permission untuk membuat objects di schema public. Format umum dari command grant adalah grant <privilege> on <object> to <role>. Anda dapat berkonsultasi dengan halaman privileges di dokumentasi Postgres untuk mengetahui nama privilege yang benar.

# as postgres
postgres=> grant create on schema public to junior_dev;
GRANT

Mari kita periksa permissions lagi:

# as junior_dev
postgres=> \dn+ public
List of schemas
┌────────┬───────────────────┬──────────────────────────────────────┐
│ Name   │ Owner             │ Access privileges                    │
├────────┼───────────────────┼──────────────────────────────────────┤
│ public │ pg_database_owner │ pg_database_owner=UC/pg_database_owner│
│        │                   │ =U/pg_database_owner                 │
│        │                   │ postgres=U/pg_database_owner         │
│        │                   │ anon=U/pg_database_owner             │
│        │                   │ authenticated=U/pg_database_owner    │
│        │                   │ service_role=U/pg_database_owner     │
│        │                   │ junior_dev=C/pg_database_owner       │
└────────┴───────────────────┴──────────────────────────────────────┘
(1 row)

Kali ini kita melihat baris baru di kolom access privileges: junior_dev=C/pg_database_owner

💡 Grantor dalam kasus di atas adalah pg_database_owner yang merupakan role yang memiliki schema public. pg_database_owner memiliki owner dari database saat ini sebagai satu-satunya member, yaitu postgres dalam kasus kita.

Sekarang mari kita coba membuat table lagi:

# as junior_dev
postgres=> create table public.apps(id serial primary key, name text);
CREATE TABLE

Mari kita masukkan beberapa data ke dalamnya:

# as junior_dev
postgres=> insert into public.apps(name) values ('next app');
INSERT 0 1
postgres=> select * from public.apps;
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│  1 │ next app │
└────┴──────────┘
(1 row)

Sekarang beralih ke senior_dev dan coba select data dari table:

# as senior_dev
postgres=> select * from public.apps;
ERROR: permission denied for table apps

senior_dev tidak dapat select data dari table public.apps. Mari kita debug error permissions seperti sebelumnya. Command di psql untuk melihat table permissions adalah \dp <tablename>:

# as senior_dev
postgres=> \dp public.apps
Access privileges
┌────────┬──────┬───────┬───────────────────┬──────────────────┐
│ Schema │ Name │ Type  │ Access privileges │ Column privileges│
├────────┼──────┼───────┼───────────────────┼──────────────────┤
│ public │ apps │ table │                   │                  │
└────────┴──────┴───────┴───────────────────┴──────────────────┘
(1 row)

Tidak ada access privileges sama sekali. Seperti yang kita lakukan sebelumnya, mari kita beralih ke user postgres dan perbaiki permissions. Halaman privileges memberitahu kita bahwa kita perlu memberikan privilege select kepada senior_dev agar mereka dapat select data dari table public.apps:

# as postgres
postgres=> grant select on table public.apps to senior_dev;
ERROR: permission denied for table apps

Mengapa postgres tidak dapat memberikan privilege select? Karena ia bukan owner, juga tidak memiliki access privileges apapun pada table tersebut. Tetapi bagaimana junior_dev dapat select data dari table? Itu karena junior_dev adalah owner dari table:

# as postgres
postgres=> \dt public.apps
List of relations
┌────────┬──────┬───────┬────────────┐
│ Schema │ Name │ Type  │ Owner      │
├────────┼──────┼───────┼────────────┤
│ public │ apps │ table │ junior_dev │
└────────┴──────┴───────┴────────────┘
(1 row)

Karena owner memiliki semua privileges pada sebuah object, junior_dev dapat select data. junior_dev juga dapat memberikan privileges pada objects yang dimiliki kepada roles lain. Mari kita perbaiki permissions dengan junior_dev:

# as junior_dev
postgres=> grant select on public.apps to senior_dev;
GRANT

Sekarang senior_dev dapat select data:

# as senior_dev
postgres=> select * from public.apps;
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│  1 │ next app │
└────┴──────────┘
(1 row)

Grant Options

Opsi lain dalam contoh di atas adalah bagi junior_dev untuk memberikan privilege untuk memberikan privilege select kepada role postgres. Role postgres kemudian akan dapat memberikan privilege select kepada senior_dev. Untuk mencoba ini, mari kita revoke privilege yang sebelumnya diberikan kepada senior_dev terlebih dahulu:

# as junior_dev
postgres=> revoke select on public.apps from senior_dev;
REVOKE

Dan kemudian berikan privilege select dengan with grant option kepada postgres:

# as junior_dev
postgres=> grant select on public.apps to postgres with grant option;
GRANT

Sekarang, jika kita melihat permissions pada table public.apps:

# as junior_dev
postgres=> \dp public.apps
Access privileges
┌────────┬──────┬───────┬────────────────────────────────┐
│ Schema │ Name │ Type  │ Access privileges              │
├────────┼──────┼───────┼────────────────────────────────┤
│ public │ apps │ table │ junior_dev=arwdDxt/junior_dev  │
│        │      │       │ postgres=r*/junior_dev         │
└────────┴──────┴───────┴────────────────────────────────┘
(1 row)

Perhatikan * setelah r di postgres=r*/junior_dev, yang menunjukkan bahwa permission select diberikan dengan with grant option. Sekarang postgres dapat memberikan privilege select kepada senior_dev:

# as postgres
postgres=> grant select on table public.apps to senior_dev;
GRANT

Dan senior_dev memiliki privilege select dan dapat select dari table lagi:

# as senior_dev
postgres=> \dp public.apps
Access privileges
┌────────┬──────┬───────┬────────────────────────────────┐
│ Schema │ Name │ Type  │ Access privileges              │
├────────┼──────┼───────┼────────────────────────────────┤
│ public │ apps │ table │ junior_dev=arwdDxt/junior_dev  │
│        │      │       │ postgres=r*/junior_dev         │
│        │      │       │ senior_dev=r/postgres          │
└────────┴──────┴───────┴────────────────────────────────┘
(1 row)

postgres=> select * from public.apps;
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│  1 │ next app │
└────┴──────────┘
(1 row)

Command grant hanya menambahkan privileges untuk objects yang sudah ada. Bagaimana jika kita ingin memberikan privileges tertentu pada objects segera setelah dibuat? Di situlah default access privileges berperan.

Default Access Privileges

Jika junior_dev sekarang membuat table lain, ia harus memberikan privileges lagi kepada senior_dev. Untuk menghindari melakukan ini setiap kali junior_dev membuat table baru, kita dapat mengubah default access privileges milik junior_dev. Pertama mari kita lihat default privileges saat ini pada schema public:

# as junior_dev
postgres=> \ddp public
Default access privileges
┌────────────────┬────────┬──────────┬────────────────────────────┐
│ Owner          │ Schema │ Type     │ Access privileges          │
├────────────────┼────────┼──────────┼────────────────────────────┤
│ postgres       │ public │ function │ postgres=X/postgres        │
│                │        │          │ anon=X/postgres            │
│                │        │          │ authenticated=X/postgres   │
│                │        │          │ service_role=X/postgres    │
│ postgres       │ public │ sequence │ postgres=rwU/postgres      │
│                │        │          │ anon=rwU/postgres          │
│                │        │          │ authenticated=rwU/postgres │
│                │        │          │ service_role=rwU/postgres  │
│ postgres       │ public │ table    │ postgres=arwdDxt/postgres  │
│                │        │          │ anon=arwdDxt/postgres      │
│                │        │          │ authenticated=arwdDxt/postgres│
│                │        │          │ service_role=arwdDxt/postgres│
│ supabase_admin │ public │ function │ postgres=X/supabase_admin  │
│                │        │          │ anon=X/supabase_admin      │
│                │        │          │ authenticated=X/supabase_admin│
│                │        │          │ service_role=X/supabase_admin│
│ supabase_admin │ public │ sequence │ postgres=rwU/supabase_admin│
│                │        │          │ anon=rwU/supabase_admin    │
│                │        │          │ authenticated=rwU/supabase_admin│
│                │        │          │ service_role=rwU/supabase_admin│
│ supabase_admin │ public │ table    │ postgres=arwdDxt/supabase_admin│
│                │        │          │ anon=arwdDxt/supabase_admin│
│                │        │          │ authenticated=arwdDxt/supabase_admin│
│                │        │          │ service_role=arwdDxt/supabase_admin│
└────────────────┴────────┴──────────┴────────────────────────────┘
(6 rows)

Baik junior_dev maupun senior_dev tidak terdaftar. Mari kita ubah default privileges milik junior_dev:

# as junior_dev
postgres=> alter default privileges in schema public grant select on tables to senior_dev;
ALTER DEFAULT PRIVILEGES

Di sini kita mengubah default privileges sedemikian rupa sehingga setiap kali junior_dev membuat table baru di schema public, senior_dev harus diberikan privilege select padanya. Mari kita periksa privileges lagi:

# as junior_dev
postgres=> \ddp public
Default access privileges
┌────────────────┬────────┬──────────┬────────────────────────────┐
│ Owner          │ Schema │ Type     │ Access privileges          │
├────────────────┼────────┼──────────┼────────────────────────────┤
│ junior_dev     │ public │ table    │ senior_dev=r/junior_dev    │
│ postgres       │ public │ function │ postgres=X/postgres        │
│                │        │          │ anon=X/postgres            │
│                │        │          │ authenticated=X/postgres   │
│                │        │          │ service_role=X/postgres    │
│ postgres       │ public │ sequence │ postgres=rwU/postgres      │
│                │        │          │ anon=rwU/postgres          │
│                │        │          │ authenticated=rwU/postgres │
│                │        │          │ service_role=rwU/postgres  │
│ postgres       │ public │ table    │ postgres=arwdDxt/postgres  │
│                │        │          │ anon=arwdDxt/postgres      │
│                │        │          │ authenticated=arwdDxt/postgres│
│                │        │          │ service_role=arwdDxt/postgres│
│ supabase_admin │ public │ function │ postgres=X/supabase_admin  │
│                │        │          │ anon=X/supabase_admin      │
│                │        │          │ authenticated=X/supabase_admin│
│                │        │          │ service_role=X/supabase_admin│
│ supabase_admin │ public │ sequence │ postgres=rwU/supabase_admin│
│                │        │          │ anon=rwU/supabase_admin    │
│                │        │          │ authenticated=rwU/supabase_admin│
│                │        │          │ service_role=rwU/supabase_admin│
│ supabase_admin │ public │ table    │ postgres=arwdDxt/supabase_admin│
│                │        │          │ anon=arwdDxt/supabase_admin│
│                │        │          │ authenticated=arwdDxt/supabase_admin│
│                │        │          │ service_role=arwdDxt/supabase_admin│
└────────────────┴────────┴──────────┴────────────────────────────┘
(7 rows)

Baris pertama sekarang menunjukkan default access privilege yang baru saja kita tambahkan. Mari kita buat table baru dan masukkan sebuah row ke dalamnya:

# as junior_dev
postgres=> create table public.users(id serial primary key, name text);
CREATE TABLE
postgres=> insert into public.users(name) values ('john doe');
INSERT 0 1

Sekarang coba select data di public.users dari senior_dev:

# as senior_dev
postgres=> select * from public.users;
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│  1 │ john doe │
└────┴──────────┘
(1 row)

Perhatikan bahwa kita langsung dapat select data dari public.users tanpa grants eksplisit dari junior_dev.

Jelas dari contoh di atas bahwa owner memiliki semua privileges pada sebuah object yang dapat mereka berikan kepada roles lain. Tetapi bisa menjadi merepotkan bagi owner untuk terus memberikan privileges yang sama kepada setiap role baru. Ada cara yang lebih baik. Kita dapat memastikan bahwa objects dimiliki oleh group dan kemudian users mana pun yang memerlukan akses ke objects tersebut diberi keanggotaan ke group. Mari kita lihat bagaimana ini bekerja.

Membuat Groups

Kita ingin membuat group developers baru yang akan memiliki table public.apps. Kemudian kita akan menjadikan junior_dev dan senior_dev sebagai members dari group developers. Ini akan memastikan bahwa mereka berdua memiliki jenis akses yang sama, tanpa secara eksplisit memberikan privileges setelah membuat object baru.

Pertama, mari kita drop table public.apps:

# as junior_dev
postgres=> drop table public.apps;
DROP TABLE

Mari kita juga revoke privilege create dari junior_dev pada schema public:

# as postgres
postgres=> revoke create on schema public from junior_dev;
REVOKE

Mari kita buat group developers. Karena group adalah role yang tidak diizinkan untuk login, gunakan parameter nologin:

# as postgres
postgres=> create role developers nologin;
CREATE ROLE

Anda tidak dapat login dengan role developers karena kita mengatur parameter nologin. Parameter login / nologin mengontrol attribute login dari sebuah role. Sebelumnya kita juga mengatur attribute password dari roles junior_dev dan senior_dev. Ada banyak role attributes lain yang akan kita bicarakan nanti di artikel ini.

Mari kita berikan privilege create kepada group developers:

# as postgres
postgres=> grant create on schema public to developers;
GRANT

Karena users junior_dev dan senior_dev tidak memiliki privilege create pada schema public, mereka tidak dapat membuat objects di dalamnya. Group developers bisa, tetapi kita tidak dapat login dengannya. Jadi bagaimana kita membuat public.apps yang dimiliki oleh developers? Nah, user dapat sementara menyamar sebagai group jika mereka adalah member dari group tersebut. Jadi mari kita pastikan junior_dev dan senior_dev adalah members dari group developers:

# as postgres
postgres=> grant developers to junior_dev;
GRANT ROLE
postgres=> grant developers to senior_dev;
GRANT ROLE

Command grant <group> to <user> adalah varian lain dari command grant tetapi harus dibaca secara mental sebagai add <user> to <group>.

💡 Dalam bentuk command grant ini, Postgres tidak memeriksa bahwa <user> adalah user dan <group> adalah group. Artinya, Postgres tidak peduli dengan kemampuan login roles ini. Oleh karena itu, grant <user1> to <user2> juga diperbolehkan, dalam hal ini <user2> dapat menyamar sebagai <user1>. Faktanya, untuk sebagian besar, Postgres tidak terlalu peduli dengan perbedaan antara user atau group. Baginya, keduanya hanyalah roles.

Sekarang junior_dev (atau senior_dev) dapat menyamar sebagai developers:

# as junior_dev
postgres=> set role developers;
SET
postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ developers   │
└──────────────┘
(1 row)

Dan membuat table public.apps:

# as junior_dev
postgres=> create table public.apps(id serial primary key, name text);
CREATE TABLE

Yang dimiliki oleh group developers:

# as junior_dev
postgres=> \dt public.apps
List of relations
┌────────┬──────┬───────┬────────────┐
│ Schema │ Name │ Type  │ Owner      │
├────────┼──────┼───────┼────────────┤
│ public │ apps │ table │ developers │
└────────┴──────┴───────┴────────────┘
(1 row)

Sekarang jika Anda menghentikan penyamaran:

# as junior_dev
postgres=> reset role;
RESET
postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ junior_dev   │
└──────────────┘
(1 row)

Dan coba insert atau select data dari public.apps, itu berhasil:

# as junior_dev or senior_dev
postgres=> insert into public.apps(name) values ('next app');
INSERT 0 1
postgres=> select * from public.apps;
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│  1 │ next app │
└────┴──────────┘
(1 row)

Alasan junior_dev dan senior_dev dapat insert dan select data adalah karena mereka adalah bagian dari group developers. Jika developer baru dibuat nanti, mereka hanya tinggal menjalankan grant developers to <new dev> untuk memiliki akses yang sama seperti developer lainnya. Bandingkan ini dengan metode sebelumnya di mana user baru harus meminta owner dari setiap object untuk memberikan mereka permissions.

Opsi Grant Lanjutan

Menjadikan user sebagai bagian dari group lain mungkin memberikannya tiga kemampuan:

1. Kemampuan untuk menyamar sebagai group

2. Kemampuan untuk mewarisi permissions dari group

3. Kemampuan untuk menambah atau menghapus users lain dari group

Semua kemampuan ini dapat dikontrol secara independen saat menjalankan command grant <group> to <user> dengan menggunakan with <option name> true/false yang ditambahkan ke command tersebut. Nama dari masing-masing opsi di atas adalah set, inherit, dan admin. Misalnya, untuk melarang user menyamar sebagai group, jalankan grant <group> to <user> with set false.

💡 Di Postgres 15, hanya opsi admin yang dapat dikontrol. Di Postgres 16, opsi inherit dan set juga dapat dikontrol. Jika opsi-opsi ini dihilangkan dari command grant, nilai default mereka adalah true untuk set dan inherit dan false untuk admin.

Untuk mendemonstrasikan, jika kita mengaktifkan opsi admin pada junior_dev:

# as postgres
postgres=> grant developers to junior_dev with admin option;
GRANT ROLE

Ia akan dapat menghapus senior_dev dari group developers:

# as junior_dev
postgres=> revoke developers from senior_dev;
REVOKE ROLE

Tanpa opsi admin, junior_dev tidak akan dapat melakukan ini.

Role Attributes

Setiap role memiliki beberapa attributes yang terkait dengannya yang mengontrol perilaku role tersebut. Beberapa yang umum tercantum di bawah ini. Untuk daftar lengkap dan detailnya, lihat dokumentasi Postgres role attributes.

• login - mengontrol kemampuan role untuk login

• superuser - mengontrol apakah role adalah superuser atau tidak (lihat bagian selanjutnya untuk detail)

• createdb - mengontrol apakah role akan dapat membuat databases

• createrole - mengontrol apakah role akan dapat membuat roles lain

• replication - mengontrol apakah role dapat digunakan untuk memulai replication

• bypassrls - mengontrol apakah role dapat melewati row level security

• connection limit - membatasi jumlah maksimum koneksi yang dapat dibuat role ke database

• inherit - mengontrol apakah role dapat mewarisi permissions dari roles yang menjadi membernya

Special Roles

Ada dua special roles yang memainkan peran penting dalam bagaimana roles dan privileges dikelola.

Superuser

superuser adalah role dengan attribute superuser yang diatur. superuser seperti root user pada sistem operasi *nix. Ia sangat powerful dan melewati semua privilege checks kecuali autentikasi saat login. Untuk alasan ini, Anda harus menghindari bekerja dengan role ini sebanyak mungkin. Hanya superusers yang dapat membuat roles superuser lainnya.

Public

public adalah group role yang setiap role lainnya secara otomatis menjadi bagiannya. Hanya ada satu role public. Jadi berbeda dengan superuser, tidak ada attribute role public. Role public digunakan untuk menyediakan privileges yang dianggap sangat umum sehingga setiap role harus memilikinya. Privileges ini adalah:

• connect - kemampuan untuk terhubung ke database

• temporary - kemampuan untuk membuat temporary tables

• execute - kemampuan untuk mengeksekusi functions

• usage - kemampuan untuk menggunakan object seperti domain, language, atau type

Role public tidak dapat dihapus, tetapi privileges-nya dapat di-revoke.

Privileges dari sebuah role adalah union dari tiga set privileges:

1. Yang diberikan kepada role secara langsung

2. Yang diwarisi dari roles yang menjadi member eksplisit dari role ini

3. Yang diwarisi dari role public, yang setiap role secara implisit menjadi membernya

Privileges yang diwarisi dari role public adalah sumber kebingungan umum saat bekerja dengan roles di Postgres. Bayangkan bahwa kita ingin melarang junior_dev dari mengeksekusi functions. Mari kita buat sebuah function terlebih dahulu:

# as postgres
postgres=> create function add(integer, integer)
returns integer
as 'select $1 + $2;'
language sql;
CREATE FUNCTION

junior_dev saat ini dapat mengeksekusi function ini:

# as junior_dev
postgres=> select add(1, 2);
┌─────┐
│ add │
├─────┤
│   3 │
└─────┘
(1 row)

Sekarang mari kita revoke permission execute milik junior_dev:

# as postgres
postgres=> revoke execute on function add(integer, integer) from junior_dev;
REVOKE

Tetapi junior_dev masih dapat mengeksekusi function tersebut:

# as junior_dev
postgres=> select add(1, 2);
┌─────┐
│ add │
├─────┤
│   3 │
└─────┘
(1 row)

Bagaimana? Mari kita periksa privileges function add:

# as postgres
postgres=> \df+ add
┌────────┬──────┬──────────────────┬─────────────────────┬────────┬──────────┬─────────────────────┬─────────┬──────────┬─────────┐
│ Schema │ Name │ Result data type │ Argument data types │ Type   │ Volatility│ Parallel            │ Owner   │ Security │ Access  │
│        │      │                  │                     │        │           │                     │         │          │ privile │
├────────┼──────┼──────────────────┼─────────────────────┼────────┼──────────┼─────────────────────┼─────────┼──────────┼─────────┤
│ public │ add  │ integer          │ integer, integer    │ func   │ volatile  │ unsafe              │ postgres│ invoker  │ =X/post │
│        │      │                  │                     │        │           │                     │         │          │ gres    │
└────────┴──────┴──────────────────┴─────────────────────┴────────┴──────────┴─────────────────────┴─────────┴──────────┴─────────┘
(1 row)

junior_dev tidak memiliki privilege apapun, tetapi nama role yang hilang di baris =X/postgres berarti role public. Mari kita revoke execute dari public:

# as postgres
postgres=> revoke execute on function add(integer, integer) from public;
REVOKE
postgres=> \df+ add
┌────────┬──────┬──────────────────┬─────────────────────┬────────┬──────────┬─────────────────────┬─────────┬──────────┬─────────┐
│ Schema │ Name │ Result data type │ Argument data types │ Type   │ Volatility│ Parallel            │ Owner   │ Security │ Access  │
│        │      │                  │                     │        │           │                     │         │          │ privile │
├────────┼──────┼──────────────────┼─────────────────────┼────────┼──────────┼─────────────────────┼─────────┼──────────┼─────────┤
│ public │ add  │ integer          │ integer, integer    │ func   │ volatile  │ unsafe              │ postgres│ invoker  │         │
└────────┴──────┴──────────────────┴─────────────────────┴────────┴──────────┴─────────────────────┴─────────┴──────────┴─────────┘
(1 row)

Sekarang junior_dev tidak dapat lagi mengeksekusi function add:

# as junior_dev
postgres=> select add(1, 2);
ERROR: permission denied for function add

Hal lain yang perlu diperhatikan di sini adalah bahwa ketika kita me-revoke privilege execute pada add dari junior_dev, sebenarnya tidak ada yang perlu di-revoke. Tetapi Postgres tidak menunjukkan peringatan apapun kepada kita. Jadi penting untuk selalu memeriksa permissions secara eksplisit, terutama setelah command revoke.

Ringkasan

Untuk meringkas:

• Setiap database object memiliki owner

• Operasi pada database objects dikontrol oleh privileges

• Owners dapat memberikan privileges pada objects yang dimiliki kepada roles lain

• Roles dapat berupa users atau groups

• Roles dapat mewarisi permissions dari roles yang menjadi membernya

• Role public adalah role yang setiap role lainnya secara implisit menjadi membernya. Ia tidak dapat dihapus, tetapi privileges-nya dapat di-revoke

• Roles superuser adalah roles yang sangat powerful yang melewati semua privilege checks dan harus digunakan dengan hati-hati

• Command grant hanya memberikan privileges pada objects yang sudah ada

• Default privileges mengontrol privileges yang akan diberikan kepada objects yang dibuat di masa depan

Kesimpulan

Permissions Postgres mengikuti model objects, roles, privileges tradisional tetapi memiliki kehalusan yang dapat mengejutkan users kecuali mereka memahaminya secara detail. Dalam artikel ini kita bereksperimen dengan model ini untuk memahaminya secara mendalam. Semoga pemahaman ini akan memungkinkan Anda untuk mengelola dan melindungi database Postgres Anda dengan lebih efektif.

---

Tips Tambahan untuk Praktik Terbaik

Berikut beberapa tips praktis yang dapat Anda terapkan:

1. Gunakan Groups untuk Manajemen yang Lebih Baik

Selalu ciptakan groups untuk mengorganisir users dengan hak akses yang serupa. Ini membuat manajemen privileges jauh lebih mudah saat tim Anda berkembang.

2. Hindari Penggunaan Superuser

Hanya gunakan role superuser untuk tugas-tugas administratif yang benar-benar memerlukannya. Untuk operasi sehari-hari, gunakan roles dengan privileges yang lebih terbatas.

3. Audit Privileges Secara Berkala

Gunakan commands seperti \dp, \dn+, dan \ddp secara berkala untuk memeriksa permissions yang ada dan memastikan tidak ada akses yang tidak diinginkan.

4. Dokumentasikan Structure Roles Anda

Buat dokumentasi yang jelas tentang roles apa yang ada, apa purposes mereka, dan siapa yang menjadi members dari groups tertentu.

5. Gunakan Default Privileges dengan Bijak

Setting default privileges dapat menghemat banyak waktu, tetapi pastikan Anda memahami implikasinya. Default privileges yang terlalu permissive dapat menciptakan risiko keamanan.

6. Hati-hati dengan Role Public

Ingat bahwa semua roles secara otomatis adalah member dari role public. Periksa dan revoke privileges dari public yang tidak diperlukan untuk semua users.

7. Principle of Least Privilege

Selalu berikan privileges minimum yang diperlukan untuk sebuah role menjalankan tugasnya. Jangan memberikan lebih banyak akses dari yang dibutuhkan.

Dengan memahami dan menerapkan konsep-konsep ini, Anda akan dapat membuat sistem database Postgres yang aman dan mudah dikelola.

---

Baik, saya akan cek dulu apakah ada hal yang missing dari tutorial di atas, lalu membuat setup end-to-end untuk kasus e-commerce.

Hal Yang Missing dari Tutorial

Beberapa hal penting yang belum dibahas secara detail:

1. Row Level Security (RLS) - Sangat penting untuk multi-tenant applications

2. Schema Isolation - Memisahkan objects berdasarkan schema untuk organisasi yang lebih baik

3. Revoke Cascade - Bagaimana revoke mempengaruhi privileges yang di-grant dengan grant option

4. Ownership Transfer - Detail tentang ALTER ... OWNER TO

5. Connection Management - Membatasi koneksi per role

6. Password Policies - Praktek terbaik untuk password management

7. Audit Logging - Tracking siapa melakukan apa

Sekarang, mari kita buat setup end-to-end untuk perusahaan e-commerce!

---

Setup End-to-End: E-Commerce Company Role & Privileges Management

Skenario Bisnis

ShopKita adalah platform e-commerce dengan requirements:

• Multiple departments: Engineering, Analytics, Customer Support, Marketing

• Different access levels: Read-only, Read-Write, Admin

• Data segregation antara operational data dan analytics

• Audit trail untuk compliance

• Security yang ketat untuk data customer

Arsitektur Database

Database: shopkita_db
├── Schemas:
│   ├── public (default objects)
│   ├── products (catalog, inventory)
│   ├── orders (transactions, payments)
│   ├── customers (user data, addresses)
│   ├── analytics (reports, aggregations)
│   └── audit (logs, changes)

Step-by-Step Implementation

Step 1: Koneksi sebagai Superuser

psql -U postgres -d shopkita_db

Step 2: Setup Schemas

-- Sebagai postgres (superuser atau admin)

-- Buat schemas yang dibutuhkan
CREATE SCHEMA IF NOT EXISTS products;
CREATE SCHEMA IF NOT EXISTS orders;
CREATE SCHEMA IF NOT EXISTS customers;
CREATE SCHEMA IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS audit;

-- Revoke public access dari semua schema (security first!)
-- Secara default, setiap user di PostgreSQL bisa membaca atau bahkan membuat objek di schema public.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA products FROM PUBLIC;
REVOKE ALL ON SCHEMA orders FROM PUBLIC;
REVOKE ALL ON SCHEMA customers FROM PUBLIC;
REVOKE ALL ON SCHEMA analytics FROM PUBLIC;
REVOKE ALL ON SCHEMA audit FROM PUBLIC;

-- Revoke execute dari public untuk functions
-- kalau ada function baru dibuat di database, maka secara default semua user (PUBLIC) tidak boleh menjalankannya.
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

Step 3: Buat Group Roles

-- ==========================================
-- GROUP ROLES (nologin)
-- NOLOGIN berarti role ini tidak bisa dipakai login langsung ke database → hanya berfungsi sebagai container privileges (grup hak akses).
-- ==========================================

-- 1. Engineering Team
CREATE ROLE engineering_admin NOLOGIN;
CREATE ROLE engineering_dev NOLOGIN;
CREATE ROLE engineering_readonly NOLOGIN;

-- 2. Analytics Team
CREATE ROLE analytics_team NOLOGIN;

-- 3. Customer Support Team
CREATE ROLE support_team NOLOGIN;

-- 4. Marketing Team
CREATE ROLE marketing_team NOLOGIN;

-- 5. Application Roles
CREATE ROLE app_backend NOLOGIN;
CREATE ROLE app_frontend NOLOGIN;

-- dokumentasi internal di PostgreSQL → biar kalau orang lain cek role, bisa langsung tahu fungsinya.
COMMENT ON ROLE engineering_admin IS 'Full admin access untuk engineering team';
COMMENT ON ROLE engineering_dev IS 'Developer access - read/write operational data';
COMMENT ON ROLE engineering_readonly IS 'Read-only access untuk junior developers';
COMMENT ON ROLE analytics_team IS 'Read-only access untuk analytics queries';
COMMENT ON ROLE support_team IS 'Limited access untuk customer support';
COMMENT ON ROLE marketing_team IS 'Read-only access untuk marketing data';
COMMENT ON ROLE app_backend IS 'Backend application database user';
COMMENT ON ROLE app_frontend IS 'Frontend application database user';

Step 4: Setup Privileges untuk Groups

-- ==========================================
-- ENGINEERING ADMIN - Full Control
-- ==========================================
GRANT ALL PRIVILEGES ON SCHEMA products, orders, customers, analytics, audit TO engineering_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA products, orders, customers, analytics TO engineering_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA products, orders, customers, analytics TO engineering_admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA products, orders, customers, analytics TO engineering_admin;

-- Default privileges untuk objects yang akan dibuat
ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers, analytics
GRANT ALL ON TABLES TO engineering_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers, analytics
GRANT ALL ON SEQUENCES TO engineering_admin;

-- ==========================================
-- ENGINEERING DEV - Read/Write Operational
-- ==========================================

-- GRANT USAGE itu apa?
-- role boleh pakai schema → bisa referensi objek di dalamnya (tabel, view, function).
-- ⚠️ Tapi tidak otomatis boleh SELECT/INSERT ke tabel. Itu harus di-grant terpisah.
-- Contoh:
-- Kalau tanpa USAGE → meskipun sudah punya GRANT SELECT ON TABLE, user tetap tidak bisa query karena tidak boleh akses schema.
-- Kalau hanya USAGE tanpa SELECT → user bisa lihat schema ada, tapi tidak bisa baca tabel di dalamnya.

GRANT USAGE ON SCHEMA products, orders, customers TO engineering_dev;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA products, orders, customers TO engineering_dev;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA products, orders, customers TO engineering_dev;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA products, orders, customers TO engineering_dev;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO engineering_dev;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers
GRANT USAGE, SELECT ON SEQUENCES TO engineering_dev;

-- ==========================================
-- ENGINEERING READONLY - Read Only
-- ==========================================
GRANT USAGE ON SCHEMA products, orders, customers, analytics TO engineering_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA products, orders, customers, analytics TO engineering_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers, analytics
GRANT SELECT ON TABLES TO engineering_readonly;

-- ==========================================
-- ANALYTICS TEAM - Read Only + Analytics Schema
-- ==========================================
GRANT USAGE ON SCHEMA products, orders, customers, analytics TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA products, orders, customers TO analytics_team;
GRANT ALL PRIVILEGES ON SCHEMA analytics TO analytics_team;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA analytics TO analytics_team;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers
GRANT SELECT ON TABLES TO analytics_team;

ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT ALL ON TABLES TO analytics_team;

-- ==========================================
-- SUPPORT TEAM - Limited Access
-- ==========================================
GRANT USAGE ON SCHEMA customers, orders TO support_team;
GRANT SELECT ON ALL TABLES IN SCHEMA customers, orders TO support_team;
-- Support bisa update customer info dan order status saja
-- (akan dikonfigurasi lebih detail dengan RLS nanti)

ALTER DEFAULT PRIVILEGES IN SCHEMA customers, orders
GRANT SELECT ON TABLES TO support_team;

-- ==========================================
-- MARKETING TEAM - Read Only Marketing Data
-- ==========================================
GRANT USAGE ON SCHEMA customers, orders, analytics TO marketing_team;
GRANT SELECT ON ALL TABLES IN SCHEMA customers, orders, analytics TO marketing_team;

ALTER DEFAULT PRIVILEGES IN SCHEMA customers, orders, analytics
GRANT SELECT ON TABLES TO marketing_team;

-- ==========================================
-- APP BACKEND - Application Access
-- ==========================================
GRANT USAGE ON SCHEMA products, orders, customers TO app_backend;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA products, orders, customers TO app_backend;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA products, orders, customers TO app_backend;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA products, orders, customers TO app_backend;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;

ALTER DEFAULT PRIVILEGES IN SCHEMA products, orders, customers
GRANT USAGE, SELECT ON SEQUENCES TO app_backend;

-- ==========================================
-- APP FRONTEND - Limited Read Access
-- ==========================================
GRANT USAGE ON SCHEMA products TO app_frontend;
GRANT SELECT ON ALL TABLES IN SCHEMA products TO app_frontend;

ALTER DEFAULT PRIVILEGES IN SCHEMA products
GRANT SELECT ON TABLES TO app_frontend;

Step 5: Buat User Roles (Individual Users)

-- ==========================================
-- INDIVIDUAL USER ROLES (login enabled)
-- ==========================================

-- Engineering Team
CREATE ROLE budi_admin LOGIN PASSWORD 'secure_password_123!' 
  CONNECTION LIMIT 5
  VALID UNTIL '2026-12-31';
GRANT engineering_admin TO budi_admin;

CREATE ROLE siti_developer LOGIN PASSWORD 'secure_password_456!' 
  CONNECTION LIMIT 10;
GRANT engineering_dev TO siti_developer;

CREATE ROLE andi_junior LOGIN PASSWORD 'secure_password_789!' 
  CONNECTION LIMIT 5;
GRANT engineering_readonly TO andi_junior;

-- Analytics Team
CREATE ROLE dewi_analyst LOGIN PASSWORD 'analyst_pass_321!' 
  CONNECTION LIMIT 5;
GRANT analytics_team TO dewi_analyst;

CREATE ROLE rudi_data_scientist LOGIN PASSWORD 'ds_pass_654!' 
  CONNECTION LIMIT 3;
GRANT analytics_team TO rudi_data_scientist;

-- Support Team
CREATE ROLE linda_support LOGIN PASSWORD 'support_pass_987!' 
  CONNECTION LIMIT 10;
GRANT support_team TO linda_support;

-- Marketing Team
CREATE ROLE maya_marketing LOGIN PASSWORD 'marketing_pass_147!' 
  CONNECTION LIMIT 5;
GRANT marketing_team TO maya_marketing;

-- Application Users
CREATE ROLE app_api_user LOGIN PASSWORD 'super_secure_api_password!' 
  CONNECTION LIMIT 50;
GRANT app_backend TO app_api_user;

CREATE ROLE app_web_user LOGIN PASSWORD 'super_secure_web_password!' 
  CONNECTION LIMIT 100;
GRANT app_frontend TO app_web_user;

-- Set search_path untuk kemudahan
ALTER ROLE budi_admin SET search_path TO products, orders, customers, analytics, public;
ALTER ROLE siti_developer SET search_path TO products, orders, customers, public;
ALTER ROLE andi_junior SET search_path TO products, orders, customers, analytics, public;
ALTER ROLE dewi_analyst SET search_path TO analytics, products, orders, customers, public;
ALTER ROLE linda_support SET search_path TO customers, orders, public;
ALTER ROLE maya_marketing SET search_path TO analytics, customers, orders, public;
ALTER ROLE app_api_user SET search_path TO products, orders, customers, public;
ALTER ROLE app_web_user SET search_path TO products, public;

Step 6: Buat Sample Tables

-- ==========================================
-- PRODUCTS SCHEMA
-- ==========================================
SET ROLE budi_admin; -- Atau tetap sebagai postgres

CREATE TABLE products.categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products.products (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES products.categories(id),
  sku VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- CUSTOMERS SCHEMA
-- ==========================================
CREATE TABLE customers.customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  full_name VARCHAR(200) NOT NULL,
  phone VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers.addresses (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers.customers(id),
  address_type VARCHAR(20), -- 'shipping' or 'billing'
  street_address TEXT NOT NULL,
  city VARCHAR(100) NOT NULL,
  province VARCHAR(100) NOT NULL,
  postal_code VARCHAR(10),
  is_default BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- ORDERS SCHEMA
-- ==========================================
CREATE TABLE orders.orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers.customers(id),
  order_number VARCHAR(50) UNIQUE NOT NULL,
  status VARCHAR(20) DEFAULT 'pending', -- pending, processing, shipped, delivered, cancelled
  total_amount DECIMAL(12,2) NOT NULL,
  shipping_address_id INTEGER REFERENCES customers.addresses(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders.order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders.orders(id),
  product_id INTEGER REFERENCES products.products(id),
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL
);

CREATE TABLE orders.payments (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders.orders(id),
  payment_method VARCHAR(50) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  transaction_id VARCHAR(100),
  paid_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- ANALYTICS SCHEMA
-- ==========================================
CREATE TABLE analytics.daily_sales (
  date DATE PRIMARY KEY,
  total_orders INTEGER,
  total_revenue DECIMAL(15,2),
  total_customers INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE analytics.product_performance (
  product_id INTEGER,
  date DATE,
  views INTEGER DEFAULT 0,
  orders INTEGER DEFAULT 0,
  revenue DECIMAL(12,2) DEFAULT 0,
  PRIMARY KEY (product_id, date)
);

-- ==========================================
-- AUDIT SCHEMA
-- ==========================================
CREATE TABLE audit.user_activities (
  id SERIAL PRIMARY KEY,
  user_role VARCHAR(100) NOT NULL,
  action VARCHAR(50) NOT NULL,
  table_name VARCHAR(100),
  record_id INTEGER,
  old_values JSONB,
  new_values JSONB,
  ip_address INET,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reset role
RESET ROLE;

Step 7: Insert Sample Data

-- Set role yang memiliki write access
SET ROLE budi_admin;

-- Insert categories
INSERT INTO products.categories (name, description) VALUES
('Electronics', 'Electronic devices and accessories'),
('Fashion', 'Clothing and accessories'),
('Home & Living', 'Home decor and furniture');

-- Insert products
INSERT INTO products.products (category_id, sku, name, description, price, stock_quantity) VALUES
(1, 'ELEC-001', 'Smartphone X', 'Latest smartphone with amazing features', 5999000, 50),
(1, 'ELEC-002', 'Laptop Pro', 'High performance laptop', 15999000, 20),
(2, 'FASH-001', 'T-Shirt Premium', 'Comfortable cotton t-shirt', 150000, 200),
(3, 'HOME-001', 'Coffee Table', 'Modern minimalist coffee table', 1500000, 15);

-- Insert customers
INSERT INTO customers.customers (email, full_name, phone) VALUES
('[e-mail]', 'John Doe', '081234567890'),
('[e-mail]', 'Jane Smith', '081234567891'),
('[e-mail]', 'Bob Wilson', '081234567892');

-- Insert addresses
INSERT INTO customers.addresses (customer_id, address_type, street_address, city, province, postal_code, is_default) VALUES
(1, 'shipping', 'Jl. Sudirman No. 123', 'Jakarta', 'DKI Jakarta', '12345', true),
(2, 'shipping', 'Jl. Thamrin No. 456', 'Jakarta', 'DKI Jakarta', '12346', true);

-- Insert orders
INSERT INTO orders.orders (customer_id, order_number, status, total_amount, shipping_address_id) VALUES
(1, 'ORD-2025-001', 'delivered', 6149000, 1),
(2, 'ORD-2025-002', 'processing', 15999000, 2);

-- Insert order items
INSERT INTO orders.order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES
(1, 1, 1, 5999000, 5999000),
(1, 3, 1, 150000, 150000),
(2, 2, 1, 15999000, 15999000);

-- Insert payments
INSERT INTO orders.payments (order_id, payment_method, amount, status, transaction_id, paid_at) VALUES
(1, 'credit_card', 6149000, 'completed', 'TRX-001', '2025-01-15 10:30:00'),
(2, 'bank_transfer', 15999000, 'completed', 'TRX-002', '2025-01-16 14:20:00');

RESET ROLE;

Step 8: Setup Row Level Security (RLS)

-- ==========================================
-- ROW LEVEL SECURITY untuk Support Team
-- ==========================================
-- Support hanya bisa lihat orders dari 30 hari terakhir

ALTER TABLE orders.orders ENABLE ROW LEVEL SECURITY;

-- Policy untuk support team
CREATE POLICY support_recent_orders ON orders.orders
  FOR SELECT
  TO support_team
  USING (created_at >= CURRENT_DATE - INTERVAL '30 days');

-- Admin bisa lihat semua
CREATE POLICY admin_all_orders ON orders.orders
  FOR ALL
  TO engineering_admin
  USING (true);

-- Backend app bisa lihat semua
CREATE POLICY app_all_orders ON orders.orders
  FOR ALL
  TO app_backend
  USING (true);

-- ==========================================
-- RLS untuk Customers - PII Protection
-- ==========================================
ALTER TABLE customers.customers ENABLE ROW LEVEL SECURITY;

-- Marketing hanya bisa lihat data tertentu (tanpa email)
-- Ini akan dikombinasikan dengan view

CREATE VIEW analytics.customer_analytics AS
SELECT 
  id,
  full_name,
  created_at,
  CASE 
    WHEN CURRENT_USER IN (SELECT rolname FROM pg_roles WHERE rolname = 'marketing_team')
    THEN '[e-mail]'
    ELSE email
  END as email
FROM customers.customers;

GRANT SELECT ON analytics.customer_analytics TO marketing_team;

Step 9: Create Audit Functions

-- ==========================================
-- AUDIT TRIGGER FUNCTION
-- ==========================================
CREATE OR REPLACE FUNCTION audit.log_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit.user_activities (user_role, action, table_name, record_id, new_values)
    VALUES (CURRENT_USER, 'INSERT', TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, NEW.id, row_to_json(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit.user_activities (user_role, action, table_name, record_id, old_values, new_values)
    VALUES (CURRENT_USER, 'UPDATE', TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, NEW.id, row_to_json(OLD), row_to_json(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit.user_activities (user_role, action, table_name, record_id, old_values)
    VALUES (CURRENT_USER, 'DELETE', TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, OLD.id, row_to_json(OLD));
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Apply audit triggers ke tables penting
CREATE TRIGGER orders_audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON orders.orders
  FOR EACH ROW EXECUTE FUNCTION audit.log_changes();

CREATE TRIGGER customers_audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON customers.customers
  FOR EACH ROW EXECUTE FUNCTION audit.log_changes();

Step 10: Testing Access

-- ==========================================
-- TEST 1: Developer bisa read/write
-- ==========================================
-- Login sebagai siti_developer
SET ROLE siti_developer;

-- Harus berhasil
SELECT * FROM products.products LIMIT 5;
INSERT INTO products.categories (name, description) VALUES ('Books', 'Books and magazines');

-- Harus gagal (tidak punya akses ke analytics)
SELECT * FROM analytics.daily_sales; -- ERROR

RESET ROLE;

-- ==========================================
-- TEST 2: Readonly hanya bisa read
-- ==========================================
SET ROLE andi_junior;

-- Harus berhasil
SELECT * FROM products.products LIMIT 5;

-- Harus gagal
INSERT INTO products.categories (name) VALUES ('Test'); -- ERROR

RESET ROLE;

-- ==========================================
-- TEST 3: Analytics bisa read operational, write analytics
-- ==========================================
SET ROLE dewi_analyst;

-- Harus berhasil
SELECT * FROM orders.orders;
INSERT INTO analytics.daily_sales (date, total_orders, total_revenue, total_customers)
VALUES (CURRENT_DATE, 10, 50000000, 8);

-- Harus gagal
INSERT INTO products.products (sku, name, price) VALUES ('TEST', 'Test', 100); -- ERROR

RESET ROLE;

-- ==========================================
-- TEST 4: Support dengan RLS
-- ==========================================
SET ROLE linda_support;

-- Hanya lihat orders 30 hari terakhir
SELECT * FROM orders.orders;

RESET ROLE;

Step 11: Maintenance Scripts

-- ==========================================
-- SCRIPT: List semua users dan groups mereka
-- ==========================================
CREATE OR REPLACE FUNCTION audit.list_user_roles()
RETURNS TABLE (
  username TEXT,
  member_of TEXT[],
  can_login BOOLEAN,
  connection_limit INTEGER
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    r.rolname::TEXT,
    ARRAY_AGG(r2.rolname ORDER BY r2.rolname)::TEXT[],
    r.rolcanlogin,
    r.rolconnlimit
  FROM pg_roles r
  LEFT JOIN pg_auth_members m ON r.oid = m.member
  LEFT JOIN pg_roles r2 ON m.roleid = r2.oid
  WHERE r.rolname NOT LIKE 'pg_%'
    AND r.rolname != 'postgres'
  GROUP BY r.rolname, r.rolcanlogin, r.rolconnlimit
  ORDER BY r.rolname;
END;
$$ LANGUAGE plpgsql;

-- Jalankan untuk melihat struktur roles
SELECT * FROM audit.list_user_roles();

-- ==========================================
-- SCRIPT: List semua privileges per schema
-- ==========================================
CREATE OR REPLACE FUNCTION audit.list_schema_privileges(schema_name TEXT)
RETURNS TABLE (
  role_name TEXT,
  privilege_type TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    grantee::TEXT,
    privilege_type::TEXT
  FROM information_schema.schema_privileges
  WHERE schema_name = $1
  ORDER BY grantee, privilege_type;
END;
$$ LANGUAGE plpgsql;

-- Contoh penggunaan
SELECT * FROM audit.list_schema_privileges('products');

-- ==========================================
-- SCRIPT: Revoke user dan cleanup
-- ==========================================
CREATE OR REPLACE FUNCTION audit.remove_user(username TEXT)
RETURNS TEXT AS $$
DECLARE
  result TEXT;
BEGIN
  -- Revoke all group memberships
  EXECUTE format('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA products, orders, customers, analytics FROM %I', username);
  EXECUTE format('REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA products, orders, customers, analytics FROM %I', username);
  EXECUTE format('REASSIGN OWNED BY %I TO postgres', username);
  EXECUTE format('DROP OWNED BY %I', username);
  EXECUTE format('DROP ROLE IF EXISTS %I', username);
  
  result := format('User %s successfully removed', username);
  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Contoh: Hapus user
-- SELECT audit.remove_user('andi_junior');

Step 12: Monitoring dan Reporting

-- ==========================================
-- VIEW: Active Connections per Role
-- ==========================================
CREATE OR REPLACE VIEW audit.active_connections AS
SELECT 
  usename as username,
  COUNT(*) as connection_count,
  MAX(backend_start) as last_connection,
  array_agg(DISTINCT client_addr::TEXT) as client_ips
FROM pg_stat_activity
WHERE usename IS NOT NULL
GROUP BY usename
ORDER BY connection_count DESC;

-- ==========================================
-- VIEW: Recent User Activities
-- ==========================================
CREATE OR REPLACE VIEW audit.recent_activities AS
SELECT 
  user_role,
  action,
  table_name,
  COUNT(*) as action_count,
  MAX(created_at) as last_action
FROM audit.user_activities
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY user_role, action, table_name
ORDER BY last_action DESC;

-- Grant access ke admin
GRANT SELECT ON audit.active_connections TO engineering_admin;
GRANT SELECT ON audit.recent_activities TO engineering_admin;

Step 13: Documentation Template

-- ==========================================
-- GENERATE ROLE DOCUMENTATION
-- ==========================================
CREATE OR REPLACE FUNCTION audit.generate_role_documentation()
RETURNS TABLE (
  role_category TEXT,
  role_name TEXT,
  description TEXT,
  can_login BOOLEAN,
  schemas_access TEXT,
  privileges TEXT
) AS $$
BEGIN
  RETURN QUERY
  WITH role_info AS (
    SELECT 
      r.rolname,
      r.rolcanlogin,
      obj_description(r.oid, 'pg_authid') as role_desc
    FROM pg_roles r
    WHERE r.rolname NOT LIKE 'pg_%'
      AND r.rolname != 'postgres'
  )
  SELECT 
    CASE 
      WHEN ri.rolname LIKE '%_admin' THEN 'Admin'
      WHEN ri.rolname LIKE '%_dev%' THEN 'Developer'
      WHEN ri.rolname LIKE '%_readonly' THEN 'Read Only'
      WHEN ri.rolname LIKE '%_team' THEN 'Team Group'
      WHEN ri.rolname LIKE 'app_%' THEN 'Application'
      ELSE 'Other'
    END::TEXT as role_category,
    ri.rolname::TEXT,
    COALESCE(ri.role_desc, 'No description')::TEXT,
    ri.rolcanlogin,
    string_agg(DISTINCT sp.schema_name, ', ')::TEXT as schemas,
    string_agg(DISTINCT sp.privilege_type, ', ')::TEXT as privileges
  FROM role_info ri
  LEFT JOIN information_schema.schema_privileges sp ON ri.rolname = sp.grantee
  GROUP BY ri.rolname, ri.rolcanlogin, ri.role_desc
  ORDER BY role_category, ri.rolname;
END;
$$ LANGUAGE plpgsql;

-- Generate documentation
SELECT * FROM audit.generate_role_documentation();

Checklist Implementasi

☑ Setup database schemas
☑ Create group roles (teams)
☑ Configure privileges per group
☑ Create individual user accounts
☑ Assign users to groups
☑ Create tables dengan proper ownership
☑ Setup Row Level Security (RLS)
☑ Implement audit logging
☑ Create monitoring views
☑ Test all access levels
☑ Document all roles dan privileges
☑ Setup maintenance procedures

Best Practices yang Diterapkan

1. Principle of Least Privilege: Setiap role hanya mendapat akses minimal yang dibutuhkan

2. Schema Segregation: Data dipisah berdasarkan domain untuk organisasi yang lebih baik

3. Group-based Management: Menggunakan groups untuk memudahkan management user

4. Audit Trail: Semua perubahan penting di-log untuk compliance

5. Row Level Security: Protection tambahan untuk data sensitif

6. Connection Limits: Mencegah resource exhaustion

7. Password Expiry: Keamanan tambahan dengan valid until

8. Default Privileges: Otomatis apply privileges ke objects baru

9. Documentation: Self-documenting dengan comments dan views

Operasional Harian

Menambah Developer Baru

CREATE ROLE new_developer LOGIN PASSWORD 'secure_pass';
GRANT engineering_dev TO new_developer;
ALTER ROLE new_developer SET search_path TO products, orders, customers, public;

Promote Developer ke Admin

REVOKE engineering_dev FROM siti_developer;
GRANT engineering_admin TO siti_developer;

Temporary Access untuk Vendor

CREATE ROLE vendor_temp LOGIN PASSWORD 'temp_pass' VALID UNTIL '2025-02-01';
GRANT engineering_readonly TO vendor_temp;

Check User Activity

SELECT * FROM audit.recent_activities WHERE user_role = 'siti_developer';

Ini adalah setup yang comprehensive dan production-ready untuk e-commerce company!