PostgreSQL

คำสั่งที่มีค่าในรูปแบบ <...> เป็น placeholder ให้แทนด้วยค่าจริงก่อนนำไปรัน

User

-- สร้าง user
CREATE USER <username> WITH PASSWORD '<password>';

-- ลบ user
DROP USER <username>;

-- เปลี่ยนรหัสผ่าน
ALTER USER <username> WITH PASSWORD '<new_password>';

CREATE ROLE <readonly_role>;
GRANT USAGE ON SCHEMA public TO <readonly_role>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <readonly_role>;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <readonly_role>;

CREATE USER <username> WITH PASSWORD '<password>';

GRANT <readonly_role> TO <username>;

Role Types

-- Read Only Role
CREATE ROLE <readonly_role>;


DROP ROLE <role_name>;


GRANT CONNECT ON DATABASE <database> TO <readonly_role>;
-- รันภายใต้การ connect database เท่านั้น
GRANT USAGE ON SCHEMA public TO <readonly_role>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <readonly_role>;

-- Read/Write Role
CREATE ROLE <readwrite_role>;
GRANT CONNECT ON DATABASE <database> TO <readwrite_role>;
GRANT USAGE ON SCHEMA public TO <readwrite_role>;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <readwrite_role>;

-- Admin Role
CREATE ROLE <admin_role> WITH SUPERUSER CREATEDB CREATEROLE;

GRANT <readonly_role> TO <username>;

Grant / Revoke Privileges

-- ให้สิทธิ์ connect เข้า database
GRANT CONNECT ON DATABASE <database> TO <username>;

-- ให้สิทธิ์ใช้งาน schema
GRANT USAGE ON SCHEMA public TO <username>;

-- ให้สิทธิ์อ่านตารางทั้งหมดใน schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <username>;

-- ให้สิทธิ์แก้ไขตารางทั้งหมด (อ่าน/เขียน)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>;

-- ยกเลิกสิทธิ์
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM <username>;

Default Privileges

-- กำหนด default privileges สำหรับ object ที่จะถูกสร้างใหม่ใน schema public
-- ให้ role <readonly_role> มีสิทธิ์ SELECT (อ่านข้อมูลได้) บน TABLES ที่สร้างใหม่โดยอัตโนมัติ
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <readonly_role>;

SELECT n.nspname AS schema,
       r.rolname AS grantor,
       d.defaclobjtype AS object_type,
       d.defaclacl AS privileges
FROM pg_default_acl d
JOIN pg_roles r ON r.oid = d.defaclrole
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace;

-- กำหนด default privileges สำหรับ object ที่จะถูกสร้างใหม่ใน schema public
-- ให้ role <readwrite_role> มีสิทธิ์ SELECT, INSERT, UPDATE, DELETE (อ่าน/เขียน/แก้ไข/ลบ)
-- บน TABLES ที่สร้างใหม่โดยอัตโนมัติ
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <readwrite_role>;

Useful

-- ดู users ทั้งหมด
\du
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication, rolcanlogin
FROM pg_roles;

-- ดู สิทธิในตาราง
\dp
SELECT n.nspname AS schema,
       c.relname AS name,
       c.relkind AS type,
       c.relacl,
       pg_catalog.array_to_string(c.relacl, E'\n') AS access_privileges
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','p')  -- r = table, v = view, S = sequence ฯลฯ
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;


-- ดูสิทธิ์ในตาราง
\z
\z <table_name>

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication, rolcanlogin
FROM pg_roles;

SELECT r.rolname,
       r.rolsuper,
       r.rolinherit,
       r.rolcreaterole,
       r.rolcreatedb,
       r.rolcanlogin,
       r.rolreplication,
       ARRAY(
         SELECT b.rolname
         FROM pg_auth_members m
         JOIN pg_roles b ON (m.roleid = b.oid)
         WHERE m.member = r.oid
       ) AS memberof
FROM pg_roles r
ORDER BY r.rolname;

