Sharing Printer dalam jaringan secara default akan bisa diakses oleh semua komputer dalam satu jaringan, namun bagaiaman jika kita menghendaki Share Printer hanya bisa terkoneksi dengan IP Address tertentu dan tidak semua IP dalam jaringan tersebut, untuk itulah disini kita akan membahas Filter Sharing Printer menggunakan Windows Firewall untuk keperluan tersebut.
Sebelum setting untuk filter Sharing Printer menggunakan Windows Firewall, tentunya kawan harus mengerti terlebih dahulu bagaimana Sharing Printer dengan Windows pada suatu jaringan.Kita ibaratkan IP komputer yang dikonfigurasi sebagai Server Printer adalah 192.168.114.100, dan kita hanya akan memberikan hak akses hanya kepada IP Address 192.168.114.201 dan 192.168.114.202 dalam jaringan.
Pada komputer Server Printer, klik Start >> Control Panel >> Windows Firewall. Kemudian akan tampil menu Windows Firewall, klik Tab Exceptions, oh ya sebelumnya pastikan posisi Firewall adalah ON pada Tab General, lihat gambar dibawah:
Pada gambar diatas checklist File and Printer Sharing kemudian klik Edit dan kita akan masuk pada menu Edit Service, perhatikan gambar dibawah:
Dalam menu tersebut diatas checklist TCP 139 (share printer menggunakan port ini untuk mennjalankan servicenya) dan klik Change scope seperti tanda panah diatas, setelah itu akan muncul kembali pop-up menu windows seperti pada gambar dibawah:
Pada menu tersebut diatas masukkan IP Address yang akan kita berikan hak akses untuk pemakaian Share Printer, terlihat pada gambar diatas konfigurasi dengan sintaks: 192.168.114.201,192.168.114.202/255.255.255.255
Setelah klik OK, silahkan kawan coba untuk mengakses Sharing Printer dari IP Address yang di berikan hak akses, kemudian cobalah akses menggunakan IP selain IP Address tersebut, sampai disini kawan telah berhasil membuat Filter untuk Sharing Printer menggunakan Windows Firewall.
Selamat mencoba, tinggalkanlah komentar jika menghadapi kendala dan terima kasih.
# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Update the package lists:
sudo apt update
# Install the latest version of PostgreSQL:
# If you want a specific version, use 'postgresql-16' or similar instead of 'postgresql'
sudo apt -y install postgresql
# List all databases
\l
# List all schemas
\dn
# Use schema
set search_path to training_schema;
# List all table
\dt
# Describe course table
\d participants
# Describe the course table including description
\d+ participants
# List all tablespaces
\db
# Execute SQL Statement
select * from participants;
# Execute SQL Statement, to save output to a file
\o participants_data.txt
SELECT * FROM participants;
\o
# history
\s
# show current connection
\conninfo
# show port
show port;
# show data directory
show data_directory;
Database Clusters
Login postgres user
su postgres
Create a Database Cluster
initdb -D training
Change port listen on Database Clusters Config
vim training/postgresql.conf
postgresql.conf
port = 5433
Starting a Database Cluster
# start database cluster
pg_ctl -D training/ -l /var/log/postgresql/training.log start
# check status database cluster
pg_ctl -D training/ status
Connecting To a Database Cluster
# access database cluster
psql -p 5433
# show current connection
\conninfo
# show port
show port;
# show data directory
show data_directory;
# stop database cluster
pg_ctl -D training/ stop
# check status database cluster
pg_ctl -D training/ status
View Cluster Control Information
pg_controldata training/
Configuration
View and Change Server Parameters
show all;
show port;
show work_mem;
show data_directory;
show config_file;
set work_mem = 8096;
alter system set work_mem=8192;
select pg_reload_conf();
Connection Settings
vim /etc/postgresql/16/main/postgresql.conf
postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 200
log_directory = 'log'
log_min_duration_statement = 5000
log_connections = on
Creating and Managing Databases
Databases
Check database
\l
Create database
create database training;
Alter database
ALTER DATABASE training
RENAME TO training_db;
Copy database
create database training_db_new with template training_db;
Using database
\c training_db;
Drop database
drop database training_db_new;
Users and Roles
Create role
create role admin login password 'password';
Alter role
alter role admin superuser;
Check role
\du
Change owner database
alter database training_db owner to admin
Connecting database use new user
psql -h localhost -U admin -p 5432 -d training_db
Tablespace
Check tablespace
\db
Create directory for tablespace
mkdir /data
chown postgres:postgres /data
Connecting database use new user
psql -h localhost -U admin -p 5432 -d training_db
Create tablespace
create tablespace data location '/data';
Using tablespace
create database training_data tablespace data;
Drop tablespace
drop tablespace data;
Databases Schema
Create schema
CREATE SCHEMA training_schema;
Check schema
\dn
Check current schema
SELECT current_schema();
Show search path
SHOW search_path;
Add new schema to search path
set search_path to training_schema;
Table
Create table participants
CREATE TABLE training_schema.participants (
id_participant SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15)
);
Create table instructors
CREATE TABLE training_schema.instructors (
id_instructor SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
specialization VARCHAR(100)
);
INSERT INTO training_schema.courses (course_name, description, start_date, end_date, id_instructor) VALUES
('Data Science 101', 'Introduction to Data Science', '2024-07-01', '2024-07-15', 1),
('Machine Learning Basics', 'Fundamentals of Machine Learning', '2024-08-01', '2024-08-10', 2);
Insert data into tables materials
INSERT INTO training_schema.materials (material_name, description, id_course) VALUES
('Introduction Slides', 'Slides for introduction', 1),
('Machine Learning Overview', 'Overview of Machine Learning', 2);
Insert data into tables registrations
INSERT INTO training_schema.registrations (id_participant, id_course) VALUES
(1, 1),
(2, 2);
Query data from tables participants
SELECT * FROM training_schema.participants;
Query data from tables instructors
SELECT * FROM training_schema.instructors;
Query data from tables courses
SELECT * FROM training_schema.courses;
Query data from tables materials
SELECT * FROM training_schema.materials;
Query data from tables registrations
SELECT * FROM training_schema.registrations;
Data
Update data on tables
UPDATE training_schema.participants
SET phone = '1122334455'
WHERE name = 'Budi';
UPDATE training_schema.instructors
SET specialization = 'Deep Learning'
WHERE name = 'Jhon Doe';
Delete data
DELETE FROM training_schema.registrations
WHERE id_participant = 2 AND id_course = 2;
Create View
CREATE VIEW training_schema.participant_courses AS
SELECT
p.name AS participant_name,
c.course_name,
r.registration_date
FROM
training_schema.registrations r
JOIN training_schema.participants p ON r.id_participant = p.id_participant
JOIN training_schema.courses c ON r.id_course = c.id_course;
Select view
SELECT * FROM training_schema.participant_courses;
Database Security
Host Based Access Control
show hba file and config
show hba_file;
select rule_number,type,database,user_name,address,netmask,auth_method;
config file hba to allow user admin remote access
vim /etc/postgresql/16/main/pg_hba.conf
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host training_db admin 0.0.0.0/0 md5
create user andi with password 'andi123';
create user budi with password 'budi123';
grant privileges to user
grant connect on database postgres to andi, budi;
grant usage on schema training_schema to andi, budi;
grant select on all tables in schema training_schema to andi, budi;
CREATE TABLE test_table (id SERIAL PRIMARY KEY, data TEXT);
Insert data into table
INSERT INTO test_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 1000);
execution query
SELECT * FROM test_table WHERE id = 1;
SELECT COUNT(*) FROM test_table;
UPDATE test_table SET data = 'updated' WHERE id = 1;
DELETE FROM test_table WHERE id = 2;
execution query more complex
SELECT * FROM test_table WHERE data LIKE 'a%';
SELECT id, COUNT(*) FROM test_table GROUP BY id;
analytics statistic query use pg_stat_statements
SELECT * FROM pg_stat_statements;
show most frequent running queries
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;
show most time consuming queries
SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
Dashboard postgresql with pgAdmin
Setup the repository
# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Install pgAdmin
sudo apt install pgadmin4
Setup webserver
sudo /usr/pgadmin4/bin/setup-web.sh
Access from browser
http://172.23.x.x/pgadmin4
Maintenance
Vacuum
Create database test_vacuum
CREATE DATABASE test_vacuum;
\c test_vacuum
Create table
CREATE TABLE data_table (id SERIAL PRIMARY KEY, data TEXT);
Insert data into table 10.000.000 line
INSERT INTO data_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 10000000);
Show statistic table
SELECT relname AS table_name,
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'data_table';
Shows the status of subscription when using logical replication
pg_stat_subscription
Show status replication on replica
select * from pg_stat_wal_receiver;
Add data
# insert data on table
insert into tb_data_1 values(generate_series(11,20),'data'||generate_series(11,20));
# show data on table
select * from tb_data_1;
Loadbalancing
Install pgpool-ll
# Create the file repository configuration
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists
sudo apt-get update
# Install the latest version of Pgpool-II. Specify the specific version of PostgreSQL which you are using, e.g. postgresql-16-pgpool2
sudo apt-get -y install pgpool2 libpgpool2 postgresql-16-pgpool2
Create user to monitor health check
CREATE ROLE pgpool WITH LOGIN password 'password';
GRANT pg_monitor TO pgpool;
insert into tb_data_1 values(generate_series(21,30),'data'||generate_series(21,30));
update tb_data_1 set name = 'test2' where id = 11;
delete from tb_data_1 where id < 20;
select * from tb_data_1
