PROBLEM:

We have granted select privilege on one table of a schema to another user. Even after that the user was getting permission denied error while selecting data.

— Privilege was granted like this

PRIMDB=# grant all privileges on table SCOTT.SERVER_LOAD_INFO to prim_user;
GRANT

Now connect to prim_user and run the select statement:

psql -d PRIMDB -U prim_user

PRIMDB=> select * from SCOTT.SERVER_LOAD_INFO;
ERROR:  permission denied for schema SCOTT
LINE 1: select * from SCOTT.SERVER_LOAD_INFO;

SOLUTION:

We need to provide usage privilege on that schema to other user also.

As per postgres note:

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema.

NSMD2SIT=# grant usage on schema SCOTT to prim_user;


-- Now run the select statment: