数据库权限异常求助：调用write_match_history()提示权限拒绝

Let's break down why you're hitting this permission error even though d_write has direct access to the match_history table. Here are the most likely fixes to work through:

1. Fix Typos in Your Permission Grant Statements #

First, I spotted two critical typos in your SQL that are preventing some permissions from being applied correctly:

• SCHEME should be SCHEMA in the sequence grant command
• talbes should be tables in the final select grant command

These typos mean those two grant operations failed (either silently or with an error you might have missed). Re-run the corrected versions:

grant select on all SEQUENCES in SCHEMA d to d_write;
grant select on all tables in schema d to d_write;

2. Check the Function's Security Context #

PostgreSQL functions run with one of two security modes:

• SECURITY INVOKER (default): Uses the permissions of the user calling the function
• SECURITY DEFINER: Uses the permissions of the user who created the function

Even if d_write has direct table access, the function might be running under a different context. Run this query to check your function's setting:

SELECT proname, prosecurity
FROM pg_proc
WHERE proname = 'write_match_history' 
  AND pronamespace = 'd'::regnamespace;

• If it returns SECURITY DEFINER: Ensure the function's owner has full access to d.match_history. If you don't want to rely on the owner's permissions, alter the function to use SECURITY INVOKER (if it aligns with your business logic):

  ALTER FUNCTION d.write_match_history(a,b,c,d,e,f) SECURITY INVOKER;
  

• If it's already SECURITY INVOKER: Double-check that the function doesn't perform hidden operations (like triggering a separate function, accessing an ungranted sequence, or writing to another table) that d_write lacks permissions for.

3. Verify the User's Actual Permissions #

Log in as d_write and run these checks to confirm all required permissions are active:

-- Check insert/select access to the target table
SELECT has_table_privilege('d_write', 'd.match_history', 'insert');
SELECT has_table_privilege('d_write', 'd.match_history', 'select');

-- Check execute permission on the function
SELECT has_function_privilege('d_write', 'd.write_match_history(a,b,c,d,e,f)', 'execute');

-- Check schema usage access
SELECT has_schema_privilege('d_write', 'd', 'usage');

All of these should return t (true). If any return f, re-grant that specific permission.

4. Inspect the Function's Internal Logic #

Open the definition of write_match_history() and look for operations that might access objects beyond d.match_history. For example:

• Does it call another function that requires additional permissions?
• Does it use a sequence not covered by your all SEQUENCES grant?
• Is there a trigger on match_history that runs on insert, and that trigger function lacks permissions for d_write?

If you find such dependencies, grant the necessary permissions to d_write for those objects.

内容的提问来源于stack exchange，提问作者Narwhal