Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Newbie
    Join Date
    Jan 2015
    Posts
    4

    Default Direct access to Postgres DB

    In case you need better reports than what's offered with Untangle, or if you are sick of their constant changes, or if you need to centrally gather data from multiple devices, and miss the emailed reports, you may want to enable direct access to your reporting DB.

    The articles I've found were from 2009 or before, so here's an updated version of what worked for us.

    Enjoy

    Enable PostgreSQL to listen on all the addresses and accept connections from the local network

    /etc/postgresql/9.4/main/pg_hba.conf (allow connections for local network)

    # This line allows user reporter to connect to uvm database with a set password
    host uvm reporter samenet md5
    # Next line allows postgres user to connect from everywhere w/o a password
    #host all postgres samenet trust

    /etc/postgresql/9.4/main/postgresql.conf

    listen_addresses = '*'

    Restart the DB engine

    /etc/init.d/postgresql restart

    Check that PostgreSQL is listening

    [root @ untangle] ~ # netstat -putan | grep 5432
    tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3485/postgres
    tcp 0 0 127.0.0.1:5432 127.0.0.1:7774 ESTABLISHED 4476/postgres: post
    tcp 0 0 127.0.0.1:7774 127.0.0.1:5432 ESTABLISHED 3456/java

    [root @ untangle] ~ # /etc/init.d/postgresql restart
    [ ok ] Restarting PostgreSQL 9.4 database server: main.

    [root @ untangle] ~ # netstat -putan | grep 5432
    tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 40818/postgres
    tcp 0 0 127.0.0.1:5432 127.0.0.1:7774 FIN_WAIT2 -
    tcp 111 0 127.0.0.1:7774 127.0.0.1:5432 CLOSE_WAIT 3456/java
    tcp6 0 0 :::5432 :::* LISTEN 40818/postgres

    Create a user to query reports

    psql -U postgres -d uvm

    CREATE USER reporter WITH ENCRYPTED PASSWORD 'passwordfortheuser';
    GRANT CONNECT ON DATABASE uvm TO reporter;
    GRANT USAGE ON SCHEMA reports TO reporter;
    GRANT SELECT ON ALL SEQUENCES IN SCHEMA reports TO reporter;
    GRANT SELECT ON ALL TABLES IN SCHEMA reports to reporter;
    ALTER DEFAULT PRIVILEGES IN SCHEMA reports GRANT SELECT ON TABLES TO reporter;

    Connect to the DB using pgAdmin 4

    Use the reporter user with the chosen password

  2. #2
    Untangler jcoffin's Avatar
    Join Date
    Aug 2008
    Location
    Sunnyvale, CA
    Posts
    9,210

    Default

    FYI, in the coming release, 12.2.0, access to the database can be set in the GUI.

    Edit: Sorry, not for public use, just developer mode.
    Last edited by jcoffin; 11-28-2016 at 10:52 AM.
    Attention: Support and help on the Untangle Forums is provided by
    volunteers and community members like yourself.
    If you need Untangle support please call or email support@untangle.com

  3. #3
    Newbie
    Join Date
    Jan 2015
    Posts
    4

    Default

    Quote Originally Posted by jcoffin View Post
    FYI, in the coming release, 12.2.0, access to the database can be set in the GUI.
    Good to hear. When we asked support they seemed to imply that doing what I described was akin to opening the gates of hell

  4. #4
    Untangler jcoffin's Avatar
    Join Date
    Aug 2008
    Location
    Sunnyvale, CA
    Posts
    9,210

    Default

    Sorry, I just realized the database GUI is only in developer mode.
    Attention: Support and help on the Untangle Forums is provided by
    volunteers and community members like yourself.
    If you need Untangle support please call or email support@untangle.com

  5. #5
    Untanglit
    Join Date
    Jan 2017
    Posts
    29

    Default

    Apologies if this is an obvious one - is there somewhere I can switch to developer mode to get this or is that a separate build, etc? I am a developer, just never done anything on or related to untangle.

    Thanks.

  6. #6
    Untangler jcoffin's Avatar
    Join Date
    Aug 2008
    Location
    Sunnyvale, CA
    Posts
    9,210

    Default

    It requires emailing UID to support to have it enabled.

    More information here.

    https://forums.untangle.com/announce...pert-mode.html
    Attention: Support and help on the Untangle Forums is provided by
    volunteers and community members like yourself.
    If you need Untangle support please call or email support@untangle.com

  7. #7
    Untangle Junkie dmorris's Avatar
    Join Date
    Nov 2006
    Location
    San Carlos, CA
    Posts
    17,747

    Default

    just modify your input filter rules to allow access.
    nothing like expert mode is required.

    ​IMPORTANT: IF YOU GIVE THE WORLD ACCESS TO YOUR POSTGRES DATABASE THEN YOUR BOX WILL GET OWNED IN A MATTER OF HOURS

    READ THE WIKI INPUT FILTER RULES WARNINGS CAREFULLY
    Attention: Support and help on the Untangle Forums is provided by volunteers and community members like yourself.
    If you need Untangle support please call or email support@untangle.com

  8. #8
    Untangle Ninja sky-knight's Avatar
    Join Date
    Apr 2008
    Location
    Phoenix, AZ
    Posts
    25,304

    Default

    Quote Originally Posted by pdestefanis View Post
    Good to hear. When we asked support they seemed to imply that doing what I described was akin to opening the gates of hell
    They implied that because that's exactly what you're doing. See the previous post about watching the input filter rules, screw that up and you may as well not have Untangle at all, I dare say you'd be better off with public addressing on all your gear and no firewall at all.
    Rob Sandling, BS:SWE, MCP
    NexgenAppliances.com
    Phone: 866-794-8879 x201
    Email: support@nexgenappliances.com

  9. #9
    Untangle Junkie dmorris's Avatar
    Join Date
    Nov 2006
    Location
    San Carlos, CA
    Posts
    17,747

    Default

    A completely fabricated story that has no truth to it whatsoever:

    One time this customer calls with a massive server (100+ gigs of memory, 30+ cores) on a fairly large network with a big internet connection. He is having load issues with some random process.

    We login and see something running we've never heard of taking tons of CPU. Reading the logs we see someone is running these scripts on his machine and searching the internet for vulnerable machines. Further investigation reveals that he gave the whole world access to his postgres database.

    US: We see you allowed the entire world access to your postgres database and to run arbitrary commands on your server. Your server is compromised.
    HIM: I disabled that rule - can you fix the server? I don't want to reboot.
    US: No you don't understand you did this months ago and your server has been compromised. Its being used to attack other servers. You need to reinstall completely. I would take it offline immediately.
    HIM: That is unreasonable. I am upset.
    US: Sorry. This is why there are several very explicit warnings in the UI and documentation about how dangerous misconfiguring input filter rules is.
    HIM: But I read it on the Untangle forums.
    Attention: Support and help on the Untangle Forums is provided by volunteers and community members like yourself.
    If you need Untangle support please call or email support@untangle.com

  10. #10
    Newbie
    Join Date
    Jan 2015
    Posts
    4

    Default

    Quote Originally Posted by sky-knight View Post
    They implied that because that's exactly what you're doing. See the previous post about watching the input filter rules, screw that up and you may as well not have Untangle at all, I dare say you'd be better off with public addressing on all your gear and no firewall at all.
    The post is about how to access the database directly as a way to perform analysis. It is not meant to tell you how to keep your unit secure. Yes, if you make your DB accessible to everyone you have a good chance of being owned.

    If someone is looking for directly PG access, I assume the person has some knowledge of how to keep it secure.

    I don't believe in covering everything in warnings (call me a Darwinist), or to start a discussion about style. The intent was to add to the community.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

SEO by vBSEO 3.6.0 PL2