How to: Access the UVM database remotely

[sky-knight]

This tutorial is written for 7.0.1, it will work on any Untangle installation that is operating Postgres 8.3. That is to say, anything 6.2 or newer that was done with a fresh install.

Consider yourself warned, performing this procedure on your Untangle server is rife with security implications. This is meant for development purposes only and not to be put into production.

So now on to business...

By default Untangle's packet filter is a block all configuration given a destination to the local server. As such, before we can go anywhere we need to define a packet filter rule to allow our local network access to the postgres port. The following will allow only the internal network access.

Enabled: Checked
Description: Pass Postgres
Action: Pass
Source Interface: Internal
Destined Local
Protocol: TCP
Destination Port: 5432

Now that IPTables is out of the way let's get on to configuring postgres.

First stop the UVM and Postgres services.

/etc/init.d/untangle-vm stop
/etc/init.d/postgresql-8.3 stop

Now, edit

/etc/postgresql/8.3/main/postgresql.conf

Find the line that says

#listen_addresses = 'localhost'

And change it to

listen_addresses = '*'

Now edit

/etc/postgresql/8.3/main/pg_hba.conf

Find the line that says

host	all	all	127.0.0.1/32	trust

and change it to

host	all	all	0.0.0.0/0	trust

Now that postgrest is configured, we need to restart it and the UVM.

/etc/init.d/postgresql-8.3 start
/etc/init.d/untangle-vm start

Get a copy of pgAdmin from http://www.pgadmin.org/download/windows.php

I'm using version 1.10.0 at this time

Once it is installed, click the plug icon in the top left to open the new server registration window.

Fill the box:
Name: Untangle
Host: Internal IP of your Untangle here
Port: 5432
SSL: allow
Maintenance DB: postgres
Username: postgres
Password: uvm

The rest of the window leave blank and click OK.

You should now be able to navigate the uvm database from your windows station.

[slopert]

Is there some documentation where everything is put? I want to find the webfilter settings, so I can filter out all white listed sites from the report. Untangle logs the website first from the deny groups. White listing does log also.
I have put in the address of our webserver (iiyama.com) in the whitelist to test this.

Slopert

[sky-knight]

slopert, if you have tried my example and made a connection attempt it would take you 30 seconds to find the appropriate table...

This database once laid out within pgadmin almost maps itself.

Is there documentation? No, we're on our own in here.

[sky-knight]

Ok so...

select * from reports.sessions where reports.sessions.hname='10.10.50.10;

Seems to be revealing a nice list of TCP/UDP traffic as it passes through the firewall module for a specific client. But I need a Dev's input to tell me what the c2p_bytes, p2c_bytes, s2p_bytes, and p2s_bytes fields mean... so I can total up the bytes used...

[doubleduh]

Ah great, I can finally get to the data with this guide. Now linking it to SQL server should be no problem anymore. For any other linux newbies like me (I managed to fuck up the config file with vim and ed), to edit files I've found you can use the nano command, it's a pretty simple and straight-forward text editor.

[sky-knight]

If you're a windows user, install WinSCP.

Connect to Untangle just like you do with putty, and it will give you an FTP like view of the file system. Navigate to the file you need to edit, and WinSCP has a built in Notepad like editor for you to use right in the comfort of your windows.

vi? vim? ed? nano? Why? I use WinSCP to edit my files right on my desktop.

[sky-knight]

Thanks to Danp for his help refining my query. And many thanks to Untangle support for providing me the definition I needed on the fields in the database.

select 
	date_trunc('day',trunc_time) as date,
	sum(c2s_bytes)/1048576 as upload_in_MiB, 
	sum(s2c_bytes)/1048576 as download_in_MiB
	from reports.session_totals 
where hname='10.10.50.10' 
	group by date
	order by date;

This query returns the total upload, and download in mibibytes for the single computer at 10.10.50.10, broken out by day, for the entire length of data Untangle has available. In my case, the 30 day max.

[lincphil]

I can not access my postgres database remotely. I did the instructions and double checked them. I get an message saying:::
Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "000.00.0.251" and accepting TCP/IP connections on port 5432?
If you encounter this message, please check if the server you're trying to contact is actually running PostgreSQL on the given port. Test if you have network connectivity from your client to the server host using ping or equivalent tools. Is your network / VPN / SSH tunnel / firewall configured correctly?
I can access it by winscp and putty app's why can I not do it with Pgadmin program??
Lincoln

[sky-knight]

That error means you didn't edit the postgres configuration properly.

What version of Untangle are you running?

[lincphil]

I am using the lastest verison of Untangle 7.0.1.