postgREST 角色授权文档整理

最新推荐文章于 2024-07-30 14:16:26 发布

henjuewang

最新推荐文章于 2024-07-30 14:16:26 发布

阅读量1.7k

点赞数

分类专栏： PostgREST

本文链接：https://blog.csdn.net/henjuewang/article/details/81011968

版权

PostgREST 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

说明

使用postgrest进行角色的jwt token加密，验证和授权。

过程

一.准备工作

以下都在pg shell中执行

1. 开启pgcrypto扩展

create extension if not exists pgcrypto;

2. 使用pgjwt函数定义

CREATE OR REPLACE FUNCTION url_encode(data bytea) RETURNS text LANGUAGE sql AS $$
    SELECT translate(encode(data, 'base64'), E'+/=\n', '-_');
$$;


CREATE OR REPLACE FUNCTION url_decode(data text) RETURNS bytea LANGUAGE sql AS $$
WITH t AS (SELECT translate(data, '-_', '+/') AS trans),
     rem AS (SELECT length(t.trans) % 4 AS remainder FROM t) -- compute padding size
    SELECT decode(
        t.trans ||
        CASE WHEN rem.remainder > 0
           THEN repeat('=', (4 - rem.remainder))
           ELSE '' END,
    'base64') FROM t, rem;
$$;



CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text)
RETURNS text LANGUAGE sql AS $$
WITH
  alg AS (
    SELECT CASE
      WHEN algorithm = 'HS256' THEN 'sha256'
      WHEN algorithm = 'HS384' THEN 'sha384'
      WHEN algorithm = 'HS512' THEN 'sha512'
      ELSE '' END AS id)  -- hmac throws error
SELECT url_encode(hmac(signables, secret, alg.id)) FROM alg;
$$;


CREATE OR REPLACE FUNCTION sign(payload json, secret text, algorithm text DEFAULT 'HS256')
RETURNS text LANGUAGE sql AS $$
WITH
  header AS (
    SELECT url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8')) AS data
    ),
  payload AS (
    SELECT url_encode(convert_to(payload::text, 'utf8')) AS data
    ),
  signables AS (
    SELECT header.data || '.' || payload.data AS data FROM header, payload
    )
SELECT
    signables.data || '.' ||
    algorithm_sign(signables.data, secret, algorithm) FROM signables;
$$;


CREATE OR REPLACE FUNCTION verify(token text, secret text, algorithm text DEFAULT 'HS256')
RETURNS table(header json, payload json, valid boolean) LANGUAGE sql AS $$
  SELECT
    convert_from(url_decode(r[1]), 'utf8')::json AS header,
    convert_from(url_decode(r[2]), 'utf8')::json AS payload,
    r[3] = algorithm_sign(r[1] || '.' || r[2], secret, algorithm) AS valid
  FROM regexp_split_to_array(token, '\.') r;
$$;

二.建立用户密码表和加密方法

函数和触发器将存在于：code：basic_auth模式中，您不应在API中公开公开。公共视图和函数将存在于不同的模式中，该模式在内部引用此内部信息。

1.建立用户密码表

-- 我们将内容置于basic_auth模式中，
-- 以将其隐藏在公共视图中。
-- 某些公共过程/视图将引用内部的帮助程序和表。
create schema if not exists basic_auth;

create table if not exists
basic_auth.users (
  email    text primary key check ( email ~* '^.+@.+\..+$' ),
  pass     text not null check (length(pass) < 512),
  role     name not null check (length(role) < 512)
);

2.触发器

我们希望该角色role是实际数据库角色的外键，但是PostgreSQL不支持对：code：pg_roles表的这些约束。我们将使用触发器手动强制执行它。

create or replace function
basic_auth.check_role_exists() returns trigger
  language plpgsql
  as $$
begin
  if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
    raise foreign_key_violation using message =
      'unknown database role: ' || new.role;
    return null;
  end if;
  return new;
end
$$;

drop trigger if exists ensure_user_role_exists on basic_auth.users;
create constraint trigger ensure_user_role_exists
  after insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.check_role_exists();

3.密码保密

使用pgcrypto扩展和触发器来保密密码：users表。

create or replace function
basic_auth.encrypt_pass() returns trigger
  language plpgsql
  as $$
begin
  if tg_op = 'INSERT' or new.pass <> old.pass then
    new.pass = crypt(new.pass, gen_salt('bf'));
  end if;
  return new;
end
$$;

drop trigger if exists encrypt_pass on basic_auth.users;
create trigger encrypt_pass
  before insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.encrypt_pass();

4.用户密码来返回角色

检查加密列的密码。如果电子邮件和密码正确，它将返回用户的数据库角色。

create or replace function
basic_auth.user_role(email text, pass text) returns name
  language plpgsql
  as $$
begin
  return (
  select role from basic_auth.users
   where users.email = user_role.email
     and users.pass = crypt(user_role.pass, users.pass)
  );
end;
$$;

三.登录访问

通过上面的操作，我们创建了一个用于存储用户信息的内部表。在这里，我们创建一个登录函数，它接受一个电子邮件地址和密码，如果凭据与内部表中的用户匹配，则返回JWT。

1.登录

如JWT from SQL中所述，我们将在登录函数中创建一个JWT。注意，需要将此示例中硬编码的密钥调整为您自己的安全密钥。

create or replace function
login(email text, pass text) returns basic_auth.jwt_token
  language plpgsql
  as $$
declare
  _role name;
  result basic_auth.jwt_token;
begin
  -- check email and password
  select basic_auth.user_role(email, pass) into _role;
  if _role is null then
    raise invalid_password using message = 'invalid user or password';
  end if;

  select sign(
      row_to_json(r), 'mysecret'
    ) as token
    from (
      select _role as role, login.email as email,
         extract(epoch from now())::integer + 60*60 as exp
    ) r
    into result;
  return result;
end;
$$;

前端调用此函数API，POST请求如下：

POST /rpc/login HTTP/1.1

{ "email": "foo@bar.com", "pass": "foobar" }

响应看起来像下面的代码段。尝试在jwt.io https://jwt.io/解码令牌。（它的编码带有以下秘钥：mysecret，如上面的SQL代码中所指定的。在你的应用程序中更改这个秘钥！）

2.注册权限

您的数据库角色需要访问模式，表，视图和函数才能为HTTP请求提供服务。回想一下“角色系统概述”_，PostgREST使用特殊角色来处理请求，即身份验证者和匿名角色。以下是允许匿名用户创建帐户并尝试登录的权限示例。

-- 名称“anon”和“authenticator”是可配置的
-- 而不是关键词，我们只是为了清晰起见而选择它们
create role anon;
create role authenticator noinherit;
grant anon to authenticator;

grant usage on schema public, basic_auth to anon;
grant select on table pg_authid, basic_auth.users to anon;
grant execute on function login(text,text) to anon;