When we want to operate on the client side of PostgreSQL, we need to access the database from the remote end. At this time, PostgreSQL artifact PostgREST can be launched. PostgREST is a server that can help us generate a series of restful APIs for operating PostgreSQL databases. I also tried to use NGX in Nginx directly before_ The Postgres module operates the data, but it is very troublesome in actual use. Moreover, the RESTful style requires that each web address represents a resource. There can be no verbs but only nouns in the web address, and the nouns used are generally the same as the database name. However, I haven't found a way to adapt this style in the use of Nginx, about ngx_postgres module, you can take a look at my blog Nginx and ngx_postgres installation and configuration | Lucas blog And this one Use Java to operate PostgreSQL database through Nginx reverse proxy server (Lucas blog) .

Configuration of PostgREST

The download and installation of postgrest is also very convenient. First, we create a folder to place the installation directory of postgrest. Here, I choose to create a folder postgrest in the / opt directory, and then download the compressed package to ~ / packages,

sudo wget <https://github.com/begriffs/postgrest/releases/download/v0.4.1.0/postgrest-0.4.1.0-ubuntu.tar.xz>

Unzip the compressed package into postgrest

cd ~/packages
sudo tar -xvf postgrest-0.4.1.0-ubuntu.tar.xz -C /opt/postgrest

Then enter the postrest directory. After decompression, there is only one executable file postrest. Execute the extracted executable file.

cd /opt/postgrest
./postgrest

After execution, you will see a prompt indicating that the configuration is successful

db-uri = "postgres://user:pass@localhost:5432/dbname"
db-schema = "public"
db-anon-role = "postgres"
db-pool = 10

Next, configure the configuration file and create a new file, default Conf, configuration file content

# postgrest.conf
# The standard connection URI format, documented at
# <https://www.postgresql.org/docs/current/static/libpq-connect.html#AEN45347>
db-uri       = "postgres://postgres:password@127.0.0.1:5432/dbname"

# The name of which database schema to expose to REST clients
db-schema    = "public"

# The database role to use when no client authentication is provided.
# Can (and probably should) differ from user in db-uri
db-anon-role = "postgres"

Start postgrest with the set configuration file

cd /opt/postgrest
sudo ./postgrest default.conf

After startup, you will see the log output directly. You can stop the program through Ctrl-C. We can output the log to the log file through adjustment, and run the program in the background. The log file should be saved in the current user space, otherwise there will be permission problems.

sudo nohup postgrest default.conf>~/postgrest.log &

After startup, we can Query or insert through PostgREST.

PostgREST and Nignx are used together

Although PostgREST can provide us with powerful RESTful API help, it does not provide some security guarantees and measures. Therefore, in practical applications, we can use the powerful Nginx to reverse proxy PostgREST with Nginx. We add the following configuration to the configuration file of Nginx:

upstream postgrest {
     server localhost:3000;
     keepalive 64;
}

server {
		...

		location /api/ {
        	default_type  application/json;
        	proxy_hide_header Content-Location;
        	add_header Content-Location  /api/$upstream_http_content_location;
        	proxy_set_header  Connection "";
        	proxy_http_version 1.1;
        	proxy_pass http://postgrest/;
    	}

		...
}

Then you can go directly through http://hostname/api/ To operate on the database

reference material: [basic tutorial of PostgREST (I)] (rapid construction of PostgREST)
Using the tutorial: Official documents