Error
Error Code:
2F004
PostgreSQL Error 2F004: SQL Data Read Not Permitted
Description
This error indicates that an SQL routine, such as a function or trigger, attempted to read data from the database in a context where it is explicitly disallowed. It commonly occurs when a function's declared volatility (e.g., IMMUTABLE) contradicts its actual behavior of performing data queries.
Error Message
reading sql data not permitted
Known Causes
3 known causesIncorrect Function Volatility
A function is declared as IMMUTABLE or STABLE but performs SQL SELECT operations, violating its declared purity and expected behavior.
Restricted Security Context
A SECURITY DEFINER function or other routine attempts to read data in an environment where such operations are explicitly disallowed for security reasons.
Trigger Data Access Violations
A trigger function tries to read data from other tables in a specific trigger context where PostgreSQL restricts such read operations.
Solutions
3 solutions available1. Grant SELECT Privilege on the Table easy
Ensure the user has explicit permission to read data from the affected table.
1
Connect to your PostgreSQL database as a superuser or a user with GRANT privileges.
2
Execute the following SQL command, replacing `your_user`, `your_schema`, and `your_table` with the actual user, schema, and table names.
GRANT SELECT ON your_schema.your_table TO your_user;3
If the error occurs for multiple tables within a schema, you can grant SELECT on all tables in that schema.
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO your_user;2. Review Row-Level Security (RLS) Policies medium
Check and adjust Row-Level Security policies if they are preventing data access.
1
Connect to your PostgreSQL database.
2
List all RLS policies for the affected table.
SELECT * FROM pg_policies WHERE tablename = 'your_table' AND schemaname = 'your_schema';3
Examine the `qual` column for each policy. If a policy has a `qual` that is always false or incorrectly restricts access for `your_user`, you may need to modify or drop it.
4
To modify a policy (example: allowing access for a specific user role):
ALTER POLICY policy_name ON your_schema.your_table USING (current_user = 'your_user' OR current_role = 'your_role');5
To drop a policy (use with caution):
DROP POLICY policy_name ON your_schema.your_table;3. Verify User and Role Membership easy
Ensure the user attempting to read data is part of the correct role that has been granted privileges.
1
Connect to your PostgreSQL database.
2
Check the roles assigned to the user.
SELECT r.rolname FROM pg_roles r JOIN pg_auth_members am ON am.member = r.oid JOIN pg_roles m ON m.oid = am.roleid WHERE r.rolname = 'your_user';3
If the user is not part of the necessary role (e.g., a role that has been granted SELECT on the table), add them.
GRANT role_name TO your_user;