postgrest和postgreSQL权限认证

前面我们说了如何从0搭建postgrest和postgresql服务，当服务搭起来后没有权限认证肯定是用不的，这节将讲解如何搭建权限认证。pg是通过jwt进行权限认证。

1. 首先要将pg所要用的jwt上传到pg服务器当中。
   此处获取插件。postgrest和postgresql权限认证，jwt插件-CentOS文档类资源-CSDN下载这是postgresql使用权限控制的插件，若是没有这个插件就无法正常工作。更多下载资源、学习资料请访问CSDN下载频道.https://download.csdn.net/download/weixin_43667400/85143226由于是用docker搭建的，首先将插件上传到docker容器里：

   docker cp /home/PostgREST/testau/pgjwt.control 
    postgrest_db_1:/usr/share/postgresql/14/extension/

   
   进入docker容器，使用命令docker exec -it  容器名 /bin/bash
   进入容器后执行命令psql -p 端口 -U 数据库用户名 -W 数据库名，进入数据库后执行

   create extension if not exists pgcrypto;
   CREATE EXTENSION if not exists pgjwt;

   
   至此准备工作已经完成，接下来进行数据库函数配置。
2. 用Nacicat连接数据库后，新建查询。
   首先，我们需要一个表格来跟踪我们的用户：

   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)
   );

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

   create or replace function
   basic_auth.check_role_exists() returns trigger 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
   $$ language plpgsql;
   
   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();

   接下来，我们将使用 pgcrypto 扩展和触发器来保证密码在users表中的安全

   create extension if not exists pgcrypto;
   
   create or replace function
   basic_auth.encrypt_pass() returns trigger 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
   $$ language plpgsql;
   
   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();

   有了表，我们可以创建一个助手来检查加密列的密码。如果电子邮件和密码正确，它将返回用户的数据库角色。

   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。请注意，您需要将本示例中硬编码的密钥调整为您选择的安全（至少 32 个字符）密钥。

   CREATE TYPE basic_auth.jwt_token AS (
     token text
   );
   
   create or replace function
   login(email text, pass text) returns basic_auth.jwt_token as $$
   declare
     _role name;
     result basic_auth.jwt_token;
   begin
     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), 'reallyreallyreallyreallyverysafe'
       ) 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;
   $$ language plpgsql security definer;

   row_to_json(r), 'reallyreallyreallyreallyverysafe'这句话就是你所要配置的密钥，可以自行用工具生成32位密钥来替换。
   

    

3. 这里只是在pg数据库中配置了，没有在postgrest服务中配置。
   修改上节的docker-compose.yml文件
   
   注意此处，如果使用了单独创建的匿名用户，那么就添加PGRST_JWT_SECRET: "reallyreallyreallyreallyverysafe"即可，如果匿名用户就是数据库超级用户的话就添加role-claim-key: "reallyreallyreallyreallyverysafe"即可。
   然后重启服务，docker-compose restart 
4. 测试
   使用postman进行测试，在测试之前首先创建几个角色。
   需要保证这里的连接角色可以跳转到其他角色,数据库中执行

   GRANT 连接角色 TO 其他角色 ;

   
   
   
   
   

 如果可以登录为用户，不能登录为角色

 新建测试表，给表中不同角色赋予不同权限

 在user表中添加几个登录用户

 用postman测试

这样就可以成功获取到token了，接着用token获取数据。 

 

 这是不允许改token用户查询此表。提示没有权限。

 这里允许改token用户查询此表，显示正常。
至此postgresql权限认证这部分就完成了，后续会讲解，多个模式之间如何切换，及权限赋予。
参考官方网址：Overview of Role System — PostgREST 9.0.0 documentation