Avoid unrestricted access to the Mastodon DB (#5)

* Use a limited, read-only view of public toots

This is less scary than unrestricted access to the production database...

* README: Update install process

* README: Update SQL query

* README: Update paragraph on direct DB access

1 files changed, 25 insertions, 0 deletions

diff --git a/install.sql b/install.sql
new file mode 100644
index 0000000..5fe7c82
--- /dev/null
+++ b/install.sql
@@ -0,0 +1,25 @@
+-- -*- mode: sql; sql-product: postgres -*-
+
+-- Create a login role for ambassador
+CREATE USER ambassador;
+
+-- Use this if your deployment uses passwords rather than peer authentication:
+-- ALTER ROLE mastodon_ambassador WITH PASSWORD 'something secret, hopefully';
+--
+-- Note that PostgreSQL supports setting “encrypted” (hashed) passwords,
+-- which is a better option if the password must be stored in some configuration
+-- management tool.
+
+
+-- Now, create the view that ambassador actually uses
+CREATE VIEW public_toots AS
+  SELECT *
+    FROM statuses
+   WHERE visibility = 0
+;
+
+-- Make sure the role doesn't have access to anything undesireable
+REVOKE ALL FROM ambassador;
+
+-- Let ambassador select from the view
+GRANT SELECT ON public_toots TO ambassador;