PostgREST

https://docs.postgrest.org/en/v13
Admin
PostgREST is a standalone web server that automatically turns your PostgreSQL database into a...
Tokens:258,602
Snippets:1,978
Trust Score:9.5
Update:1 week ago
Show doc for...
Tokens:
Raw
### Show example configuration with postgrest [-e|--example]

Source: https://docs.postgrest.org/en/v13/_sources/references/cli.rst.txt

Shows example configuration options.

```bash
$ postgrest [-e|--example]
```

--------------------------------

### Install PostgREST with nix-env

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Installs PostgREST from nixpkgs.

```bash
nix-env -i postgrest

```

--------------------------------

### Start PostgREST with a config file

Source: https://docs.postgrest.org/en/v13/references/configuration.html

Starts the PostgREST server with a configuration file. The file path is the only argument; there is no predefined location.

```bash
./postgrest /path/to/postgrest.conf

```

--------------------------------

### Install PostgREST with pkg on FreeBSD

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Installs PostgREST from the official FreeBSD ports.

```bash
pkg install hs-postgrest

```

--------------------------------

### Enable and start PostgREST service

Source: https://docs.postgrest.org/en/v13/_sources/integrations/systemd.rst.txt

Commands to enable the service at boot and start it. Includes commented reload command.

```bash
systemctl enable postgrest
systemctl start postgrest

## For reloading the service
## systemctl restart postgrest
```

--------------------------------

### Verify PostgREST installation

Source: https://docs.postgrest.org/en/v13/_sources/explanations/install.rst.txt

Check that the server is installed by running this command. It should display the help information for the postgrest executable.

```bash
postgrest --help
```

--------------------------------

### Install PostgREST with Chocolatey or Scoop on Windows

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Installs PostgREST using Chocolatey or Scoop on Windows.

```bash
choco install postgrest
scoop install postgrest

```

--------------------------------

### Install PostgREST with pacman on Arch Linux

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Installs PostgREST from the Arch Linux community repository.

```bash
pacman -S postgrest

```

--------------------------------

### openapi-server-proxy-uri example output

Source: https://docs.postgrest.org/en/v13/_sources/references/configuration.rst.txt

Example JSON output showing the OpenAPI self-documentation with a custom host and base path, as configured by the openapi-server-proxy-uri setting.

```json
{
  "swagger": "2.0",
  "info": {
    "version": "0.4.3.0",
    "title": "PostgREST API",
    "description": "This is a dynamic API generated by PostgREST"
  },
  "host": "postgrest.com:443",
  "basePath": "/",
  "schemes": [
    "https"
  ]
}
```

--------------------------------

### Install PostgREST with Homebrew on macOS

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Installs PostgREST from the Homebrew official repository on macOS.

```bash
brew install postgrest

```

--------------------------------

### Install libpq-dev on Ubuntu/Debian

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Install the libpq development package on Ubuntu or Debian systems, required to build PostgREST from source.

```bash
sudo apt-get install libpq-dev
```

--------------------------------

### Install libpq on Fedora/CentOS/Red Hat

Source: https://docs.postgrest.org/en/v13/tutorials/tut0.html

Installs postgresql-libs to satisfy the libpq dependency on Fedora, CentOS, or Red Hat.

```bash
sudo yum install postgresql-libs

```

--------------------------------

### Start PostgREST with Docker Compose

Source: https://docs.postgrest.org/en/v13/_sources/explanations/install.rst.txt

Run this command in the directory containing the docker-compose.yml file to start the database and PostgREST containers. The logs of both services will be shown and PostgREST will be accessible on port 3000.

```bash
docker-compose up
```

--------------------------------

### Example URL with select clause

Source: https://docs.postgrest.org/en/v13/_sources/references/api/resource_representation.rst.txt

A URL example showing that the ordering of columns in the response may differ from the order in the select clause when using resource embedding. The response may reorder properties.

```bash
http://localhost:3000/films?select=directors(last_name,id),title
```

--------------------------------

### View Endpoint Example

Source: https://docs.postgrest.org/en/v13/_sources/references/api/tables_views.rst.txt

Example of creating a view in PostgreSQL to provide a new endpoint for complex filters.

```APIDOC
## View Endpoint Example

### Description
For more complicated filters, you can create a view in the database. The view will provide a new endpoint.

### Example
Create a view to show "today's stories" including possibly older pinned stories:

```sql
CREATE VIEW fresh_stories AS
SELECT *
  FROM stories
 WHERE pinned = true
    OR published > now() - interval '1 day'
