Query Security

[treystaff]

We should consider some security barrier on the query view. This view takes a raw string as input without any check to ensure someone isn't doing a bunch of DELETE FROM queries. We could filter by IP (#72), but we should probably also consider checking the query string as well. Don't let queries through with key terms like 'delete', 'alter', 'update', 'drop', 'update', etc.

The query function should basically be limited only to fetching information (select queries). Other functionality is already available through views (which limits delete, insert, etc to predictable and controlled queries). Custom sql not let through should probably be limited to admins that have direct access to the psql interface anyway.

@jpaden, what do you thing?

[jpaden]

I think it is nice to be able to build queries in Matlab (or Python… but limited experience on my end there). I would be very happy to have that be extremely limited (like from the host server only), or possibly to just open it up during maintenance periods when the server is not accepting any outside connections. So… I think we could turn off the interface except for selects. But it would be nice to document how to turn it back on easily for doing complex maintenance chores.

John Paden
Center for Remote Sensing of Ice Sheets (CReSIS)
Nichols Hall, Room 325
2335 Irving Hill Rd
Lawrence, KS 66045
USA
Cell Phone: (303) 818-6406
Work Phone: (785) 864-1692

From: Trey Stafford [mailto:[email protected]]
Sent: August 05, 2014 11:25 AM
To: CReSIS/OPS
Cc: Paden, John D.
Subject: [OPS] Query Security (#80)

We should consider some security barrier on the query view. This view takes a raw string as input without any check to ensure someone isn't doing a bunch of DELETE FROM queries. We could filter by IP (#72#72), but we should probably also consider checking the query string as well. Don't let queries through with key terms like 'delete', 'alter', 'update', 'drop', 'update', etc.

The query function should basically be limited only to fetching information (select queries). Other functionality is already available through views (which limits delete, insert, etc to predictable and controlled queries). Custom sql not let through should probably be limited to admins that have direct access to the psql interface anyway.

@jpadenhttps://github.com/jpaden, what do you thing?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/80.