PostgREST concise tutorial

introduce

PostgREST is a Web server that relies on the PostgSQL database and converts it directly into a Restful API service.

This article shows you how to use the PostgREST implementation to convert PostgSQL data into the appropriate HTTP interface and how to implement authentication.

Operating Environment Configuration

1. Install PostgSQL database: The online installation tutorial is much older and will not be described here (this article is based on v14).

2. To install PostgREST, go to the Github release page to download the corresponding platform package, and then decompress it to the local PC (this document is based on the V9 version).

3. Run postgrest in the decompressed directory (postgrest.exe in Windows). If information similar to the following is displayed, the environment is normal:

   Usage: postgrest [-e|--example] [--dump-config | --dump-schema] FILENAME
     PostgREST 9.0. 0 / create a REST API to an existing Postgres database
   
   Available options: ...
   Copy the code

PostgREST configuration

PostgREST reads configuration files to determine how to connect to the database and how to handle client requests. Create a configuration file postgrest-todos.conf that contains at least the following fields:

UserName, password, port, database name) db-uri ="Postgres: / / username: password @ localhost: 5432 / postgres" # needs to be exposed to the client using what is the db-schemas ="Public" # Since PostgREST supports JWT authentication, this configuration item configures the db which role the database uses to operate without client authentication (see the creation of this role later in this article)-anon-role =Error will not print the request log. Info will print the request log for debugging-level = "info" 
Copy the code

In addition, the configuration items can be configured in the form of environment variables. For example, the preceding configuration items can be represented by the following environment variables (prefixed with PGRST_, separated by hyphens and uppercase letters) :

PGRST_DB_URI="postgres://username:password@localhost:5432/postgres"
PGRST_DB_SCHEMAS="public"
PGRST_DB_ANON_ROLE="anon"
# PGRST_LOG_LEVEL="info"
Copy the code

Database Configuration

Next you need to configure it in PostgSQL based on the above configuration items and populate it with some DEMO data for testing:

CREATE ROLE anon NOLOGIN;
GRANT SELECT ON todos TO anon;
Copy the code

CREATE TABLE todos (
   id SERIAL PRIMARY KEY,
   content TEXT,
   complete BOOL NOT NULL DEFAULT false
);

INSERT INTO todos(content) 
VALUES('todo1'), ('todo2');
Copy the code

Run and get the list

Run the./postgrest postgrest-todos.conf command. If the command output similar to the following is successful, an HTTP service is started on port 3000.

15/Jan/2022:19:01:59 +0800: Attempting to connect to the database...
15/Jan/2022:19:01:59 +0800: Connection successful
15/Jan/2022:19:01:59 +0800: Listening on port 3000
15/Jan/2022:19:01:59 +0800: Config re-loaded
15/Jan/2022:19:01:59 +0800: Listening for notifications on the pgrst channel
15/Jan/2022:19:01:59 +0800: Schema cache loaded
Copy the code

You can then make a GET request for the list:

curl http://localhost:3000/todos
Copy the code

How to update data

Anon does not have permission to modify the database. If we update the database with PostgREST’s request (POST/PATH, etc.), the request will return the following response:

{
  "hint": null."details": null."code": "42501"."message": "permission denied for table todos"
}
Copy the code

PostgREST provides jWT-BASED authentication. Here’s how to implement the function that only logged-in users can modify data:

First of all, let’s make a brief description/review of the use of JWT. JWT is full JSON Web Token. Its usage process is generally shown in the figure below:

Let’s start configuring to enable JWT:

1. Create a role in the database that can modify data:

   create role no_anon nologin;
   grant all on todos to no_anon;
   grant usage,select on sequence todos_id_seq to no_anon;
   Copy the code

2. Update postgrest – todos. Conf:

   # Add the following configuration
   jwt-secret = "A key string of at least 32 characters."
   Copy the code

3. Run PostgREST again

