postgREST 角色授权文档整理

说明

使用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;

您可能会担心,匿名用户可以从:basic_auth.users表中读取所有内容。 但是,此表不适用于直接查询,因为它位于单独的架构中。 匿名角色需要访问,因为public:users视图使用调用用户的权限读取基础表。 但我们已确保视图正确限制对敏感信息的访问。

发布了22 篇原创文章 · 获赞 8 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览