ORDER BY pinned DESC, published DESC;
```

The view will provide a new endpoint:

```bash
curl "http://localhost:3000/fresh_stories"
```

### Notes
- The view endpoint is automatically available at `/view_name`.
- This allows complex filters that cannot be expressed with simple query parameters.
```

--------------------------------

### Install postgresql-libs on Fedora/CentOS/Red Hat

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Install the PostgreSQL client libraries on Fedora, CentOS, or Red Hat systems, required to build PostgREST from source.

```bash
sudo yum install postgresql-libs
```

--------------------------------

### Install pg-safeupdate via PGXN

Source: https://docs.postgrest.org/en/v13/_sources/integrations/pg-safeupdate.rst.txt

Installation command for the pg-safeupdate extension using PGXN, followed by a comment showing the required postgresql.conf setting. The extension raises an error if UPDATE or DELETE are executed without conditions.

```bash
sudo -E pgxn install safeupdate

# then add this to postgresql.conf:
# shared_preload_libraries='safeupdate';
```

--------------------------------

### Get execution plan with curl

Source: https://docs.postgrest.org/en/v13/references/observability.html

Use the `Accept: application/vnd.pgrst.plan` header to get the EXPLAIN plan in text format. This feature is enabled by db-plan-enabled (false by default).

```bash
curl "http://localhost:3000/users?select=name&order=id" \
  -H "Accept: application/vnd.pgrst.plan"
```

--------------------------------

### GET /callcounter

Source: https://docs.postgrest.org/en/v13/references/transactions.html

Example of a GET request to a view that modifies a sequence, resulting in a 405 Method Not Allowed error because the transaction is read-only.

```APIDOC
## GET /callcounter

### Description
Example of a GET request to a view that modifies a sequence, resulting in a 405 Method Not Allowed error because the transaction is read-only.

### Method
GET

### Endpoint
/callcounter

### Response
#### Error Response (405 Method Not Allowed)
- **code** (string) - Error code
- **details** (null) - Additional details
- **hint** (null) - Hint
- **message** (string) - Error message

#### Response Example
{
  "code": "25006",
  "details": null,
  "hint": null,
  "message": "cannot execute nextval() in a read-only transaction"
}
```

--------------------------------

### PostgREST configuration file tutorial.conf

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Configuration file tutorial.conf for PostgREST, specifying the database URI, schemas, and anonymous role.

```ini
db-uri = "postgres://authenticator:mysecretpassword@localhost:5432/postgres"
db-schemas = "api"
db-anon-role = "web_anon"
```

--------------------------------

### Complex logical conditions with curl --get

Source: https://docs.postgrest.org/en/v13/references/api/tables_views.html

Shows an incorrect example (commented out) and the correct way to pass complex logical conditions using curl's --get and -d flags to avoid shell interpretation.

```shell
# curl "http://localhost:3000/people?grade=gte.90&student=is.true&or=(age.eq.14,not.and(age.gte.11,age.lte.17))"

curl --get "http://localhost:3000/people" \
  -d "grade=gte.90" \
  -d "student=is.true" \
  -d "or=(age.eq.14,not.and(age.gte.11,age.lte.17))"

```

--------------------------------

### GET /fresh_stories

Source: https://docs.postgrest.org/en/v13/references/api/tables_views.html

A view created in the database provides a new endpoint. This example shows how to query a view named fresh_stories.

```APIDOC
## GET /fresh_stories

### Description
A view created in the database provides a new endpoint. This example shows how to query a view named fresh_stories.

### Method
GET

### Endpoint
/fresh_stories

### Parameters
None

### Request Example
```
curl "http://localhost:3000/fresh_stories"
```

### Response
#### Success Response (200)
- **body** (JSON) - The rows of the view.

#### Response Example
```json
[
  {
    "id": 1,
    "title": "Today's story",
    "pinned": true,
    "published": "2024-01-01T00:00:00Z"
  }
]
```
```

--------------------------------

### Set up PostGIS and sample table

Source: https://docs.postgrest.org/en/v13/_sources/references/api/media_type_handlers.rst.txt

Sets up a PostGIS extension and a sample table with geometry data, used as the basis for the TWKB handler example.

```postgres
create extension postgis;

create table lines (
  id   int primary key
, name text
, geom geometry(LINESTRING, 4326)
);

insert into lines values (1, 'line-1', 'LINESTRING(1 1,5 5)'::geometry), (2, 'line-2', 'LINESTRING(2 2,6 6)'::geometry);
```

