PostgreSQLUSAGE和SELECT权限案例深入分析

起因

因为项目需求，需要学习PostgreSQL，本人之前学习过MYSQL，临时快速上手，尽快的去学习项目需要PostgREST， 在学习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 中角色权限的授予及其影响。CREATE ROLE 命令用于创建一个新的角色，同时可以通过 NOLOGIN 选项指定角色无法直接登录到数据库。通过 GRANT 命令可以为角色授予权限，如 SCHEMA 的 USAGE 权限和对表的 SELECT 权限。

在授权过程中，注意区分 SCHEMA 的 USAGE 权限和表的 SELECT 权限。授予角色 SCHEMA 的 USAGE 权限意味着角色可以访问 SCHEMA 中的其他数据库对象（例如数据类型、操作符和函数等）。而为角色授予对表的 SELECT 权限，则允许角色查询该表的数据。

总之，在 PostgreSQL 中设置权限时，需要根据具体情况为角色分别授予 SCHEMA 的 USAGE 权限和表的 SELECT 权限，以确保正确的访问能力。仅具有 SCHEMA 的 USAGE 权限将无法访问表的数据，需要额外为表授予 SELECT 权限。

到此这篇关于PostgreSQL USAGE和SELECT权限案例深入分析的文章就介绍到这了,更多相关PostgreSQL USAGE和SELECT内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！