postgresql 创建数据库API

Connect to the SQL console (psql) inside the container. To do so, run this from your command line:

sudo docker exec -it tutorial psql -U postgres
You should see the psql command prompt:

psql (9.6.3)
Type "help" for help.

postgres=#
The first thing we’ll do is create a named schema for the database objects which will be exposed in the API. We can choose any name we like, so how about “api.” Execute this and the other SQL statements inside the psql prompt you started.

create schema api;
Our API will have one endpoint, /todos, which will come from a table.

create table api.todos (
id serial primary key,
done boolean not null default false,
task text not null,
due timestamptz
);

insert into api.todos (task) values
('finish tutorial 0'), ('pat self on back');
Next make a role to use for anonymous web requests. When a request comes in, PostgREST will switch into this role in the database to run queries.

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;
The web_anon role has permission to access things in the api schema, and to read rows in the todos table.

It’s a good practice to create a dedicated role for connecting to the database, instead of using the highly privileged postgres role. So we’ll do that, name the role authenticator and also grant him the ability to switch to the web_anon role :

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;
Now quit out of psql; it’s time to start the API!

\q