问题描述
我正在尝试首次创建Postgres数据库.所以这可能是一个愚蠢的问题.
I'm trying to create a Postgres database for the first time. So this is probably a stupid question.
我为必须从PHP脚本访问数据库的DB角色分配了基本的只读权限，并且我有一个好奇心:如果执行
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;
从文档:
我认为，如果可以选择或操作模式中包含的任何数据，则可以访问模式本身的任何对象.我错了吗?如果没有， GRANT USAGE ON 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"?
推荐答案
GRANT 是分开的.对数据库进行 GRANT 对其内部架构没有 GRANT 权限.同样，对架构进行 GRANT 不会授予其中表的权限.
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.
如果您有权从表中进行 SELECT ，但无权在包含该表的架构中查看它，那么您将无法访问该表.
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.
按顺序进行权限测试:
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.
您的困惑可能是由于 public 模式具有默认的 GRANT 拥有角色 public 的所有权利的事实，每个用户/group是的成员.因此，每个人都已经在该架构上使用过了.
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.
这句话:
是说您在架构上必须具有 USAGE 才能在其中使用对象，但是在架构上具有 USAGE 本身不足以在架构中使用对象模式，您还必须对对象本身具有权限.
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.
这就像目录树.如果创建的目录 somedir 中包含文件 somefile ，请对其进行设置，以便只有您自己的用户才能访问该目录或文件(模式 rwx----- 在目录上，模式为 rw ------- 在文件上)，那么没有其他人可以列出目录以查看文件是否存在.
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.
如果您要授予文件世界读取权限(模式为 rw-r--r-)，但不更改目录权限，则没有任何区别.没有人可以看到来读取文件，因为他们无权列出目录.
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.
如果您改为在目录上设置 rwx-r-xr-x 并将其设置为人们可以列出和遍历目录，但不更改文件权限，则人们可以 list 文件，但由于无法访问该文件而无法读取.
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.
Pg中的内容相同.您需要具有模式 USAGE 权限和对象权限，才能对对象执行操作，例如从表中进行 SELECT .
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.
(类比下降了一点，因为PostgreSQL还没有行级安全性，因此用户仍然可以通过 SELECT 从 pg_class .虽然它们无法以任何方式进行交互，所以只是列表"部分并不完全相同.)
(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.)
这篇关于SCHEMA上的GRANT USAGE到底能做什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！