Why do we need to use "GRANT USAGE ON SCHEMA" and what its purpose?

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article applies to Pivotal Greenplum Database (GPDB).

Resolution

Privileges must be granted for each object individually. For example, granting ALL on a database does not grant full access to the objects within that database. It only grants all of the database-level privileges (CONNECT, CREATE, TEMPORARY) to the database itself. Similarly, GRANTing on a schema does not grant rights on the tables within.

Therefore, placing that into practice, if you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you cannot access the table. But an object on PUBLIC schema has a default GRANT of all rights to the role public, which every user/group is a member of, so a object in PUBLIC schema does not need a extra GRANT command, unless users have been revoked from PUBLIC schema access.