5

I'm trying to DISABLE remote client access on my Postgres 9.5.7 database (running Ubuntu 16.04).

I made what I thought are the correct changes (see below) to no avail. I can think of two reasons why my changes wouldn't work:

  • I'm editing the wrong file
  • I'm not restarting the service properly

I thought I ruled these out, but remote clients can still talk. So what am I missing?

Actually, what I'm trying to do is revert my changes that enabled remote client access on so I can capture those changes and re-apply them with our salt stack. Before I start running deployment tests via salt I want to know that remote access is disabled so that I can be confident that my salt stack is making the changes.

Some data

I'm starting postgres using the same config file I'm editing (ps output reformatted for readability)

postgres@testweb:~$ ps -eaf | grep bin/post
postgres [snip] /usr/lib/postgresql/9.5/bin/postgres 
                -D /var/lib/postgresql/9.5/main 
                -c config_file=/etc/postgresql/9.5/main/postgresql.conf

postgres is configured to listen on the localhost port only (this was '*' earlier)

postgres@testweb:~$ grep listen_addresses /etc/postgresql/9.5/main/postgresql.conf
listen_addresses = 'localhost'      # what IP address(es) to listen on;

Host-based auth is set to localhost. This shouldn't matter if I'm DISABLING access, correct? My understanding is that if listen_addresses='localhost' then remote client access will not work.

postgres@testweb:~$ grep ^host /etc/postgresql/9.5/main/pg_hba.conf
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

I'm editing the correct config files, yet listen_addresses claims *

postgres@testweb:~$ sql
psql (9.5.7)
Type "help" for help.

postgres=# show config_file;
           config_file
------------------------------------------
/etc/postgresql/9.5/main/postgresql.conf
(1 row)

postgres=# show hba_file;
               hba_file
--------------------------------------
/etc/postgresql/9.5/main/pg_hba.conf
(1 row)

postgres=# show listen_addresses;
 listen_addresses
------------------
*

Proof at the OS level that we're still listening on ALL interfaces (that is, that the show list_addresses; command is in effect.

postgres@testweb:~$ netstat -nlt | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp6       0      0 :::5432                 :::*                    LISTEN

I have a custom conf file in conf.d, but I'm unclear if it's being read, because I didn't touch this in order to allow remote client access. Regardless, it is set to what I want for now

postgres@testweb:~$ cat /etc/postgresql/9.5/main/conf.d/custom.conf
listen_addresses = 'localhost'
shared_buffers = 4011MB
work_mem = 320MB
maintenance_work_mem = 1018MB
effective_cache_size = 8022MB
shared_preload_libraries = 'pg_stat_statements'

No stray config files appear on the server

postgres@testweb:~$ locate postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf.bak
/usr/lib/tmpfiles.d/postgresql.conf
/usr/share/postgresql/9.5/postgresql.conf.sample

postgres@testweb:~$ locate pg_hba.conf
/etc/postgresql/9.5/main/pg_hba.conf
/usr/share/postgresql/9.5/pg_hba.conf.sample

I restart the postgres instance with service postgresql restart (I've even tried restarting the server (ugh) just to make sure. Still no luck. I've verified that if I execute service postgresql stop that my remote clients fail.

Obviously, in futzing around to ENABLE remote client access I must have tweaked something else; but for the life of me I cannot think of what it was. I have undone the changes I remember making (in the two conf files). I must have missed something ... but what?

EDIT(s)

per a comment, if I log in specifying the host, I see the same result (AFAIK, I never set the postgres user password, so I'm accessing as root).

root@testweb:~# sudo -u postgres -h localhost psql
psql (9.5.7)
Type "help" for help.

postgres=# show listen_addresses;
 listen_addresses
------------------
 *
(1 row)

postgres=# show config_file;
               config_file
------------------------------------------
 /etc/postgresql/9.5/main/postgresql.conf
(1 row)

Per another comment, here is the output after executing service postgresql restart ... seems clean (unsure about the last line ... research (thanks Daniel) inidicates that it's "inconsequential")

2017-07-18 17:43:16.440 MDT [4911] LOG:  database system was shut down at 2017-07-18 17:43:15 MDT
2017-07-18 17:43:16.444 MDT [4911] LOG:  MultiXact member wraparound protections are now enabled
2017-07-18 17:43:16.447 MDT [4910] LOG:  database system is ready to accept connections
2017-07-18 17:43:16.447 MDT [4915] LOG:  autovacuum launcher started
2017-07-18 17:43:16.848 MDT [4917] [unknown]@[unknown] LOG:  incomplete startup packet
  • Have you tried psql -h localhost, you might be connecting to another version of PostgreSQL, if you have more than one installed. – joanolo Jul 18 '17 at 23:09
  • 1
    I tried that; I would have expected ps output to expose any other running instances, but it's worth double-checking – stv Jul 18 '17 at 23:18
  • Is there any message in the log suggestion that the config file could not be read? [I doubt that PostgreSQL would start at all..]. Is there any possibility that the setting listen_addresses = ... appears more than once in the config file? Or that it is # commented out? From your exhaustive list, I think you've already checked and double-checked every reasonable thing... so I'm firing a bit at will – joanolo Jul 18 '17 at 23:25
  • 3
    If include_dir is uncommented, there may be another stray listen_address in an alternate file. You may try find /etc/postgresql -type f -exec grep listen_addresses {} \; -print just in case. Also does changing other parameters work? – Daniel Vérité Jul 18 '17 at 23:32
  • include_dir is uncommented; I included the contents of the only file in there custom.conf ... there is a listen_addresses in there, but it's set to localhost. I've removed that line just to be sure (hadn't thought of that before). Restarted, no changes : ( Great idea though. – stv Jul 18 '17 at 23:49
6

Look at the postgresql.auto.conf file in your $PGDATA.

If someone uses the ALTER SYSTEM command to change the settings, you will find the value there.

postgresql.auto.conf is read after postgresql.confand each setting put in this file overwrite those in postgresql.conf...

It's worth giving a look...

  • 1
    Argh! This was it ... as soon as I read this answer I remember attempting to fix my initial setup by executing tan alter system command (I had issues in my pg_hba.confthat needed resolving, and I later corrected these. Executing ALTER SYSTEM RESET LISTEN_ADDRESSES did the trick. Thanks! – stv Jul 19 '17 at 15:48
  • I am trying to set the listen_addresses to * from localhost using command ALTER SYSTEM SET listen_addresses TO "*"; . But I am not able to change the pramater. Am I doing anythng wrong in this command; – soumya sambit Kunda Jan 6 at 13:56
  • Did you restart the server ? This parameter needs restart as mentionned in documentation (postgresql.org/docs/current/…) – Arkhena Jan 9 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.