4. Generate a JWT for the client to use: This step requires invoking the JWT generation algorithm, which can be generated inline on the site for testing purposes. As shown below, fill in the first arrow with the name of the role we created above (PostgREST will first switch to this role and then execute SQL), fill in the second arrow with its own key string, and the left side will automatically get JWT:

5. Emulated client interface invocation: append JWT to the request header of the update request:

   curl -X POST --location "http://localhost:3000/todos" \
       -H "Content-Type: application/json" \
       -H "Authorization: Bearer [YOUR_JWT_HERE]" \
       -d "{"content":"created from request"}"
   Copy the code

   If the response code is 201, it indicates success. In this case, you can query the data through the GET interface and find that a new data is added:

   curl http://localhost:3000/todos
   Copy the code

How do I add expiration time to JWT

The JWT generated above never expires by default, which would certainly be unreasonable in a real scenario, so how do you add an expiration time? Mysql > create JWT; add an exp field for Payload (Unix timestamp)

{
  "role": "no_anon"."exp": 1642269576}Copy the code

The resulting JWT will expire after that time, and the expired request response returns {“message”:”JWT expired”}

How do I disable JWT immediately

There is still a problem with the added expiration time above: the JWT must wait until the specified time before it becomes invalid. So how do you disable JWT immediately? First of all, to undo JWT immediately, we need to do some extra storage, such as creating a new table to hold blacklisted users, and then the back-end logic determines each time the requested JWT data is in the blacklist and rejects it.

PostgREST provides a configuration item that allows the PostgreSQL Fucntions function to be executed before each request. If the function fails, the request will fail.

db-pre-request = "auth"
Copy the code

Then create a PostgreSQL function called auth:

create or replace function auth() returns void
  language plpgsql
  as $$
declare
  creator text;
begin
  creator := current_setting('request.jwt.claims'.true)::json->>'creator';
  if  creator ='babo' 
  then
    raise insufficient_privilege using hint = 'So sorry! ';
  end if;
end
$$;
Copy the code

As can be seen from the above function definition, when creator of JWT Payload is set to babo, the function will throw an error, that is, the request will be blocked. Then let’s generate two JWT for verification:

//Payload {"role": "no_anon", "creator": "mabo"}//Payload {"role": "no_anon", "creator": "babo"}Copy the code

POST requests using the above two JWT’s will respond normally and the latter will return 403:

{ "hint": "So sorry!" , "details":null,
  "code": "42501",
  "message": "insufficient_privilege"
}
Copy the code

Of course, in actual scenarios, our blacklist is maintained separately rather than hard-coded in functions. Here, we create a blacklist table to store blacklist users:

-- Create a blacklist
create table blacklist(
    name text PRIMARY key 
);
-- Insert blacklisted users
insert into blacklist(name) values('babo); Grant read permission to no_anon. Select on blacklist to no_anon.Copy the code

Then create a new auth2 function to check from the blacklist:

create or replace function auth2() returns void
  language plpgsql
  as $$
declare
  creator text;
begin
  creator := current_setting('request.jwt.claims'.true)::json->>'creator';
  if exists(select 1 from blacklist u where u.name = creator)
  then
    raise insufficient_privilege using hint = 'So sorry from auth2! ';
  end if;
end
$$;
Copy the code

Finally, update the PostgREST configuration file and run it again:

db-pre-request = "auth2"
Copy the code

In this case, the JWT can be revoked in real time by updating the data of the Blacklist.

At the end

If you want to use PostgREST online, you’ll need to learn more about how to “hot-reload” configuration items, how to improve permissions and quarantine, and how to prevent cyber attacks, all of which can be found in the documentation on the official website. With this tool, we can write almost no back-end code, which seems very useful for CRUD-like applications.

Knot the end

Searching on the Internet, I suddenly found that there is a prest project written based on Go. Although the specific differences between the two have not been further explored, just from the development language, Go sounds more friendly, as if from the mountains to the city suddenly 🥲.