What GRANT USAGE ON SCHEMA exactly do

Question

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

User · Answer

Well  this is my final solution for a simple db  for Linux     Read this before        roles in postgres are users  and can be used also as group of users      ROLE LOCAL will be the user that access the db for maintenance and     administration   ROLE REMOTE will be the user that access the db from the webapp     you have to change   ROLE LOCAL     ROLE REMOTE  and   DB      strings with your desired names     it s preferable that  ROLE LOCAL     DB   -------------------------------------------------------------------------------    ----------- SKIP THIS PART UNTIL POSTGRES JDBC ADDS SCRAM - START ----------    cd  etc postgresql  VERSION main sudo cp pg hba conf pg hba conf bak sudo -e pg hba conf    change all  md5  with  scram-sha-256    save and exit    ------------ SKIP THIS PART UNTIL POSTGRES JDBC ADDS SCRAM - END -----------    sudo -u postgres psql    in psql  create role  ROLE LOCAL login createdb   password  ROLE LOCAL create role  ROLE REMOTE login   password  ROLE REMOTE  create database  DB owner  ROLE LOCAL encoding  utf8    connect  DB  ROLE LOCAL    Create all tables and objects  and after that    connect  DB postgres  revoke connect on database  DB from public  revoke all on schema public from public  revoke all on all tables in schema public from public   grant connect on database  DB to  ROLE LOCAL  grant all on schema public to  ROLE LOCAL  grant all on all tables in schema public to  ROLE LOCAL  grant all on all sequences in schema public to  ROLE LOCAL  grant all on all functions in schema public to  ROLE LOCAL   grant connect on database  DB to  ROLE REMOTE  grant usage on schema public to  ROLE REMOTE  grant select  insert  update  delete on all tables in schema public to  ROLE REMOTE  grant usage  select on all sequences in schema public to  ROLE REMOTE  grant execute on all functions in schema public to  ROLE REMOTE   alter default privileges for role  ROLE LOCAL in schema public     grant all on tables to  ROLE LOCAL   alter default privileges for role  ROLE LOCAL in schema public     grant all on sequences to  ROLE LOCAL   alter default privileges for role  ROLE LOCAL in schema public     grant all on functions to  ROLE LOCAL   alter default privileges for role  ROLE REMOTE in schema public     grant select  insert  update  delete on tables to  ROLE REMOTE   alter default privileges for role  ROLE REMOTE in schema public     grant usage  select on sequences to  ROLE REMOTE   alter default privileges for role  ROLE REMOTE in schema public     grant execute on functions to  ROLE REMOTE     CTRL D

User · Answer

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

User · Answer

For a production system  you can use this configuration    --ACCESS DB REVOKE CONNECT ON DATABASE nova FROM PUBLIC  GRANT  CONNECT ON DATABASE nova  TO user   --ACCESS SCHEMA REVOKE ALL     ON SCHEMA public FROM PUBLIC  GRANT  USAGE   ON SCHEMA public  TO user   --ACCESS TABLES REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC   GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read only   GRANT SELECT  INSERT  UPDATE  DELETE ON ALL TABLES IN SCHEMA public TO read write   GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin

[database] What GRANT USAGE ON SCHEMA exactly do?

Examples related to database

Examples related to postgresql

Examples related to schema

Examples related to grant

Examples related to database-permissions