I'm trying to create for the first time a Postgres database, so this is probably a stupid question. 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 execute also
GRANT USAGE ON SCHEMA schema TO role;
?
From 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 GRANT USAGE ON SCHEMA
is used for? And what does the documentation means exactly with "assuming that the objects' own privilege requirements are also met"?
GRANT
s on different objects are separate.GRANT
ing on a database doesn'tGRANT
rights to the schema within. Similiarly,GRANT
ing 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:
Your confusion may arise from the fact that the
public
schema has a defaultGRANT
of all rights to the rolepublic
, which every user/group is a member of. So everyone already has usage on that schema.The phase:
Is saying that you must have
USAGE
on a schema to use objects within it, but havingUSAGE
on a schema is not its self 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 filesomefile
within it then set it so that only your own user can access the directory or the file (moderwx------
on the dir, moderw-------
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, likeSELECT
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
SELECT
ing frompg_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.)For a production system, you can use this configuration :