Kepiye tata cara roles lan privileges ing PostgreSQL?

Question

Accepted Answer

PostgreSQL ngatur kontrol akses liwat **roles** (sing berfungsi minangka users lan groups) lan **privileges** (permissions sing diwenehake kanggo objects). Sistem iki ngontrol sapa sing bisa nyambung, lan apa sing bisa dilakokake — dhasar saka keamanan database.

## Roles — users lan groups sing terpadu

```sql
-- a role can be a USER (can log in) or a GROUP (collection of permissions)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';   -- a login role (a "user")
CREATE ROLE readonly;                                 -- a group role (no login)

-- roles can be members of other roles (inherit their privileges)
GRANT readonly TO app_user;          -- app_user inherits readonly's privileges
```

Ing Postgres, **role** yaiku konsep sing terpadu — bisa login (ndadi user) lan/utawa ngandhut roles liyane (ndadi group). Model fleksibel iki nangani users lan permission groups.

## Privileges — wewenang permissions kanggo objects

```sql
-- grant specific privileges on objects
GRANT SELECT ON users TO readonly;                    -- read-only on a table
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;  -- full data access
GRANT USAGE ON SCHEMA sales TO app_user;              -- access a schema
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;

REVOKE INSERT ON orders FROM app_user;                -- take away a privilege
```

Privileges (`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `USAGE`, lsp.) diwenehake kanggo objects (tables, schemas, databases) menyang roles — ngontrol apa persis sing bisa dilakokake saben role.

## Prinsip least privilege

```sql
-- ✅ grant only the permissions each role actually needs
GRANT SELECT ON reports TO analyst;     -- analyst can only READ reports
-- ❌ don't grant ALL PRIVILEGES or superuser broadly — minimize the blast radius
```

**Least privilege** — wewenang saben role mung permissions sing diperlokake — yaiku praktek keamanan inti sing mbatesi karusakan saka credentials sing dikompromikan utawa kesalahan.

## Default privileges lan ownership

```sql
-- the object's OWNER has full control; new objects need explicit grants
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO readonly;   -- auto-grant on FUTURE tables
```

## Apa sing penting

Negeri marang roles lan privileges PostgreSQL penting kanggo **keamanan database** — ngontrol sapa sing bisa akses database lan apa sing bisa dilakokake yaiku dhasar kanggo proteksi data, dadi ilmu iki berharga kanggo basis data produksi.

Negeri marang konsep **role** (model terpadu PostgreSQL kang roles ndadi users — sing bisa login — lan groups — koleksyon permissions sing roles liyane warisi) perlu kanggo ngatur akses, amarga kuwi cara nggawekake database users lan ngorganisir permissions.

Negeri marang cara grant lan revoke **privileges** (`SELECT`, `INSERT`, lsp.) kanggo objects (tables, schemas, databases) menyang roles kudu kanggo ngontrol apa persis sing bisa dilakokake saben user utawa aplikasi.

Sing kritis penting yaiku **prinsip least privilege** — wewenang saben role mung permissions sing bener-bener diperlokake (conto: role analyst sing baca-bae, role aplikasi kanthi akses sing diperlokake) tinimbang permissions luas utawa superuser — yaiku praktek keamanan inti sing mbatesi karusakan saka credentials sing dikompromikan, bugs, utawa kesalahan (pertahanan sing nyata lan penting).

Negeri marang default privileges lan ownership (owner object duwe kontrol lengkap; objects mendatang kabutuhen grant eksplisit) ngrampungake manajemen akses praktis.

Amarga keamanan database kritis (basis data ngandhut data sensitif, berharga), lan amarga kontrol akses sing tepat liwat roles lan least-privilege grants yaiku cara proteksi data lan mbatesi risiko, negeri marang roles lan privileges PostgreSQL — model role terpadu, grant/revoke privileges, lan luwih utama prinsip least-privilege — yaiku ilmu keamanan relevan kanggo administrasi lan proteksi basis data produksi, tanggung jawab sering relevan lan topik umum kanggo sapa sing ngatur akses database.