Install on Debian¶

the packages are in official repo

sudo apt install postgresql postgresql-client

run¶

sudo -u postgres psql

New User & Database¶

Create a regular system user account using adduser (skip this step to use an existing account):

sudo adduser mypguser #from regular shell

Switch to user postgres and create a new database user and a database:

sudo  su - postgres

createuser –pwprompt mypguser #from regular shell
createdb -O mypguser mypgdatabase

Connect as user mypguser to new database

or, if the OS user name is not the same as the database user name:

psql -d mypgdatabase -h localhost -U mypguser

you can also use a ~/.pgpass file

Add line for auth :

echo ‘hostname:port:mypgdatabase:mypguser:mypgpassword’ >> ~/.pgpass

Secure the file

chmod 600 ~/.pgpass Now you can easily connect with
psql -d mypgdatabase -h localhost -U mypguser

PostgreSQL Allow Remote Connections¶

After installing PostgreSQL, you can log in to the Server Locally using the postgres system user without a password. However, we are not allowed to connect to the server from a remote computer.

That is because, by default, PostgreSQL does not allow remote connections to the server.

Run the ss -tlnp command, you will see that the Postgres process only listens on 127.0.0.1 IP address (localhost).

Allowing Remote Connections is a Two-step Process¶

First, we need to change the listen_addresses option in the postgresql.conf, the main configuration file.

By default PostgreSQL only listens on localhost, which means we can only connect to the server locally. This behavior of the PostgreSQL server is controlled by the listen_addresses directive of the postgresql.conf.

ambagasdowa@kukulkan:~$ netstat  -ntlp | grep :5432
tcp        0      0 127.0.0.0:5432        0.0.0.0:*               LISTEN      -

In Debian, the location of the main configuration file is /etc/postgresql/15/main/postgresql.conf, if you are running PostgreSQL 15.

Enable local-net and tunnel , localhost is only for loop interface¶

listen_addresses = 'localhost,192.168.1.1,10.44.45.10'

Note

listen_addresses (string) Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. The entry 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening for all IPv6 addresses. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. If the list is not empty, the server will start if it can listen on at least one TCP/IP address. A warning will be emitted for any TCP/IP address which cannot be opened. The default value is localhost, which allows only local TCP/IP “loopback” connections to be made.

After that, we should add a host record(s) to the pg_hba.conf file (Host Based Authentication file) to allow access to the remote computer. A host entry in the pg_hba.conf file is used to specify hosts that are allowed to connect to the PostgreSQL server. In Ubuntu, the pg_hba.conf file is in the same directory as the postgresql.conf file.

The pg_hba.conf file manages client authentication between the PostgreSQL server and the client computer (Local and Remote). It allows you to define who is allowed to connect to which databases from which computers, like a Firewall.

host    all    postgres    192.168.1.1/32    trust

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
#host    all    postgres    192.168.1.1/32    trust # no password required
host    all    ambagasdowa    192.168.1.224/32    scram-sha-256 # password required
host    all    ambagasdowa    10.44.45.10/32    scram-sha-256 # password required
host    all    ambagasdowa    10.44.45.224/32    scram-sha-256 # password required

Changing pg_hba.conf requires a PostgreSQL service reload:

output

ambagasdowa@kukulkan:~$ netstat  -ntlp | grep :5432
tcp        0      0 10.44.45.10:5432        0.0.0.0:*               LISTEN      -
tcp        0      0 192.168.1.1:5432        0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -
tcp6       0      0 ::1:5432                :::*                    LISTEN      -

Create User and DB lua

postgres=# create database mydb;
CREATE DATABASE
postgres=# create user myuser with encrypted password 'pass123';
CREATE ROLE
postgres=# grant all privileges on database mydb to myuser;
GRANT

Last update: May 01, 2025