--------------------------------

### Run PostgREST server

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Run the PostgREST server using the tutorial.conf configuration file, either from a package manager installation or from the binary.

```bash
# Running postgrest installed from a package manager
postgrest tutorial.conf

# Running postgrest binary
./postgrest tutorial.conf
```

--------------------------------

### Create add_them function

Source: https://docs.postgrest.org/en/v13/_sources/references/api/functions.rst.txt

Defines a PostgreSQL function that adds two integers. This function is used in the POST and GET examples.

```postgres
CREATE FUNCTION add_them(a integer, b integer)
RETURNS integer AS $$
 SELECT a + b;
$$ LANGUAGE SQL IMMUTABLE;
```

--------------------------------

### GET /people?or=(age.lt.18,age.gt.21)

Source: https://docs.postgrest.org/en/v13/references/api/tables_views.html

Combines multiple conditions using the `or` operator. This example returns people under 18 or over 21.

```APIDOC
## GET /people?or=(age.lt.18,age.gt.21)

### Description
Combines multiple conditions using the `or` operator. This example returns people under 18 or over 21.

### Method
GET

### Endpoint
/people

### Parameters
#### Query Parameters
- **or** (string) - Required - Logical OR expression with conditions.

### Request Example
```
curl "http://localhost:3000/people?or=(age.lt.18,age.gt.21)"
```

### Response
#### Success Response (200)
- **body** (JSON) - The filtered rows.

#### Response Example
```json
[
  {
    "id": 4,
    "name": "Bob",
    "age": 17
  },
  {
    "id": 5,
    "name": "Carol",
    "age": 22
  }
]
```
```

--------------------------------

### Run PostgreSQL in Docker

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Starts a PostgreSQL container named 'tutorial' as a daemon, exposing port 5432 to the host. Requires Docker and that port 5432 is not already in use; otherwise, change the first 5432 to another port (e.g., 5433:5432) and adjust the config file in Step 5.

```bash
sudo docker run --name tutorial -p 5432:5432 \
                -e POSTGRES_PASSWORD=notused \
                -d postgres
```

--------------------------------

### Show CLI help with postgrest [-h|--help]

Source: https://docs.postgrest.org/en/v13/_sources/references/cli.rst.txt

Shows all the commands available.

```bash
$ postgrest [-h|--help]
```

--------------------------------

### GET /projects?id=gt.10

Source: https://docs.postgrest.org/en/v13/_sources/references/api/resource_representation.rst.txt

Example of querying the projects table with a filter and requesting a JSON array response with null values stripped.

```APIDOC
## GET /projects?id=gt.10

### Description
Fetches projects with id greater than 10, returning a JSON array with null values stripped.

### Method
GET

### Endpoint
/projects?id=gt.10

### Parameters
#### Query Parameters
- **id** (string) - Required - Filter expression: `gt.10` (greater than 10)

### Request Example
```
curl "http://localhost:3000/projects?id=gt.10" \
  -H "Accept: application/vnd.pgrst.array+json;nulls=stripped"
```

### Response
#### Success Response (200)
Returns a JSON array of project objects. Null fields are omitted.

#### Response Example
```json
[
  { "id": 11, "name": "OSX", "client_id": 1, "another_col": "val" },
  { "id": 12, "name": "ProjectX" },
  { "id": 13, "name": "Y"}
]
```
```

--------------------------------

### GET /people?age=lt.13

Source: https://docs.postgrest.org/en/v13/_sources/references/api/tables_views.rst.txt

Filters result rows by adding conditions on columns. This example returns people aged under 13 years old.

```APIDOC
## GET /people?age=lt.13

### Description
Filters result rows by adding conditions on columns. This example returns people aged under 13 years old.

### Method
GET

### Endpoint
/people?age=lt.13

### Parameters
#### Query Parameters
- **age** (string) - Required - Filter condition using operator 'lt' (less than).

### Request Example
```bash
curl "http://localhost:3000/people?age=lt.13"
```

### Response
#### Success Response (200)
- Returns the filtered rows in JSON format.

#### Response Example
(Not provided in source)

```

--------------------------------

### GET /people?age=gte.18&student=is.true

Source: https://docs.postgrest.org/en/v13/_sources/references/api/tables_views.rst.txt

Evaluates multiple conditions on columns by adding more query string parameters. This example returns people who are 18 or older and are students.

