怎么高效访问和操作数据库：基于JW实现Postgrest鉴权的方法

在当今数字化时代，数据库的管理和访问成为了软件开发中的核心环节。PostgreSQL作为一款开源的对象-关系数据库系统，以其强大的功能和灵活性赢得了广泛的认可。而PostgREST，作为一个基于PostgreSQL的RESTful API服务器，更是为开发者提供了一种简洁高效的方式来访问和操作数据库。

文章导航

一、怎么安装启动postgrest

在正式使用PostgREST之前，我们需要完成其安装和启动工作。这一步骤虽然看似简单，但确是后续操作的基础。通过正确的配置和启动，我们能够确保PostgREST能够顺利地与PostgreSQL数据库进行交互。下面，我们将详细介绍如何创建PostgREST的配置文件并启动服务。

创建postgrest的配置文件db.conf，内容如下：

db-uri = "postgres://myuser:qymlxin%40xlerp@127.0.0.1:9001/mydatabase"
db-schema = "public"
#访问时使用的角色
#postgrest启动时占用的端口号
#db-anon-role = "postgres"
server-port = "8085"
#使用restful调用时的路径
basePath = "/"
# 这里要配置用于生成和校验jwt的secret
jwt-secret = "ae518b32161245da5d1f4efde77402d9"
# 日志配置
log-level = "debug" # 日志级别
log-filter = "[]" # 日志过滤

启动postgrest

# 执行如下命令，db.conf就是第一步编辑的配置文件，启动后就可以直接使用了
./postgrest db.conf

二、生成JWT（JSON Web Token）的步骤

完成PostgREST的安装和启动后，我们需要考虑如何安全地访问API。JWT作为一种轻量级的、自包含的、基于JSON的用于双方之间安全传输信息的简洁的、URL安全的表示方法，为我们提供了一种有效的解决方案。通过生成JWT，我们可以验证用户的身份，并控制其对API的访问权限。

package jwt

import (
    "crypto/hmac"
    "crypto/sha256"
    "encoding/base64"
    "encoding/json"
    "strings"
    "time"
)

type Header struct {
    Alg string `json:"alg"`
    Typ string `json:"typ"`
}

type Payload struct {
    UserId int64  `json:"user_id"`
    Role   string `json:"role"`
    Iat    int64  `json:"iat"`
    Exp    int64  `json:"exp"`
}

func base64Encode(data []byte) string {
    return strings.TrimRight(base64.URLEncoding.EncodeToString(data), "=")
}

func signMessage(message, secret string) string {
    key := []byte(secret)
    h := hmac.New(sha256.New, key)
    h.Write([]byte(message))
    return base64Encode(h.Sum(nil))
}

func createJWT(payload Payload, secret string, exp time.Duration) string {
    // 生成头部
    header := Header{
        Alg: "HS256",
        Typ: "JWT",
    }
    headerJSON, _ := json.Marshal(header)
    headerEncoded := base64Encode(headerJSON)

    payloadJSON, _ := json.Marshal(payload)
    payloadEncoded := base64Encode(payloadJSON)

    // 生成签名
    message := headerEncoded + "." + payloadEncoded
    signature := signMessage(message, secret)

    // 组合成 JWT
    jwt := message + "." + signature
    return jwt
}

package jwt

import (
    "fmt"
    "testing"
    "time"
)

func TestCreateJWT(t *testing.T) {
    payload := Payload{
        UserId: 1,
        Role:   "myuser",
        Iat:    time.Now().Unix(),
        Exp:    time.Now().Add(time.Hour * 1).Unix(),
    }
    secret := "ae518b32161245da5d1f4efde77402d9"
    exp := time.Hour * 1

    jwt := createJWT(payload, secret, exp)

    fmt.Println(jwt)

    if jwt == "" {
        t.Errorf("Expected JWT to be non-empty, got empty")
    }
}

三、怎么使用JWT调用postgrest

有了JWT，我们就可以开始调用PostgREST提供的API了。在调用过程中，我们需要将JWT放置在请求头中，以便PostgREST能够验证我们的身份并授权访问。使用curl等命令行工具可以方便地实现这一过程。下面，我们将详细介绍如何使用JWT调用PostgREST。

使用crul调用，这里要把刚刚生成的jwt放到请求头Authorization中

curl 'http://$host:$port/$tablename' \
--header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyX2lkIjoxLCJyb2xlIjoibXl1c2VyIiwiaWF0IjoxNzI0OTIxNTg2LCJleHAiOjE3MjQ5MjUxODZ9.vLWSkabQmLRNqeoExvaodo7OQgyoik6YzgxZTeIMRas' \
--header 'Prefer: return=representation;params=single-object'

这是数据库中的数据

四、根据JWT中的payload信息过滤数据

在实际应用中，我们可能需要根据用户的身份或权限来过滤数据。通过JWT中的payload信息，我们可以实现这一目标。PostgREST允许我们在SQL视图中使用current_setting函数来获取JWT的claims信息，从而根据用户的身份来动态地过滤数据。

创建视图，使用current_setting函数获取JWT的claims信息，request.jwt.claims这个是固定的

CREATE OR REPLACE VIEW user_view AS
SELECT 
    "id",
    "name",
    age,
    info,
    is_deleted,
    to_char(create_time, 'YYYY-MM-DD HH24:MI:SS') AS create_time,
    to_char(update_time, 'YYYY-MM-DD HH24:MI:SS') AS update_time
FROM "user"
WHERE id = coalesce(((current_setting('request.jwt.claims', true)::jsonb) ->> 'user_id')::int, 1)

原始数据

这里使用的jwt中的user_id是2，所以就只会查询出id为2的数据，这样就达到了限制不同用户的查询范围的目的

五、怎么用check机制对更新字段进行限制

在数据操作过程中，更新操作是一个重要的环节。然而，如果不加以限制，用户可能会胡乱更新字段的值，导致数据的不一致或损坏。为了解决这个问题，我们可以利用PostgreSQL的CHECK机制对更新字段进行限制。通过合理设置CHECK约束，我们可以确保用户只能更新特定的字段，从而保护数据的安全性和完整性。

为了防止使用者胡乱更新字段的值，可以利用pg的check机制对更新字段进行限制，这里给出一个示例限制了只能更新user表的info字段，其他字段都不能更新

-- 创建一个函数，返回值为TRIGGER
CREATE OR REPLACE FUNCTION prevent_other_updates()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查是否仅更新了 test 字段
    IF 
                                (NEW.info IS DISTINCT FROM OLD.info) AND
                                old.id = new.id AND
                                old.name = new.name AND 
                                old.age = new.age AND 
                                old.is_deleted = new.is_deleted AND 
                                old.create_time = new.create_time AND 
                                old.update_time = new.update_time THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION 'Only test field can be updated, and user_id must remain unchanged';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 创建一个触发器，在更新前执行prevent_other_updates函数
CREATE TRIGGER update_test_only_trigger
BEFORE UPDATE ON "user"
FOR EACH ROW
EXECUTE FUNCTION prevent_other_updates();