Postgres Privileges issue

Question

I am having issues setting up a new user in postgres, and part of it is probably my knowledge of how things work behind the scenes in postgres. I'm using TestBox and Lucee to run unit tests and everything works as expected under the postgres superuser, but after created a new user and granted privileges, the unit test fails

Here is my code:

CREATE USER test_user WITH PASSWORD 'postgres';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA not_public TO test_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO test_user;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA public TO test_user;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA not_public TO test_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO test_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA not_public TO test_user;

The Unit test runs a procedure from the public schema that references tables in the not_public schema, but errors out immediately with the following message:

 ERROR: permission denied for schema not_public Where: PL/pgSQL function p_unit__delete_test_production() line 18 at SQL statement

I ran the following for the new user/grantee:

SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges

and all tables for all schemas referenced in the procedure have select, insert, update, delete privileges. Is there a step I'm missing in my grants? Is this an issue with something else? Any help is appreciated!

Answer 1

Try

GRANT USAGE ON SCHEMA not_public TO test_user;

Note that the error message permission denied for schema not_public is on the schema itself and not on a specific object inside the schema.

Also GRANT ... ON ALL ... IN SCHEMA... works only on objects that already exist in that schema, which is a common cause of confusion with postgres' GRANT commands.