New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Enable DB integrations such as Zapier #230

Open
Matt-Yorkley opened this Issue Aug 6, 2018 · 2 comments

Comments

Projects
None yet
2 participants
@Matt-Yorkley
Copy link
Collaborator

Matt-Yorkley commented Aug 6, 2018

In the UK we've started using Zapier, which is a service that integrates with the database and allows for a wide range of extra functionality, like creating custom reports for enterprises, exporting order data to accounting software, or sending email alerts when something gets added.

It involves creating a postgresql user with very restricted permissions, and enabling the service to connect directly to the DB (read-only).

It looks like this could be useful for multiple instances, and since it involves server configuration we should probably add an optional role for it to ofn-install and decide on the best way to do it. There are some security issues to discuss as well.

@Matt-Yorkley Matt-Yorkley self-assigned this Aug 6, 2018

@Matt-Yorkley

This comment has been minimized.

Copy link
Collaborator

Matt-Yorkley commented Aug 7, 2018

The manual process looks like this. I'll look at making a role for it, maybe a generic one with variables for user, password, and ip_address

MAKE A SECURE PASSWORD:
----------------------

openssl rand -hex 128


CREATE THE USER:
----------------

sudo -u postgres psql openfoodnetwork

CREATE ROLE zapier WITH PASSWORD 'secure_password' LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT USAGE ON SCHEMA public TO zapier;

GRANT CONNECT ON DATABASE openfoodnetwork TO zapier;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO zapier;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO zapier;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO zapier;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO zapier;



ALLOW ACCESS:
-------------

sudo su postgres

echo "hostssl openfoodnetwork zapier 54.86.9.50/24 md5" >> /etc/postgresql/9.5/main/pg_hba.conf

echo "listen_addresses = '*'" >> /etc/postgresql/9.5/main/postgresql.conf

sudo systemctl reload postgresql

@sauloperez

This comment has been minimized.

Copy link
Contributor

sauloperez commented Sep 5, 2018

Looks good to me. In Katuma we also need to do the same to allow Metabase queryingy the DB. I initially thought of doing that from our custom additions to ofn-install (https://github.com/coopdevs/katuma-provisioning) but we'll follow the same approach in any case.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment