PostgreSQL USAGE和SELECT权限实例代码分析

今天小编给大家分享一下PostgreSQL USAGE和SELECT权限实例代码分析的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

起因

 在学习PostgREST的第一个起步案例 教程 0 - 让它运行起来 的时候碰到了下面几条语句：

CREATE ROLE web_anon NOLOGIN;
GRANT USAGE ON SCHEMA api TO web_anon;
GRANT SELECT ON api.todos TO web_anon;

因为不太懂PostgSQL，就行了了研究，研究结果如下:

第一个语句：

CREATE ROLE web_anon NOLOGIN;

这条命令创建了一个名为 web_anon 的新角色，并通过使用 NOLOGIN 选项指定该角色不能直接登录到数据库。通常，这是为了给不需要直接访问数据库的应用或服务授予权限。

第二个和第三个语句：

GRANT USAGE ON SCHEMA api TO web_anon;
GRANT SELECT ON api.todos TO web_anon;

这两条命令分别将对 schema api 的使用权（USAGE）和对表 api.todos 的查看权（SELECT）授予 web_anon 角色。这意味着任何具有 web_anon 角色的用户或连接将能够查询 api.todos 表中的数据。

分析

如果不给USAGE，只给SELECT 会有什么效果
如果只授予角色（例如 web_anon）对表（如 api.todos）的 SELECT 权限但没有授予 SCHEMA（如 api）的 USAGE 权限，那么该角色将只能查询与授权的表相关的数据。

在这种情况下，web_anon 角色将仍然能够执行 SELECT 查询访问 api.todos 表中的数据，因为你已经授予了该角色针对该表的 SELECT 权限。然而，如果 schema api 包含其他对象（如数据类型、操作符、函数等），web_anon 角色将无法使用这些对象，因为没有授予 SCHEMA 的 USAGE 权限。

简而言之，没有 SCHEMA 的 USAGE 权限，角色将无法使用此 SCHEMA 中的其他数据库对象，但仍然可以运行 SELECT 查询以访问已授权表中的数据。需要注意的是，这可能会导致某些查询和操作失败，特别是当查询涉及到 SCHEMA 中其他对象时。

授权USAGE之后，还需要授权select 吗？ 如果不授权select能进行查询数据吗？
即使已经授予角色 SCHEMA 的 USAGE 权限，你仍然需要为某个表（例如：api.todos）单独授权 SELECT 权限。USAGE 权限只让角色能够访问 SCHEMA 中的数据类型、操作符、函数等其他数据库对象，但是不包含对 SCHEMA 中的表的访问权。

如果没有为表（例如：api.todos）授权 SELECT 权限，即使已授权 SCHEMA 的 USAGE 权限，角色仍然无法读取该表中的数据。为了允许一个角色执行 SELECT 查询并获取某个表中的数据，你需要为该角色分别授权 SCHEMA 的 USAGE 权限和表的 SELECT 权限。

只有USAGE没有SELECT的例子
为了更好地理解只有 SCHEMA 的 USAGE 权限的情况，我们首先需要了解 PostgreSQL schema。一个 schema 是一个命名空间，它可以包含多个数据库对象，如表、视图、数据类型、函数等。在这个例子中，假设我们有一个名为 api 的 schema 和一个名为 api.todos 的表。我们还有一个自定义数据类型和一个函数。

创建自定义数据类型：

CREATE TYPE api.priority_level AS ENUM ('Low', 'Medium', 'High');

创建一个使用自定义数据类型的表：

CREATE TABLE api.todos_with_priority (
    id SERIAL PRIMARY KEY,
    task VARCHAR(255) NOT NULL,
    priority api.priority_level NOT NULL
);

创建一个使用自定义数据类型的函数：

CREATE FUNCTION api.get_priority_as_text(p api.priority_level) RETURNS text
    LANGUAGE sql
    AS $$ SELECT 'Priority: ' || p::text; $$;

如果我们创建一个名为 web_anon 的角色并仅授权 SCHEMA 的 USAGE 权限：

CREATE ROLE web_anon NOLOGIN;
GRANT USAGE ON SCHEMA api TO web_anon;

在这种情况下，web_anon 角色将可以使用 api schema 中的自定义数据类型（例如 api.priority_level）以及自定义函数（例如 api.get_priority_as_text），但由于缺少 SELECT 权限，它无法查询 api.todos_with_priority 表。

如果将 web_anon 角色分配给另一个用户或角色并尝试执行以下查询：

SELECT id, task, api.get_priority_as_text(priority)
FROM api.todos_with_priority;

这个查询将失败，因为没有授予 SELECT 权限。

为了让 web_anon 角色能查询 api.todos_with_priority 表，我们需要给它授予对该表的 SELECT 权限：

GRANT SELECT ON api.todos_with_priority TO web_anon;

以上就是“PostgreSQL USAGE和SELECT权限实例代码分析”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注亿速云行业资讯频道。