-- เปลี่ยนเจ้าของตาราง/DB
ALTER TABLE <table_name> OWNER TO <username>;
ALTER DATABASE <database> OWNER TO <username>;

Workaround: สร้าง User และกำหนดสิทธิ์ Database

ตัวอย่างขั้นตอนสำหรับสร้าง user ให้ application ใช้งาน database โดยแทน placeholder ใน <...> ให้เป็นค่าจริงก่อนรัน SQL (เช่น <database> เป็น my_database) คำสั่งสร้าง user และ role ให้รันด้วย account ที่มีสิทธิ์จัดการ role

Full access (อ่าน/เขียน)

1. สร้าง role และ user แล้วอนุญาตให้เชื่อมต่อ database

-- สร้าง role สำหรับสิทธิ์อ่านและเขียน
CREATE ROLE <app_full_role> NOLOGIN;

-- สร้าง user สำหรับ application
CREATE USER <app_user> WITH PASSWORD '<app_password>';

-- ให้ user รับสิทธิ์จาก role แบบ full access
GRANT <app_full_role> TO <app_user>;

-- อนุญาตให้เชื่อมต่อ database
GRANT CONNECT ON DATABASE <database> TO <app_full_role>;

2. เชื่อมต่อเข้า database แล้วกำหนดสิทธิ์ใน schema public

-- อนุญาตให้ใช้งาน schema
GRANT USAGE ON SCHEMA public TO <app_full_role>;

-- ให้สิทธิ์อ่าน เพิ่ม แก้ไข และลบข้อมูลในตารางที่มีอยู่
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO <app_full_role>;

-- ให้สิทธิ์ใช้งาน sequence ที่มีอยู่ เช่น sequence ของ SERIAL
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA public
TO <app_full_role>;

-- ให้สิทธิ์กับตารางที่สร้างใหม่โดย object owner
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <app_full_role>;

-- ให้สิทธิ์กับ sequence ที่สร้างใหม่โดย object owner
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO <app_full_role>;

ถ้าต้องการให้ migration role สร้าง table หรือ object ใหม่ใน schema public ให้เพิ่มสิทธิ์นี้:

-- อนุญาตให้ migration role ใช้งาน schema และสร้าง object ใหม่
GRANT USAGE, CREATE ON SCHEMA public TO <migration_role>;

สิทธิ์ CREATE บน schema ใช้สร้าง table ได้ แต่ PostgreSQL ไม่มี privilege แยกสำหรับ DROP table; การ DROP ต้องมี ownership ของ table นั้น หาก migration ต้องลบ table ที่สร้างโดย role อื่น ให้รัน migration ในฐานะ owner หรือ SET ROLE เป็น <table_owner> ก่อน

Read-only

1. สร้าง role และ user แล้วอนุญาตให้เชื่อมต่อ database

-- สร้าง role สำหรับอ่านข้อมูลเท่านั้น
CREATE ROLE <reader_role> NOLOGIN;

-- สร้าง user สำหรับการอ่านข้อมูล
CREATE USER <reader_user> WITH PASSWORD '<reader_password>';

-- ให้ user รับสิทธิ์จาก role แบบ read-only
GRANT <reader_role> TO <reader_user>;

-- อนุญาตให้เชื่อมต่อ database
GRANT CONNECT ON DATABASE <database> TO <reader_role>;

2. เชื่อมต่อเข้า database แล้วกำหนดสิทธิ์อ่านใน schema public

-- อนุญาตให้ใช้งาน schema
GRANT USAGE ON SCHEMA public TO <reader_role>;

-- ให้สิทธิ์อ่านตารางและ view ที่มีอยู่
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <reader_role>;

-- ให้สิทธิ์อ่านตารางและ view ที่สร้างใหม่โดย object owner
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO <reader_role>;

ALTER DEFAULT PRIVILEGES มีผลเฉพาะ object ที่สร้างโดย role ซึ่งรันคำสั่งนี้ หากมีหลาย role เป็นผู้สร้างตาราง ให้รันคำสั่ง ALTER DEFAULT PRIVILEGES FOR ROLE ... แยกตามแต่ละ owner