```APIDOC
## GET /people?age=gte.18&student=is.true

### Description
Evaluates multiple conditions on columns by adding more query string parameters. This example returns people who are 18 or older and are students.

### Method
GET

### Endpoint
/people?age=gte.18&student=is.true

### Parameters
#### Query Parameters
- **age** (string) - Required - Filter condition using operator 'gte' (greater than or equal).
- **student** (string) - Required - Filter condition using operator 'is' (is true).

### Request Example
```bash
curl "http://localhost:3000/people?age=gte.18&student=is.true"
```

### Response
#### Success Response (200)
- Returns the filtered rows in JSON format.

#### Response Example
(Not provided in source)

```

--------------------------------

### Check PostgREST Help

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Prints the PostgREST help page with its version and available options. Run after installing PostgREST via a package manager.

```bash
postgrest -h
```

--------------------------------

### Get request headers, cookies, and JWT claims

Source: https://docs.postgrest.org/en/v13/references/transactions.html

Examples of retrieving all headers, a single header, a cookie value, and a JWT claim using current_setting and JSON operators.

```sql
-- To get all the headers sent in the request
SELECT current_setting('request.headers', true)::json;

-- To get a single header, you can use JSON arrow operators
SELECT current_setting('request.headers', true)::json->>'user-agent';

-- value of sessionId in a cookie
SELECT current_setting('request.cookies', true)::json->>'sessionId';

-- value of the email claim in a jwt
SELECT current_setting('request.jwt.claims', true)::json->>'email';

```

--------------------------------

### Run postgrest with --help flag

Source: https://docs.postgrest.org/en/v13/_sources/explanations/install.rst.txt

Run the postgrest binary with the --help flag to see usage instructions. The command differs depending on whether postgrest was installed from a binary or a package manager.

```bash
# Running postgrest binary
./postgrest --help

# Running postgrest installed from a package manager
postgrest --help

# You should see a usage help message
```

--------------------------------

### Delete entire table (dangerous)

Source: https://docs.postgrest.org/en/v13/_sources/integrations/pg-safeupdate.rst.txt

Example API request that deletes the entire table by omitting the query parameter. This can happen accidentally, e.g., switching from GET to DELETE.

```bash
curl "http://localhost:3000/logs" -X DELETE
```

--------------------------------

### Build PostgREST binary with Stack

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Clones the PostgREST repository and builds the binary with Stack, using --install-ghc for the first build and --local-bin-path to set the installation directory. Requires installing Stack and library dependencies for your OS first. If the build fails on systems with less than 1GB of memory, add a swap file.

```bash
git clone https://github.com/PostgREST/postgrest.git
cd postgrest

# adjust local-bin-path to taste
stack build --install-ghc --copy-bins --local-bin-path /usr/local/bin

```

--------------------------------

### GET /people?grade=gte.90&student=is.true&or=(age.eq.14,not.and(age.gte.11,age.lte.17))

Source: https://docs.postgrest.org/en/v13/references/api/tables_views.html

Applies complex logic to conditions using logical operators. This example combines multiple conditions with AND and OR.

```APIDOC
## GET /people?grade=gte.90&student=is.true&or=(age.eq.14,not.and(age.gte.11,age.lte.17))

### Description
Applies complex logic to conditions using logical operators. This example combines multiple conditions with AND and OR.

### Method
GET

### Endpoint
/people

### Parameters
#### Query Parameters
- **grade** (string) - Required - Filter condition using the `gte` operator.
- **student** (string) - Required - Filter condition using the `is` operator.
- **or** (string) - Required - Logical OR expression with conditions, including negation and AND.

### Request Example
```
curl --get "http://localhost:3000/people" \
  -d "grade=gte.90" \
  -d "student=is.true" \
  -d "or=(age.eq.14,not.and(age.gte.11,age.lte.17))"
```

### Response
#### Success Response (200)
- **body** (JSON) - The filtered rows.

#### Response Example
```json
[
  {
    "id": 6,
    "name": "Dave",
    "age": 14,
    "grade": 95,
    "student": true
  }
]
```
```

--------------------------------

### Show PostgREST help

Source: https://docs.postgrest.org/en/v13/explanations/install.html

Shows PostgREST usage instructions. Use the first form when running the extracted binary, the second when PostgREST was installed via a package manager.

```sh
./postgrest --help
```

```sh
postgrest --help
```

--------------------------------

