PostgreSQL:Grant

lastmod 2026-02-26created 2026-02-253 edits

Relationship between GRANT and RLS

In PostgreSQL, table access is controlled in two stages.

1. Stage 1: GRANT (table-level privileges)
   • "Can this role access this table?"
   • Without a GRANT, the role cannot access the table at all. It is like being stopped at the door.
2. Stage 2: RLS (row-level privileges)
   • "Among the roles allowed access, which rows can they view/modify?"
   • RLS is applied after passing GRANT. It decides which of the people who got through the door can enter which rooms.

Supabase role structure

• anon - users who are not logged in (access with the API key only)
• authenticated - logged-in users (auth.uid() exists)
• service_role - server-side administrator — bypasses RLS

If the table has no GRANT:

authenticated 사용자 → GRANT 없음 → 차단 (RLS까지 도달하지 못함)

If you add a GRANT:

authenticated 사용자 → GRANT 통과 → RLS 정책 평가 → 허용

Meaning of each privilege

  GRANT SELECT,   -- 조회 (SELECT 쿼리)
        INSERT,   -- 삽입 (다른 관리자 추가)
        UPDATE,   -- 수정
        DELETE    -- 삭제
  ON public.my_table -- 테이블 이름
  TO authenticated;  -- 로그인한 사용자 역할에게

Summary:

• RLS without GRANT → no one can access
• GRANT only, no RLS → every authenticated user can access all rows
• GRANT + RLS → only authenticated users who satisfy the policy can access

How to check a table's grants

  SELECT grantee, privilege_type
  FROM information_schema.role_table_grants
  WHERE table_schema = 'public'
    AND table_name = 'admins';

When there is no GRANT:

   grantee | privilege_type
  ---------+---------------
  (0 rows)

When there is a GRANT:

      grantee     | privilege_type
  ----------------+---------------
   authenticated  | SELECT
   authenticated  | INSERT
   authenticated  | UPDATE
   authenticated  | DELETE

Supabase's automatic GRANT

By default, Supabase sets the following default privileges on the public schema.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO anon, authenticated;

Because of this setting, creating a new table in public automatically applies GRANTs to anon and authenticated.

From a security standpoint, if there is no reason for non-logged-in users to access the table, it is better to remove the anon role's privileges.

REVOKE ALL ON public.admins FROM anon;

PUBLIC grantee

A virtual group meaning every role in PostgreSQL.

• GRANT to PUBLIC → every current and future role gets that privilege
• REVOKE from PUBLIC → removes that default privilege

When you create a function, PostgreSQL automatically applies "GRANT EXECUTE TO PUBLIC". That is why anyone, including anon, could run it unless it was explicitly REVOKEd.

EVOKE EXECUTE ON FUNCTION public.is_admin() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.is_admin() TO authenticated;