I'm trying to create a Postgres database for the first time.
I assigned basic read-only permissions to the DB role that must access the database from my PHP scripts, and I have a curiosity: If I execute
GRANT some_or_all_privileges ON ALL TABLES IN SCHEMA schema TO role;
is there any need to also execute this?
GRANT USAGE ON SCHEMA schema TO role;
From the documentation:
USAGE: For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema.
I think that if I can select or manipulate any data contained in the schema, I can access to any objects of the schema itself. Am I wrong? If not, what is GRANT USAGE ON SCHEMA used for? And what does the documentation mean exactly with "assuming that the objects' own privilege requirements are also met"?
GRANTs on different objects are separate. GRANTing on a database doesn't GRANT rights to the schema within. Similiarly, GRANTing on a schema doesn't grant rights on the tables within.
If you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you can't access the table.
The rights tests are done in order:
Do you have `USAGE` on the schema?
No: Reject access.
Yes: Do you also have the appropriate rights on the table?
No: Reject access.
Yes: Check column privileges.
Your confusion may arise from the fact that the public schema has a default GRANT of all rights to the role public, which every user/group is a member of. So everyone already has usage on that schema.
The phrase:
(assuming that the objects' own privilege requirements are also met)
Is saying that you must have USAGE on a schema to use objects within it, but having USAGE on a schema is not by itself sufficient to use the objects within the schema, you must also have rights on the objects themselves.
It's like a directory tree. If you create a directory somedir with file somefile within it then set it so that only your own user can access the directory or the file (mode rwx------ on the dir, mode rw------- on the file) then nobody else can list the directory to see that the file exists.
If you were to grant world-read rights on the file (mode rw-r--r--) but not change the directory permissions it'd make no difference. Nobody could see the file in order to read it, because they don't have the rights to list the directory.
If you instead set rwx-r-xr-x on the directory, setting it so people can list and traverse the directory but not changing the file permissions, people could list the file but could not read it because they'd have no access to the file.
You need to set both permissions for people to actually be able to view the file.
Same thing in Pg. You need both schema USAGE rights and object rights to perform an action on an object, like SELECT from a table.
(The analogy falls down a bit in that PostgreSQL doesn't have row-level security yet, so the user can still "see" that the table exists in the schema by SELECTing from pg_class directly. They can't interact with it in any way, though, so it's just the "list" part that isn't quite the same.)