### Delete entire logs table (dangerous)

Source: https://docs.postgrest.org/en/v13/integrations/pg-safeupdate.html

Deletes all rows from the logs table because no query parameter is provided. This is dangerous and can happen accidentally, for example by switching a request from GET to DELETE.

```bash
curl "http://localhost:3000/logs" -X DELETE

```

--------------------------------

### PostgREST startup output

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Expected output when starting PostgREST, showing successful connection and listening on port 3000.

```text
Starting PostgREST 12.0.2...
Successfully connected to PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
API server listening on port 3000
```

--------------------------------

### GET /survey?or=(age_range.adj."[18,21)",age_range.cs."[30,35]")

Source: https://docs.postgrest.org/en/v13/references/api/tables_views.html

Handles reserved characters in filter values by wrapping them in double quotes. This example uses the `adj` and `cs` operators.

```APIDOC
## GET /survey?or=(age_range.adj."[18,21)",age_range.cs."[30,35]")

### Description
Handles reserved characters in filter values by wrapping them in double quotes. This example uses the `adj` and `cs` operators.

### Method
GET

### Endpoint
/survey

### Parameters
#### Query Parameters
- **or** (string) - Required - Logical OR expression with conditions, using quoted values for reserved characters.

### Request Example
```
curl -g 'http://localhost:3000/survey?or=(age_range.adj."[18,21)",age_range.cs."[30,35]")'
```

### Response
#### Success Response (200)
- **body** (JSON) - The filtered rows.

#### Response Example
```json
[
  {
    "id": 7,
    "age_range": "[18,21)"
  }
]
```
```

--------------------------------

### Estimated count request on small table

Source: https://docs.postgrest.org/en/v13/references/api/pagination_count.html

Use the Prefer: count=estimated header to get the exact count up to a threshold (db-max-rows) and planned count beyond. This example shows a request on a small table that returns the exact count.

```bash
curl "http://localhost:3000/smalltable?limit=25" -I \
  -H "Prefer: count=estimated"

```

--------------------------------

### Build PostgREST from source with Stack

Source: https://docs.postgrest.org/en/v13/_sources/explanations/install.rst.txt

Build PostgREST from source using Stack. The --install-ghc flag is only needed for the first build and can be omitted in subsequent builds. If building fails on a system with less than 1GB of memory, try adding a swap file.

```bash
git clone https://github.com/PostgREST/postgrest.git
cd postgrest

# adjust local-bin-path to taste
stack build --install-ghc --copy-bins --local-bin-path /usr/local/bin
```

--------------------------------

### Order films by director's last name descending

Source: https://docs.postgrest.org/en/v13/references/api/resource_embedding.html

Use the order parameter with the embedded resource path directors(last_name) to sort the top-level films by the director's last name in descending order. The example uses curl with the --get flag and -d options to send the query parameters.

```bash
# curl "http://localhost:3000/films?select=title,directors(last_name)&order=directors(last_name).desc"

curl --get "http://localhost:3000/films" \
  -d "select=title,directors(last_name)" \
  -d "order=directors(last_name).desc"

```

--------------------------------

### Set response settings with set_config

Source: https://docs.postgrest.org/en/v13/references/transactions.html

Shows how to set response settings using the 'response.' prefix and the set_config function.

```sql
-- response settings use the ``response.`` prefix.
SELECT
  set_config('response.<setting>', 'value1' ,true);

```

--------------------------------

### LIBPQ Environment Variables for Database Connection

Source: https://docs.postgrest.org/en/v13/_sources/references/configuration.rst.txt

Shows how to set database connection parameters using libpq environment variables. Any parameter not set in the connection string is read from these environment variables.

```text
PGHOST=localhost PGPORT=5433 PGUSER=authenticator PGDATABASE=postgres
```

--------------------------------

### Configure db-pre-request in tutorial.conf

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut1.rst.txt

Adds the db-pre-request configuration line to specify the auth.check_token function to run during attempted authentication.

```ini
# add this line to tutorial.conf

db-pre-request = "auth.check_token"
```

--------------------------------

### Sample output from login function

Source: https://docs.postgrest.org/en/v13/_sources/how-tos/sql-user-management-using-postgres-users-and-passwords.rst.txt

Example output from SELECT * FROM public.login('foo', 'bar');. Shows a single-row result containing a JWT token and the row count marker.

```text
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTg4ODQ3fQ.idBBHuDiQuN_S7JJ2v3pBOr9QypCliYQtCgwYOzAqEk
(1 row)
```

