1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
# pg×html
This is a tool to make custom web interfaces to PostgreSQL databases,
using simple and standard technologies:
- SQL for querying
- XSLT for templating (translation of XML query results into XHTML)
- HTML forms for user input
- Optional HTTP basic authentication for PostgreSQL authentication
URL query parameters are available for use from XSLTs. SQL query
templates also can use those, as well as HTML form data submitted with
the POST method.
Request timeouts are enforced and do cancel DB queries, but otherwise
it relies on PostgreSQL for access permissions and security policies,
as well as for any business logic that may be needed.
It is based on WAI, and can be used with (S,Fast)CGI, or various other
methods.
## Usage
### Invocation
See [wai-cli](https://hackage.haskell.org/package/wai-cli) for CLI
arguments. The used environment variables are:
- `TIMEOUT`: request timeout in seconds, 10 by default.
- `XSLT_DIR`: a directory to read XSLT files from, current working
directory by default.
Regular [libpq environment
variables](https://www.postgresql.org/docs/current/libpq-envars.html)
are used for database connections.
### Templating
URL query parameters are made visible to XSLTs as `xsl:param`
parameters. The documents they get applied to are either the results
of SQL queries (which are expected to return a single XML document,
using `query_to_xml` or similar functions), or error documents (which
contain error details) in case of an SQL error.
The XSLTs are taken from `XSLT_DIR`, using file name from the URL
query, with its extension changed to `xsl`.
### Querying
SQL queries provided in the `q` URL query parameter get executed, with
some substitutions to handle HTML forms:
- `f:<name>` for POST parameters
- `q:<name>` for GET parameters
- `:fields` for POST parameter names
- `:values` for POST parameter values (in the same order as the names)
`:fields` and `:values` are unnecessary, but are provided for
convenience of insert/upsert operations.
SQL queries get tokenized by splitting into words and reassembled
afterwards, hence some whitespace separation is needed.
### Authentication
Presence of `authorised` in the URL path requires HTTP basic
authentication, and the provided credentials are used directly for
PostgreSQL authentication.
### Web server
This is intended to be used with an HTTP server, which would take care
of encryption, compression, static files, redirects, and so on, while
pgxhtml only focuses on providing a web interface to a database.
### Security checklist
- Regular privileges (see
[GRANT](https://www.postgresql.org/docs/current/sql-grant.html))
should be set appropriately.
- [Row Security
Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
should be defined and enabled.
- [Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html)
are useful for data validation.
- [REVOKE CREATE ON SCHEMA public FROM
PUBLIC](https://www.postgresql.org/docs/current/ddl-schemas.html)
would likely be desired (perhaps granting `CREATE` just to the
database owner/administrators).
## See also
- [PostgREST](http://postgrest.org/), "a standalone web server that
turns your PostgreSQL database directly into a RESTful API".
- [automatic-api](https://github.com/dbohdan/automatic-api/), a list
of tools for automatic HTTP-based API generation.
|
