1. Install and start PostgreSQL.
[root@www ~]# dnf module -y install postgresql:12/server
[root@www ~]# postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@www ~]# systemctl enable --now postgresql
2. Secara default, dimungkinkan untuk terhubung ke Server PostgreSQL hanya dari Localhost dengan otentikasi [peer].
# listen only localhost by default
[root@www ~]# grep listen_addresses /var/lib/pgsql/data/postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# authentication methods by default
[root@www ~]# grep -v -E "^#|^$" /var/lib/pgsql/data/pg_hba.conf
local all all peer
host all all 127.0.0.1/32 ident
host all all ::1/128 ident
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
3. Pada otentikasi [peer], diperlukan pengguna OS dan pengguna PostgreSQL yang namanya sama untuk terhubung ke Server PostgreSQL.
# add an OS user
[root@www ~]# useradd cent
# add an PostgreSQL user and his Database with PostgreSQL admin user
[root@www ~]# su - postgres
[postgres@www ~]$ createuser cent
[postgres@www ~]$ createdb testdb -O cent
# show users and databases
[postgres@www ~]$ psql -c "select usename from pg_user;"
usename
----------
postgres
cent
(2 rows)
[postgres@www ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | cent | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
PostgreSQL 12 : Remote Connection
1. Ada banyak metode otentikasi di PostgreSQL. Pada contoh ini, Konfigurasikan metode sandi MD5.
[root@www ~]# vi /var/lib/pgsql/data/postgresql.conf
# line 59: uncomment and change
listen_addresses = '*'
[root@www ~]# vi /var/lib/pgsql/data/pg_hba.conf
# add to the end
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
# specify network range you allow to connect on [ADDRESS] section
# if allow all, specify [0.0.0.0/0]
host all all 10.0.0.0/24 md5
[root@www ~]# systemctl restart postgresql
2. Jika Firewalld sedang berjalan, izinkan layanan PostgreSQL.
[root@www ~]# firewall-cmd --add-service=postgresql --permanent
success
[root@www ~]# firewall-cmd --reload
success
3. Untuk terhubung ke Database PostgreSQL dengan kata sandi MD5, setel kata sandi untuk setiap pengguna PostgreSQL.
# connect to own database
[cent@www ~]$ psql -d testdb
psql (12.1)
Type "help" for help.
# set or change own password
testdb=> \password
Enter new password:
Enter it again:
testdb=> \q
# also possible to set or change password for any users with PostgreSQL admin user
[postgres@www ~]$ psql -c "alter user cent with password 'password';"
ALTER ROLE
4. Verifikasi pengaturan untuk terhubung ke Database PostgreSQL dengan kata sandi MD5 dari host jarak jauh.
[root@node01 ~]# psql -h www.srv.world -d testdb -U cent
Password for user cent: # password
psql (12.1)
Type "help" for help.
testdb=> # connected
PROBLEM:
Sewaktu ingin membuat extention CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
muncul error seperti dibawah ini:
ERROR: could not open extension control file "/usr/share/pgsql/extension/uuid-ossp.control": No such file or directory
SOLUTION:
Anda mungkin juga ingin menginstal paket contrib yang menyediakan beberapa fitur tambahan untuk sistem database PostgreSQL.
$ sudo dnf install postgresql-contrib
PROBLEM:
Tidak dapat membuat extention CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; karena bukan superuser
SOLUTION:
[root@www ~]# su - postgres
[postgres@www ~]$ ALTER USER namauser WITH SUPERUSER;