--------------------------------

### Example postgrest.conf configuration file

Source: https://docs.postgrest.org/en/v13/_sources/references/configuration.rst.txt

A sample configuration file showing the key-value pairs for db-uri, db-anon-role, jwt-secret, and server-port. The jwt-secret must be at least 32 characters.

```plaintext
# postgrest.conf

# The standard connection URI format, documented at
# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
db-uri       = "postgres://user:pass@host:5432/dbname"

# The database role to use when no client authentication is provided.
# Should differ from authenticator
db-anon-role = "anon"

# The secret to verify the JWT for authenticated requests with.
# Needs to be 32 characters minimum.
jwt-secret           = "reallyreallyreallyreallyverysafe"
jwt-secret-is-base64 = false

# Port the postgrest process is listening on for http requests
server-port = 3000
```

--------------------------------

### GET /rpc/plus_one

Source: https://docs.postgrest.org/en/v13/_sources/references/api/functions.rst.txt

This snippet documents the RPC endpoint for calling a function with an array parameter using a GET request.

```APIDOC
## GET /rpc/plus_one

### Description
Calls a function that takes an array parameter using a GET request. The array is passed as an array literal, with curly brackets URL-encoded.

### Method
GET

### Endpoint
/rpc/plus_one

### Parameters
#### Query Parameters
- **arr** (string) - Required - The array literal, e.g., `{1,2,3,4}` with curly brackets URL-encoded (`%7B` and `%7D`).

### Request Example
```bash
curl "http://localhost:3000/rpc/plus_one?arr=%7B1,2,3,4%7D"
```

### Response
#### Success Response (200)
- **body** (array of integers) - The resulting array with each element incremented by one.

#### Response Example
```json
[2,3,4,5]
```
```

--------------------------------

### Create table and set owner for testing

Source: https://docs.postgrest.org/en/v13/how-tos/sql-user-management-using-postgres-users-and-passwords.html

Creates a table 'foobar' and sets its owner to 'postgres' to test role-based access.

```sql
CREATE TABLE public.foobar(foo int, bar text, baz float);
ALTER TABLE public.foobar owner TO postgres;

```

--------------------------------

### Docker run and logs commands

Source: https://docs.postgrest.org/en/v13/_sources/references/observability.rst.txt

Commands to start a PostgreSQL container with the init.sh script and to follow its logs with docker logs.

```bash
docker run -v "$(pwd)/init.sh":"/docker-entrypoint-initdb.d/init.sh" -d postgres
docker logs -f <container-id>
```

--------------------------------

### Example metrics response

Source: https://docs.postgrest.org/en/v13/references/observability.html

Example HTTP response from the metrics endpoint showing schema cache metrics in Prometheus format.

```http
HTTP/1.1 200 OK
Content-Type: text/plain; charset=utf-8

# HELP pgrst_schema_cache_query_time_seconds The query time in seconds of the last schema cache load
# TYPE pgrst_schema_cache_query_time_seconds gauge
pgrst_schema_cache_query_time_seconds 1.5937927e-2
# HELP pgrst_schema_cache_loads_total The total number of times the schema cache was loaded
# TYPE pgrst_schema_cache_loads_total counter
pgrst_schema_cache_loads_total 1.0
...

```

--------------------------------

### Install PostgreSQL with Homebrew on macOS

Source: https://docs.postgrest.org/en/v13/_sources/tutorials/tut0.rst.txt

Install PostgreSQL via Homebrew on macOS, providing the required libraries for building PostgREST.

```bash
brew install postgresql
```

--------------------------------

### Create sample user foo with password bar

Source: https://docs.postgrest.org/en/v13/_sources/how-tos/sql-user-management-using-postgres-users-and-passwords.rst.txt

Creates a sample user 'foo' with password 'bar' for testing the login function.

```postgres
CREATE ROLE foo PASSWORD 'bar';
```

--------------------------------

### Run postgrest with a configuration file

Source: https://docs.postgrest.org/en/v13/_sources/explanations/install.rst.txt

Run the PostgREST server by passing a configuration file as its only argument. You can generate a sample config file with 'postgrest -e > postgrest.conf' and edit it to remove usage parts before use.

```bash
postgrest /path/to/postgrest.conf

# You can also generate a sample config file with
# postgrest -e > postgrest.conf
# You'll need to edit this file and remove the usage parts for postgrest to read it
```
Tokens:
Raw