When working with PostgreSQL foreign key constraints, you might encounter the frustrating "permission denied for schema public" error. This typically occurs when:

ERROR:  permission denied for schema public
LINE 1: SELECT 1 FROM ONLY "public"."category" x WHERE "category_id"...

The error appears because PostgreSQL needs to verify foreign key references during INSERT operations, but the current user lacks proper permissions on the referenced tables.

In your case, the issue stems from:

• The table category_google_taxonomy is owned by user 'testing'
• The referenced table category is owned by user 'super'
• The INSERT operation requires SELECT permission on the referenced table

Here's how to properly fix this:

-- Grant USAGE on the schema (if not already granted)
GRANT USAGE ON SCHEMA public TO testing;

-- Grant necessary permissions on the referenced table
GRANT SELECT ON TABLE public.category TO testing;

-- For write operations, you might also need:
GRANT REFERENCES ON TABLE public.category TO testing;

After applying these grants, check the permissions:

-- Check table privileges
\dp category

-- Expected output should show testing has SELECT privilege

If you have admin privileges, you can modify the table ownership:

-- Change ownership of both tables to the same user
ALTER TABLE category_google_taxonomy OWNER TO super;
-- OR
ALTER TABLE category OWNER TO testing;

To avoid such issues in production:

• Create a dedicated role for your application
• Grant consistent permissions across related tables
• Use schema-specific roles for better security

-- Example of role setup
CREATE ROLE app_user;
GRANT CONNECT ON DATABASE testing TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

When working with PostgreSQL foreign key constraints, you might encounter the frustrating "permission denied for schema public" error. This typically occurs when attempting to INSERT into a table that has foreign key references to other tables.

The error appears because PostgreSQL needs to verify the existence of referenced rows during INSERT operations. This verification requires SELECT permission on the referenced tables, not just the table you're inserting into.

-- Error occurs during this implicit check:
SELECT 1 FROM ONLY "public"."category" x 
WHERE "category_id" OPERATOR(pg_catalog.=) $1 
FOR SHARE OF x

To properly resolve this, you need to grant several permissions:

-- 1. Grant schema usage
GRANT USAGE ON SCHEMA public TO your_user;

-- 2. Grant permissions on referenced tables
GRANT SELECT (category_id) ON category TO your_user;
GRANT SELECT (google_taxonomy_id) ON google_taxonomy TO your_user;

-- 3. Optionally grant all privileges if needed
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user;

After applying the grants, verify the permissions:

-- Check table permissions
\dp category
\dp google_taxonomy

-- Check schema privileges
\dn+ public

If your tables are in different schemas, the solution requires additional grants:

-- For tables in schema1 referencing schema2
GRANT USAGE ON SCHEMA schema2 TO your_user;
GRANT SELECT ON schema2.referenced_table TO your_user;

1. Avoid using superuser accounts for regular operations
2. Create specific roles for your application
3. Grant only necessary permissions
4. Consider using row-level security for sensitive data

If you still encounter problems after granting permissions:

-- Check effective privileges
SELECT * FROM information_schema.role_table_grants 
WHERE grantee = 'your_user';

-- Verify the search_path
SHOW search_path;