Firewall & Database Permissions: How do I allow Zapier access to the server and MySQL?

7 posts / 0 new
Last post
#1 Mon, 10/28/2019 - 10:09
croco2511

Firewall & Database Permissions: How do I allow Zapier access to the server and MySQL?

Hi!

I've got Virtualmin running successfully on Amazon EC2 (using a step-by-step tutorial). I use the server to run Sendy, a newsletter program. In order to integrate Sendy now with Facebook Ads (and other apps), I want to use Zapier. Zapier shows no error messages when I connect FB and Sendy and do a test run (or make the Zap active). It can connect to Sendy and sees the brands and lists there just fine. BUT no new subscribers get added. I did some research and talked with Sendy as well as Zapier support. It looks like Zapier needs explicit permission to access the server and database. More research followed. I found out the IP (54.86.9.50/32) that Zapier uses to connect to MySQL and added it to the security group I had created for the server instance on Amazon EC2. So far so good, but now I'm not 100% sure how to proceed: Do I have to whitelist Zapier's IP address in Virtualmin's firewall settings and if so, how do I do that? Or do I have to add database permissions or database user permissions for user@54.86.9.50/32 (and again how would I do that using Virtualmin's interface)? Or do I need to do both? Or is that wrong and there is something else to do?

Thanks for any clues you can give me. :)

Mon, 10/28/2019 - 14:03
adamjedgar

Why is it that marketing has to be so bloody mysterious and vague? I struggled to find anything even remotely helpful on Zappier website about how it's API works (maybe I need to look again?)

Anyway, as best I can tell, your interaction is between Zappier and the software that is being hosted on virtualmin (not virtualmin itself). From what you describe it sounds like you are trying to give Zappier root level access to the mysql database in Virtualmin. This seems strange to me...take the fllowing Wordpress example...

So if for example Zappier was being used to automate WordPress tasks, then Zappier would be communicating through WordPress...not virtualmin.

I am assuming this means it communicates with said software through TCP port 80?

If so, then the mysql permissions, usernames and passwords would be controlled via Amazon network firewall settings (ie ensuring port 80 is open) and the rest by WordPress on that port.

I am thinking that this would mean Virtualmin would simply see the Zappier connection as just being the equivalent of a wordpress admin log in on said port. There shouldn't be any firewall issues or permissions issues specific to virtualmin that control this...these would be controlled by my wordpress example...because WordPress, once setup correctly, controls it own mysql database and it's own permissions. All that is needed in Virtualmin is a wordpress user and WordPress database.

So you would need to check the docs for the application being hosted on your Amazon instance to get the right permissions and from a virtualmin/Amazon network firewall perspective, just make sure the correct ports are open. If you are running fail2ban on virtualmin perhaps that might be blocking it but I don't see why unless you added a rule blocking access on port 80 for said application?

Now if you are trying to use Zappier to provision services on virtualmin that's a different story...but you haven't asked for that here.

In your example, swap WordPress for Sendy . You would need to check the Sendy documentation for help on this because Zappier is automating tasks with Sendy (I assume because I haven't a clue what Sendy is actually)

That's what I am thinking at least.

Note...I looked whilst on my mobile phone...I might have this all wrong.

AJECreative is the home of $5 webhosting, $15/month VPS servers (1cpu,1gb RAM, 25GB storage)
Centos7, Debian9, or Ubuntu18LTS
Available Control Panels = Centos-Webpanel, Cyberpanel, or Virtualmin

https://ajecreative.com.au

Mon, 10/28/2019 - 15:56
Dibs
Do I have to whitelist Zapier's IP address in Virtualmin's firewall settings and if so, how do I do that? Or do I have to add database permissions or database user permissions for user@54.86.9.50/32 (and again how would I do that using Virtualmin's interface)? Or do I need to do both? Or is that wrong and there is something else to do?

Both, as in

  1. You will need to open MySQL's port to the internet (3306 is default according to Google) and then restrict access to 54.86.9.59.
  2. Create a database user in the database in question and give it the appropriate permissions (hopefully read only on the table\s in question should be enough).

The following posts might help

https://www.virtualmin.com/node/18397 which mentions doing it from the command line, in which case https://www.garron.me/en/bits/iptables-open-port-for-specific-ip.html might shed some light on how to do it for a specific port & IP.

For the MySQL user\permissions - depends if you have phpMyAdmin or whatever it's called these days but I think you can do it from Webmin - Servers - MySQL Database Server : User Permissions - Create New User: looks like it should do what you need.

HIH

Dibs

Tue, 10/29/2019 - 03:58 (Reply to #3)
Jfro

Offtopic. @Dibbs You give a very nice example how the MONOPOL of the BIG G.. has reached the goals. Ofcourse a lot of people in the world do an worse think the same.

But in the real it wasn't google who start to say 3306 is default port for MQSQL. ( the way google is presenting information these days with their not search engine is confusing , to find/know out the source, which is in most cases ofcourse not google, though their way of presenting it to readers is so wrong. ( They may copy paste almost everything even ask money for those to presented on first page, if others do they could be banned by them....) don't be.eval... big google and little persons working for them!

BUT yup both posts above pointing out userrights and open ports in security firewall setting on the (virtual) box itself and the platform. There is also a security setting / variable to have MYSQL remote accesable yes or no !

Tue, 10/29/2019 - 11:31
Dibs

@Jfro - Google: it has it's upsides and it's downsides in respect to a user - folk do need to click thru to the links and evaluate them on the basis of who they are and what they say, what their "credentials" are and if what they say "matches" the rest of the "body" of knowledge. Just because something is on the web - doesn't mean it's true or correct. Or the person writing it fully understands the subject. Like the saying goes - "A little knowledge is dangerous."

MySQL - opening up ports to the external world can be dangerous but limiting it to one IP should minimise it. A DDOS springs to mind but a 3rd party could just as well inflict that on port 80\443 anyway - so to my mind, you aren't increasing the risk much if anything.

p.s. the port info was from dev.mysql.com - ;)

Tue, 10/29/2019 - 14:45
adamjedgar

Can I ask, why would the mysql port need opening to the world in this case? If one is running a local application driven by say php (in my example WordPress), isn't that supposed to remove the need for opening mysql port to the outside? Shouldn't the O.Ps app do the same as WordPress? I would have thought allowing external access directly to mysql is a bad idea?

AJECreative is the home of $5 webhosting, $15/month VPS servers (1cpu,1gb RAM, 25GB storage)
Centos7, Debian9, or Ubuntu18LTS
Available Control Panels = Centos-Webpanel, Cyberpanel, or Virtualmin

https://ajecreative.com.au

Tue, 10/29/2019 - 15:24 (Reply to #6)
Dibs

The MySQL port would need opening to the external Zapier app - which the OP said needed to access his MySQL database.

If just your own local app (PHP or otherwise) needed access - then you wouldn't open it to the "world".