-
Notifications
You must be signed in to change notification settings - Fork 0
/
godfw-rasql-20161129.slide
104 lines (71 loc) · 2.76 KB
/
godfw-rasql-20161129.slide
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
102
103
104
RASQL - REST API for SQL
Viverae
November 29, 2016
Dallas, Texas, USA
John Scott
CTO of RJ2 Technologies
jmscott@setspace.com
jmscott@rj2tech.com
https://github.com/jmscott/talk
* REST API for SQL (rasql)
Transform SQL Queries in Files to REST Queries via HTTP Protocol
curl --user id:passwd http://localhost:8080/<schema>/<query-file>?a1=abc&a2=xyz
.code godfw-rasql-20161129/state.code
* Find Available REST Queries
.code godfw-rasql-20161129/pg_class.code
* Specification
- Build REST Queries from SQL Queries in Files
- Simple Configuration File
- Validate URL Query Arguments
- Encryption via Secure Sockets.
- Basic Authentification
- Self Documenting from SQL File
- Export Results in JSON, CSV and TSV
- Log Slow Queries
- Pool SQL Connections for SSL
* Only 1500 Lines of Go Code!
* Parse Preamble in SQL File
.code godfw-rasql-20161129/preamble.code
.caption https://github.com/jmscott/work/blob/master/rasql/pg_stat_activity-slow-count.sql
* Configuration File in JSON (abbreviated)
.code godfw-rasql-20161129/config-small.code
.caption https://github.com/jmscott/work/blob/master/rasql/pg_catalog.rasql.example
* All SQL Queries in pg_catalog.rasql.example
.code godfw-rasql-20161129/config-sql.code
* All HTTP Query Arguments in pg_catalog.rasql.example
.code godfw-rasql-20161129/config-http.code
.caption https://github.com/jmscott/work/blob/master/rasql/pg_catalog.rasql.example
Regular Expressions Validate Query Arguments.
SQL Layer Also Validates.
* Easy to Bind JSON Keys to Go Variables
.code godfw-rasql-20161129/json.code
* Extract Row as JSON, CSV or Tab Separated
.code godfw-rasql-20161129/state-csv.code
Content-Type: text/csv
* Basic Authentification is Apache Password Format
.code godfw-rasql-20161129/password.code
.caption https://github.com/jmscott/work/blob/master/rasql/password-rasql.example
Need to authenticate with SQL query!
* Adding https/SSL to Server is Trivial
.code godfw-rasql-20161129/ssl.code
.caption https://github.com/jmscott/work/blob/master/rasql/rasqld.go
* SSL Key Generation and Self Signing is Easy
Generate private key (.key)
.code godfw-rasql-20161129/tls-key.code
Generation of self-signed(x509) public key (PEM-encodings .pem|.crt) based on the private (.key)
.code godfw-rasql-20161129/tls-sign.code
May need insecure mode in curl during testing
curl --insecure http://localhost:8080...
* Enhancements
- Use GoLang Text Templates to Conditionally Rewrite SQL queries
- Authenticate with an SQL Query or PostgreSQL Auth
- Generate HTML Documentation of SQL
- Parse SQL to Extract Target List and Arguments.
- Monitor Changes in SQL Files
- View SQL Source Code from REST Query
- Authenticate with Trusted Keys
- Syslog
- SSL SQL Connections
- HTTP Redirect of http:// to https://
- Scan Directory for SQL Query Files
- Trusted Network