After the Permission for Querying Tables in a Schema Is Granted to a User, the User Still Cannot Query the Tables

Symptom

After an authorized user runs the GRANT SELECT ON all tables in schema schema_name to u1 command to grant the access permission of tables in a schema to user u1, user u1 still cannot access the tables.

Possible Causes

To authorize a user to access table or view objects in a schema, you also need to grant the USAGE permission of the schema to the user. Without this permission, the user can only view the names of the objects but cannot access them.

If you want to grant user u1 the permission on tables to be created in the schema, run the ALTER DEFAULT PRIVILEGES command to change the default permission.

Handling Procedure

Log in to the database as a user with the schema permission and run the following command to grant the table permission in the schema to a specified user:

1
2

GRANT USAGE ON SCHEMA schema_name TO u1;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO u1;

Run the following command to grant the permission on the tables to be created in the schema to a specified user:

1

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO u1;

In the preceding SQL statements, GRANT SELECT indicates that the table query permission is assigned. If you want to assign other permissions to other users, see the GRANT syntax description.

To grant the permission to query all tables in all schemas in the database to a user, query all the schemas in the PG_NAMESPACE system catalog then grant the permission to the user. Example:

1	SELECT 'grant select on all tables in '|| nspname || 'to u1' FROM pg_namespace;