起因

因為項目需求，需要學習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;

這條命令創(chuàng)建了一個名為 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 角色將仍然能夠執(zhí)行 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 權限，角色仍然無法讀取該表中的數據。為了允許一個角色執(zhí)行 SELECT 查詢并獲取某個表中的數據，你需要為該角色分別授權 SCHEMA 的 USAGE 權限和表的 SELECT 權限。

只有USAGE沒有SELECT的例子
為了更好地理解只有 SCHEMA 的 USAGE 權限的情況，我們首先需要了解 PostgreSQL schema。一個 schema 是一個命名空間，它可以包含多個數據庫對象，如表、視圖、數據類型、函數等。在這個例子中，假設我們有一個名為 api 的 schema 和一個名為 api.todos 的表。我們還有一個自定義數據類型和一個函數。

創(chuàng)建自定義數據類型：

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

創(chuàng)建一個使用自定義數據類型的表：

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

創(chuàng)建一個使用自定義數據類型的函數：

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

如果我們創(chuàng)建一個名為 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 角色分配給另一個用戶或角色并嘗試執(zhí)行以下查詢：

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 命令用于創(chuàng)建一個新的角色，同時可以通過 NOLOGIN 選項指定角色無法直接登錄到數據庫。通過 GRANT 命令可以為角色授予權限，如 SCHEMA 的 USAGE 權限和對表的 SELECT 權限。

在授權過程中，注意區(qū)分 SCHEMA 的 USAGE 權限和表的 SELECT 權限。授予角色 SCHEMA 的 USAGE 權限意味著角色可以訪問 SCHEMA 中的其他數據庫對象（例如數據類型、操作符和函數等）。而為角色授予對表的 SELECT 權限，則允許角色查詢該表的數據。

總之，在 PostgreSQL 中設置權限時，需要根據具體情況為角色分別授予 SCHEMA 的 USAGE 權限和表的 SELECT 權限，以確保正確的訪問能力。僅具有 SCHEMA 的 USAGE 權限將無法訪問表的數據，需要額外為表授予 SELECT 權限。

到此這篇關于PostgreSQL USAGE和SELECT權限案例深入分析的文章就介紹到這了,更多相關PostgreSQL USAGE和SELECT內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！