Using HTTP to access postgres
On a whim I wanted to try making HTTP calls to access postgres so I slapped something together and called it pg_httpd. For the moment it can only query tables. For example, "http://localhost:54321/postgres/pg_stat_activity" will connect to the database named "postgres" and execute the SQL statement "SELECT * FROM pg_stat_activity;". It'll return the query output as JSON.
An example using curl:
An example using curl:
curl http://localhost:54321/postgres/pg_stat_activityThe resulting JSON has been reformatted for easier viewing:
[
{
"datid":"12774",
"datname":"postgres",
"procpid":28887,
"usesysid":"10",
"usename":"markwkm",
"application_name":"",
"client_addr":null,
"client_hostname":null,
"client_port":-1,
"backend_start":"2012-05-26 14:11:41.91862-07",
"xact_start":"2012-05-26 14:11:41.920169-07",
"query_start":"2012-05-26 14:11:41.920169-07",
"waiting":false,
"current_query":"SELECT * FROM pg_stat_activity;"
}
]
Labels: postgres, postgresql
posted by markwkm at 2:20 PM
5 Comments:
It would be nice if you could access all stored procedures via HTTP.
The pg_httpd database user could be granted EXECUTE rights only to the SPs you want to expose.
It would be very useful to quickly build webapps, eliminating the need of a middle layer.
pg_httpd will use pg_catalog.pg_proc to resolve the matching function, based on the method and parameters specified in the JSON/RPC call.
Functions return values, records or setof records, which maps perfectly to JSON.
This would also eliminate the need of evil named query files.
Great initiative!
Awesome! I was looking for a REST API kinda access to Postgres and here is it in some form!!
Are you planning improvements? Perhaps this should be in the contrib for wider acceptance?
Some sort of api-key access and retrieval of CRUD access would be nice!
Thanks!
see also http://dbrelay.com/
I don't have any formal plans for improvements. I'll try to do more but I can't promise any rapid development.
It would be easy to put this up on PGXN but I may hold back until there's more to show.
Wouldn't it be safer to implement such functionality as CGI? I'd fear to code a simple socket listener and expose it on the internet. The standard web servers are probably more familiar with various attack techniques as well as the appropriate protection.
Post a Comment
<< Home