What is GRANT USE ON SCHEMA?

Question

I am trying to create a Postgres database for the first time, so this is probably a dumb question. I have assigned basic read-only permissions to the db role, which should access the database from my php scripts, and I have curiosity: if I execute

GRANT some_or_all_privileges ON ALL TABLES IN SCHEMA schema TO role; 

is there a need to fulfill also

 GRANT USAGE ON SCHEMA schema TO role; 

?

From the documentation :

USAGE: for schemas, allows access to the objects contained in the specified schema (provided that the object's own privilege requirements are also respected). In essence, this allows the recipient to "search" for objects within the circuit.

I think that if I can select or manipulate any data contained in the diagram, I can access any objects of the diagram itself. Am I mistaken? If not, what is GRANT USAGE ON SCHEMA ? And what does the documentation mean exactly with "assuming that the object privilege requirements are also met"?

Answer 1

GRANT for different objects separate. GRANT ING based on non GRANT rights to the circuit inside. Similarly, providing GRANT for a schema does not give rights to tables inside.

If you have SELECT rights from the table, but you cannot see it in the schema in which it is contained, you cannot access the table.

The verification of rights is carried out in the following 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 due to the fact that the public scheme has, by default, GRANT all rights to the public role, of which each user / group is a member. Thus, everyone is already using this scheme.

Phrase:

(provided that the own requirements for privileges of objects are also fulfilled)

It is said that to use the objects inside it, you must have USAGE in the scheme, but the presence of USAGE in the scheme is not enough for using objects in the scheme, you must also have rights to the objects themselves.

It is like a directory tree. If you create somedir directory with somedir file inside it, then set it so that only your own user rwx------ access to the directory or file ( rwx------ mode rwx------ in the directory, rw------- mode rw------- in the file), then no one else will be able to rw------- list the directory to see that the file exists.

If you granted read permissions for the file ( rw-r--r-- mode), but did not change the permissions for the directory, that would not matter. No one could see the file in order to read it, because they do not have rights to list the directory.

If instead you install rwx-r-xr-x in the directory, setting it up so that people can view and view the directory, but without changing the file permissions, people could list the file, but could not read it, because they would not have access to the file.

You need to set both permissions so that people can actually view the file.

Same thing in Pg. You need both USAGE rights and object rights schemes to perform actions on the object, for example, SELECT , from the table.

(The analogy falls a bit in that PostgreSQL does not yet have row-level security, so the user can still “see” that the table exists in the SELECT schema directly from pg_class . They cannot interact with it anyway, so it's just part of the list that is not exactly the same.)

Answer 2

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 ; 

Answer 3

Well, this is my final solution for a simple database 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 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