PHP Installation

Installation sudo apt -y install postgresql postgresql-contrib phppgadmin Install verification sudo -u postgres psql -c “SELECT version();”

systemctl start postgresql systemctl enable postgresql

systemctl status postgresql

Switching user to postgres sudo su - postgres psql To exit out of the PostgreSQL shell type: \q

or sudo -u postgres psql or sudo -i -u postgres psql

Creating PostgreSQL Role

CREATE ROLE hakase WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'hakasepasspgsql';

check all available users on the PostgreSQL system

\du

Creating PostgreSQL Role and Database

Encoding UTF8

Collation en_US.UTF-8

Character Type en_US.UTF-8

Configuring phppgsql/phppdadmin

cd /etc/apache2/conf-available/

vim phppgadmin.conf

Change the default path URL ‘phppgadmin’ with your own path as below.

Alias /pgsqladminlogin /usr/share/phppgadmin

Now comment out the line #Require local by adding a # in front of the line and add below the line allow from all so that you can access from your browser.

Require all granted

Save and close.

Next, test the Apache configuration and make sure there is no error, then restart the Apache service.

apachectl configtest systemctl restart apache2

Next, open your web browser and type the server IP address following with your custom path of phpPgAdmin.

http://your-server-ip/pgsqladminlogin/

Testing services ss -plnt

Enable Remote Access to PostgreSQL server

sudo nano /etc/postgresql/12/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

sudo service postgresql restart

Testing again services ss -nlt | grep 5432

/etc/postgresql/12/main/pg_hba.conf

you can also edit pg_hba.conf with single line

sudo bash -c "echo host all all 0.0.0.0/0 md5 >> /etc/postgresql/12/main/pg_hba.conf"


# Database administrative login by unix domain socket
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                 peer
local   all             all                                      md5

# The user jane can access all databases from all locations using md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane can access only the janedb from all locations using md5 password
host    janedb          jane             0.0.0.0/0                md5
host    moodle          moodle_admin     0.0.0.0/0                md5
host    sis             sis_admin        0.0.0.0/0                md5

# The user jane can access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

#example from moodle docs page  https://docs.moodle.org/310/en/PostgreSQL
host        moodle      moodleuser      127.0.0.1/32        password

sudo service postgresql restart

Previous
Next