Skip to content

Instantly share code, notes, and snippets.

@herberthobregon
Forked from josefglatz/a-sshAndSwitch.sh
Last active August 8, 2024 20:01
Show Gist options
  • Save herberthobregon/a9238b87e0d7928183584e81e70e0fba to your computer and use it in GitHub Desktop.
Save herberthobregon/a9238b87e0d7928183584e81e70e0fba to your computer and use it in GitHub Desktop.
Synology cheatsheet: how to query internal Synology postgresql database
# Connect to you NAS with YOUR admin account
ssh [email protected]
# go sudo
sudo -i
# switch to user postgres
su - postgres
# start psql CLI
psql
# list user and roles
\du
# Now be careful! May the force be with you

Login to the console of the Synology NAS, and make a copy of the PostgreSQL configuration, just to be sure:

sudo cp /etc/postgresql/pg_hba.conf /etc/postgresql/pg_hba.conf.orig

sudo cp /etc/postgresql/postgresql.conf /etc/postgresql/postgresql.conf.orig

Then open the following file:

sudo vim /etc/postgresql/postgresql.conf

Near the top, you should have a line saying:

listen_addresses = '127.0.0.1'

That means that PostgreSQL is only listening to the localhost address (i.e. no outside connectivity whatsoever).

Change it to:

listen_addresses = '127.0.0.1,192.168.1.200'

(where 192.168.1.200 is the IP address of your Synology NAS)

Note that there are no spaces before or after the comma (PostgreSQL is picky!).

Save the file. Now let's do the other one:

sudo vim /etc/postgresql/pg_hba.conf

The 'hba' in the name stands for 'host-based authentication', which is how PostgreSQL will figure out which devices/networks are allowed to connect.

By default, this file will only have the following lines, with two rules:

# TYPE   DATABASE  USER         ADDRESS           METHOD
local       all                 postgres                               peer map=pg_root
local       all                 all                                           peer

which essentially means that PostgreSQL will only accept connections from the local machine.

Add a third rule for your network, at the bottom of the file:

host       all                 all               192.168.1.0/24   trust

This means that, from now on, PostgreSQL will start accepting connections that come from inside your local network.

Save the file, and restart PostgreSQL with the new configuration:

sudo systemctl restart pgsql.service
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment