database SCHEMA 上的 GRANT USAGE 究竟是做什么的？

What GRANT USAGE ON SCHEMA exactly do?

提问by Marco Sulla

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

我第一次尝试创建 Postgres 数据库，所以这可能是一个愚蠢的问题。我为必须从我的 php 脚本访问数据库的 db 角色分配了基本的只读权限，我有一个好奇心：如果我执行

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 SCHEMAis used for? And what does the documentation means exactly with "assuming that the objects' own privilege requirements are also met"?

我认为如果我可以选择或操作模式中包含的任何数据，我就可以访问模式本身的任何对象。我错了吗？如果不是，GRANT USAGE ON SCHEMA是用来做什么的？文档与“假设对象自身的权限要求也得到满足”究竟是什么意思？

回答by Craig Ringer

GRANTs on different objects are separate. GRANTing on a database doesn't GRANTrights to the schema within. Similiarly, GRANTing on a schema doesn't grant rights on the tables within.

GRANT不同对象上的 s 是分开的。GRANT对数据库进行操作并无权GRANT访问其中的架构。同样，GRANT对架构进行 ing 不会授予对其中表的权限。

If you have rights to SELECTfrom a table, but not the right to see it in the schema that contains it then you can't access the table.

如果您有权访问SELECT某个表，但无权在包含该表的架构中查看该表，则您将无法访问该表。

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 publicschema has a default GRANTof all rights to the role public, which every user/group is a member of. So everyone already has usage on that schema.

您可能会感到困惑，因为public架构默认拥有GRANT角色的所有权限public，每个用户/组都是该角色的成员。所以每个人都已经使用了该架构。

The phrase:

词组：

(assuming that the objects' own privilege requirements are also met)

（假设也满足对象自身的权限要求）

Is saying that you must have USAGEon a schema to use objects within it, but having USAGEon a schema is not by itself sufficient to use the objects within the schema, you must also have rights on the objects themselves.

是说您必须拥有USAGE架构才能使用其中的对象，但是拥有USAGE架构本身并不足以使用架构中的对象，您还必须拥有对象本身的权限。

It's like a directory tree. If you create a directory somedirwith file somefilewithin 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.

它就像一个目录树。如果您创建一个somedir包含文件的目录，somefile然后将其设置为只有您自己的用户可以访问该目录或文件（目录rwx------上的模式rw-------，文件上的模式），则其他人无法列出该目录以查看该文件是否存在。

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 seethe file in order to read it, because they don't have the rights to list the directory.

如果您要授予对文件 (mode rw-r--r--) 的全局读取权限，但不更改目录权限，则没有任何区别。没有人可以看到该文件以读取它，因为他们没有列出目录的权限。

If you instead set rwx-r-xr-xon the directory, setting it so people can list and traverse the directory but not changing the file permissions, people could listthe file but could not readit because they'd have no access to the file.

如果您改为设置rwx-r-xr-x目录，将其设置为人们可以列出和遍历目录但不更改文件权限，则人们可以列出文件但无法读取它，因为他们无权访问该文件。

You need to set bothpermissions for people to actually be able to view the file.

您需要为人们设置这两个权限才能实际查看文件。

Same thing in Pg. You need both schema USAGErights and object rights to perform an action on an object, like SELECTfrom a table.

在 Pg 中也是如此。您需要架构USAGE权限和对象权限才能对对象执行操作，例如SELECT从表中执行操作。

(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_classdirectly. They can't interact with it in any way, though, so it's just the "list" part that isn't quite the same.)

（这个比喻有点不恰当，因为 PostgreSQL 还没有行级安全性，所以用户仍然可以SELECT通过pg_class直接ing from 来“看到”该表存在于模式中。他们不能以任何方式与之交互，不过，所以它只是“列表”部分不完全相同。）

回答by bilelovitch

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 ;

回答by Marco Sulla

Well, this is my final solution for a simple db, for Linux:

好吧，这是我针对 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