Sửa lỗi PostgreSQL ERROR: permission denied for table

TL;DR

Role của bạn chưa được cấp quyền trên table. Kết nối với tư cách superuser hoặc chủ sở hữu table rồi chạy:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table TO your_role;

Để cấp toàn quyền:

GRANT ALL PRIVILEGES ON TABLE your_table TO your_role;

Đảm bảo role cũng có quyền truy cập schema — đây là phần hay bị quên nhất:

GRANT USAGE ON SCHEMA public TO your_role;

Mô tả lỗi

ERROR: permission denied for table users

Lỗi này xảy ra khi một PostgreSQL role cố truy vấn hoặc chỉnh sửa table mà nó không có quyền. Bạn thường gặp lỗi này sau khi tạo app user mới, chuyển database, khôi phục từ dump, hoặc chạy migration bằng một role khác với role mà app thực sự dùng để kết nối.

Nguyên nhân

Mô hình phân quyền của PostgreSQL rất chặt chẽ. Mọi role đều bắt đầu với quyền truy cập table bằng không — kết nối được vào database không có nghĩa là bạn được làm gì bên trong đó. Sở hữu database không đồng nghĩa với việc sở hữu các object bên trong nó.

Các nguyên nhân phổ biến:

App user mới được tạo nhưng chưa được cấp quyền trên table
Table được tạo bởi postgres hoặc một migration user — app role của bạn không thể truy cập
Khôi phục từ pg_dump nhưng dump không bao gồm các câu lệnh GRANT
Thiếu quyền USAGE trên schema (PostgreSQL từ chối truy cập trước khi kiểm tra quyền trên table)

Bước 1: Kiểm tra quyền còn thiếu

Chạy lệnh sau với tư cách superuser để xem các grant hiện tại trên một table:

-- Trong psql
\dp your_table

-- Hoặc qua SQL
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'your_table';

Kiểm tra quyền truy cập schema:

SELECT nspname, nspacl
FROM pg_namespace
WHERE nspname = 'public';

Nếu role của bạn không có trong danh sách, đó chính là vấn đề.

Cách sửa 1: Cấp quyền trên table

Kết nối với tư cách chủ sở hữu table hoặc superuser, sau đó cấp các quyền cần thiết cho role:

-- Chỉ đọc
GRANT SELECT ON TABLE your_table TO your_role;

-- Đọc và ghi
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table TO your_role;

-- Tất cả table trong một schema cùng lúc
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_role;

Cách sửa 2: Cấp quyền USAGE trên schema

Chỉ cấp quyền ở cấp table thôi chưa đủ. Role còn cần quyền USAGE trên schema chứa table đó:

GRANT USAGE ON SCHEMA public TO your_role;

Bỏ qua bước này, PostgreSQL sẽ từ chối truy vấn trước khi kiểm tra quyền trên table — nhưng thông báo lỗi vẫn hiện permission denied for table. Sự không nhất quán này khiến việc thiếu quyền schema rất dễ bị bỏ sót.

Cách sửa 3: Cấp quyền truy cập sequence cho INSERT

Các table dùng cột serial, bigserial, hoặc GENERATED phụ thuộc vào một sequence bên dưới. Nếu không có quyền truy cập sequence, lệnh INSERT sẽ thất bại ngay cả sau khi đã cấp quyền trên table:

-- Sequence cụ thể
GRANT USAGE, SELECT ON SEQUENCE your_table_id_seq TO your_role;

-- Tất cả sequence trong schema
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO your_role;

Cách sửa 4: Default Privileges (Ngăn lỗi tái diễn)

Chán phải chạy GRANT sau mỗi lần migration? Default privileges giải quyết điều đó. Từ thời điểm này, các table và sequence mới sẽ tự động được cấp quyền:

-- Chạy lệnh này với tư cách role tạo table (ví dụ: migration user của bạn)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO your_role;

Lưu ý: những quyền này chỉ áp dụng cho các object được tạo sau khi chạy lệnh này. Chúng cũng chỉ có hiệu lực với role đã chạy lệnh. Ví dụ, nếu migration kết nối bằng postgres nhưng bạn chạy ALTER DEFAULT PRIVILEGES với tư cách app_admin — PostgreSQL sẽ không áp dụng quyền đó cho các table mới được tạo bởi postgres.

Script thiết lập đầy đủ

Đây là script hoàn chỉnh để chạy với tư cách superuser khi thêm một app role mới:

-- Kết nối vào đúng database trước
\c your_database

-- Quyền truy cập schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Các table hiện có
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Các sequence hiện có
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Các table và sequence trong tương lai (chạy với tư cách migration user)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

Kiểm tra kết quả

Chuyển sang role đó và kiểm tra trực tiếp:

-- Từ phiên superuser
SET ROLE your_role;
SELECT * FROM your_table LIMIT 1;
RESET ROLE;

Hoặc kết nối bằng role từ dòng lệnh:

psql -U your_role -d your_database -c "SELECT * FROM your_table LIMIT 1;"

Không có lỗi nghĩa là đã thành công.

Mẹo thêm

Trên Linux, PostgreSQL có hai lớp phân quyền riêng biệt. SQL privileges kiểm soát những gì role có thể làm bên trong database — tất cả những gì đề cập ở trên thuộc loại này. Còn quyền cấp file trên thư mục dữ liệu, socket file và pg_hba.conf được quản lý ở cấp hệ điều hành. Đối với phần đó, Unix Permissions Calculator trên ToolCraft rất tiện để tra cứu giá trị chmod mà không cần tính toán bằng số octal trong đầu.

Đang dùng pg_dump/pg_restore? Chỉ truyền --no-acl khi bạn cố ý muốn loại bỏ các grant khỏi dump. Nếu không truyền flag đó, pg_dump sẽ tự động bao gồm tất cả câu lệnh GRANT. Tuy nhiên — cờ --no-acl vô tình bị để lại trong pipeline restore là nguyên nhân đằng sau không ít sự cố kiểu "tại sao sau khi restore xong không có gì có quyền cả?".