Need IPs opened for Google Data Studio connection

[bee1]

Hi All,

I was wondering how I go about getting the following IPs allowed to connect to my PostgresDB hosted on Tommy? It seems things have changed on their end and need these opened up now for continued use.

The google article I was sent by Google: https://support.google.com/datastudio/answer/7288010?hl=en&ref_topic=7332343#zippy=%2Cin-this-article%2Cshow-the-list-of-ip-addresses

Thanks!

[Krydos]

Which user would you like to have remote access to your PostgreSQL database?

[bee1]

Hi Krydos, the following users:

bee1_sensor_user

bee1_remote

bee1_data

on the "bee1_sensor_data" db please.

[Krydos]

Can you connect remotely now?

[bee1]

Hey Krydos,

Sorry for the lack of response on this - I've been trying to troubleshoot this but I still cannot commit data into the database. Since my account was moved to Plesk did the IP change? Right now I have it pointing to 65.19.143.6 - is that still correct?

[Krydos]

No, 65.19.143.6 is the old cPanel server. Only one server can use an IP address at a time so Plesk is using 65.19.141.77. Normally when we rebuild a server we shutdown the old server and then rebuild it on the same IP, but since we wanted to have both online at the same time we had to do it this way.

[bee1]

Hey @Krydos,

Thanks for your help on the above IP address correction. It seems I am still having an issue. I can connect to the database but it says my tables do not exist. I am trying to insert into the "bee_data_table" with the "bee1_sensor_user". Does this user have insert rights?

 

[Krydos]

We've discovered that the transfer script doesn't transfer permissions correctly for postgresql databases. The data is there, but you can't see it until I fix the permissions. Can you insert data now?

[bee1]

Hey @Krydos,

 I am still getting this error....

psycopg2.errors.UndefinedTable: relation "bee_data_table" does not exist
LINE 1: INSERT INTO bee_data_table (beekeeper_id, sensor_id, hive_te...

There is a little carrot ^ under the "b" of "bee_data_table" on the LINE 1

Here is what I am trying to insert:

INSERT INTO bee_data_table (beekeeper_id, sensor_id, hive_temp, hive_hum, weather_temp, weather_hum, time_taken) VALUES (ian, 2, 10, 10, 10, 10, 2020-09-29 13:07:09)

I am using the bee1_sensor_user, into the bee1_sensor_data DB

Any thoughts would be appreciated, thanks!

[Krydos]

Oh, weird. Here are your tables on cPanel:

postgres=# \c bee1_sensor_data
You are now connected to database "bee1_sensor_data" as user "postgres".
bee1_sensor_data=# \dt+
                            List of relations
 Schema |      Name      | Type  |      Owner       | Size  | Description
--------+----------------+-------+------------------+-------+-------------
 public | bee_data_table | table | bee1_sensor_data | 12 MB |
 public | beekeepers     | table | bee1_sensor_data | 40 kB |
(2 rows)

And here are your tables on Plesk:

postgres=# \c bee1_sensor_data
You are now connected to database "bee1_sensor_data" as user "postgres".
bee1_sensor_data=# \dt+
                                    List of relations
 Schema |    Name    | Type  |      Owner       | Persistence |    Size    | Des
cription
--------+------------+-------+------------------+-------------+------------+----
---------
 public | beekeepers | table | bee1_sensor_user | permanent   | 8192 bytes |
 public | data       | table | bee1_sensor_user | permanent   | 11 MB      |
(2 rows)

For some reason the table seems to have been renamed from "bee_data_table" to just "data", but they both seem to have a similar amount of data in them 11 MB and 12 MB. I have no idea why a table would get renamed. Can you insert into data? You probably have permissions to rename the table back to what it was now if you need to.

[bee1]

Hey,

So - I've tried all sorts of way to get this to work and it is just not getting there, haha. I am going to create a new table and see if I can commit into that and then check back in with you, I appreciate the help so far!

 

[bee1]

Hey @Krydos

Circling back to the original post on this thread. I was not able to get that database to work, so I created a new database 'bee1_hives' with the user 'bee1_hive_user'. The bee1_hives db now works and I am currently submitting data into it.

My next dilemma is that I used to be able to connect a postgres db to Google Data Studio, but when I try to connect this new db I am getting an error on connection (there is no record of what their error codes mean unfortunately). I am trying to troubleshoot this, so I guess my question and thoughts are...

- The bee1_hive_user is able to submit data into the db, but is there an additional permission which needs to be enabled for remote connection, reading?

- Do you need to allow connection from the Google Data Studio IPs? If so these are the IPs I need opened please, if possible.

• 142.251.74.0/23
• 142.251.74.0/23
• 2001:4860:4807::/48 (Optional, for platforms that support IPv6)
• 74.125.0.0/16

Reference Article: https://support.google.com/looker-studio/answer/11521624?visit_id=638028271131359562-3095635232&p=jdbc-ip-23&rd=1#jul-14-2022

 

[Krydos]

Can you connect from Google Data Studio now?

[bee1]

No

I've been trying for the past day and doing some troubleshooting but I am not sure it will work. I found on their FAQ that they only support up through Version 12, and from my understanding HelioHost is using 13.x, correct?

https://support.google.com/looker-studio/answer/7288010#how-to-connect-to-postgresql&zippy=%2Cin-this-article

[Krydos]

Ahh, that might be it then. Tommy is running PostgreSQL 13 and Johnny is going to have 14.