Sunday, June 10, 2018

Cara Setting Reverse SSH Tunnel pada Linux

Assalamu'alaikum Wr. Wb.

Tutorial ini dibuat hanya untuk sharing pengalaman saja. Kejadiannya ketika saya pertama kali masuk ke Startup Company di daerah Depok City. Ketika itu dapat tugas untuk membuat webservice (inter-koneksi antara android dan database) menggunakan codeigniter & mysql/mariadb.

Issue-nya : Harus bisa membuat koneksi yg aman menggunakan ssh, antara webservice dan server database menggunakan linux.

Solusi yg saya tawarkan salah satunya adalah :
1. Buat koneksi ssh sebelum menjalankan query ke database melalui php extension yaitu php_ssh2 library.
2. Buat koneksi ssh dari webservice ke server database menggunakan autossh.
3. Buat koneksi reverse ssh dari server database ke webservice menggunakan autossh.
4. Buat database replication slave pada webservice, tapi tetap menggunakan jalur ssh.

Masalah lain yaitu ketika server webservice masih menggunakan shared hosting, yang mana akses untuk solusi no 1 & 2 terbatas, tetapi tidak menghalangi saya untuk mencari jalan keluarnya. Karena solusi no.4 adalah solusi terakhir saya, jadi saya tidak akan fokus kesana.

Yang mau saya bahas adalah solusi no.3 yaitu membuat koneksi ssh reverse dari server database ke server webservice menggunakan autossh.

Linux :
$ cat /proc/version
Linux version 3.10.0-327.28.2.el7.x86_64 (builder@kbuilder.dev.centos.org) (gcc version 4.8.3 20140911 (Red Hat 4.8.3-9) (GCC) ) #1 SMP Wed Aug 3 11:11:39 UTC 2016

Langkah ini dilakukan pada server database :
-  Install autossh pada Linux Box. Kebetulan saya pakenya CentOS/Redhat.
# rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/Packages/a/autossh-1.4c-2.el6.x86_64.rpm

-  Buat user untuk autossh.
# useradd -m -s /sbin/nologin autossh

-  Buat SSH Key untuk user autossh.
# su - autossh -s /bin/bash
# ssh-keygen -t rsa

note: passwordnya dikosongin aja biar bisa autologin dengan autossh nantinya.

-  Copykan ssh key-nya ke server webservice
# ssh-copy-id user@server-webservice.com

note: user isikan user yg didapat dari shared hosting berikut passwordnya nanti.

-  Testing
# su - autossh -s /bin/bash
$ autossh -M 0 -N -R 3333:localhost:3306 user@server-webservice.com -i /home/autossh/.ssh/id_rsa

note: sekarang harusnya kita bisa melakukan koneksi ke database mysql pada server webservice. Dengan koneksi host: localhost & port: 3333

-  Selanjutnya kita akan seting auto run pada saat booting (server database).
# cat > /etc/systemd/system/autossh-webservice.service << EOF
[Unit]
Description=Keep a reverse tunnel to 'webservice server' open
After=network.target

[Service]
User=autossh
ExecStart=/usr/bin/autossh -M 20000 -N -R 3333:127.0.0.1:3306 user@server-webservice -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -i /home/autossh/.ssh/id_rsa

[Install]
WantedBy=multi-user.target
EOF

-  Jalankan service-nya
# systemctl daemon-reload
# systemctl start autossh-webservice.service

-  Aktifkan auto run nya
# systemctl enable autossh-webservice.service

Perluasan penggunaan SSH Tunnel :
Bisa digunakan untuk jalur vpn, remote akses (seperti teamviewer & radmin, dll)

Pernyataan :
Saya sama sekali tidak mendukung menggunakan ini untuk apa pun tindakan kejahatan, seperti mendapatkan akses VPN atau menerobos firewall perusahaan dan lain sebagainya. Tutorial ini hanyalah panduan bagaimana menggunakan secure, reverse tunneling dimana konektivitas biasanya tidak dimungkinkan.

Wassalam.

Wednesday, October 11, 2017

Managing Hierarchical Data In Postgresql

Berikut ane kumpulin rangkuman/artikel/tanya-jawab seputar cara me-manage data hirarkis pada postgresql.

Menggunakan query standard:

This may have been asked/answered a million times already, but what the
heck...

Basically, I have a table that maintains parent<-->child relationships
within itself.  The parent_id field points to the collection_id field.
A parent_id id of -1 means it's a root record (ie, no parent).  Pretty
simple.

Question is, how do I sort a query so that children follow their parent?

I came up with this, and it works, but I'm sure there's a better way:

SELECT *, CASE WHEN parent_id = -1 THEN collection_id||'' WHEN parent_id
!= -1 THEN parent_id||collection_id END as z FROM collection order by z;

Any advice will be greatly appreciated.

thanks!

eric

Source 

Some self join would work best I suppose:

select p.*, c.*
from collection p, collection.c
where c.parent_id = p.collectionid
order by p.collectionid ASC, c.collectionid

Depending on your datset you might need to use an outer join instead.

Jochem

Source

Menggunakan ltree:

The ltree extension

The ltree extension is a great choice for querying hierarchical data. This is especially true for self-referential relationships.
Lets rebuild the above example using ltree. We'll use the page's primary keys as the "labels" within our ltree paths and a special "root" label to denote the top of the tree.
CREATE EXTENSION ltree;

CREATE TABLE section (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_path LTREE
);

CREATE INDEX section_parent_path_idx ON section USING GIST (parent_path);
We'll add in our data again, this time rather than using the id for the parent, we will construct an ltree path that represents the parent node.
INSERT INTO section (id, name, parent_path) VALUES (1, 'Section 1', 'root');
INSERT INTO section (id, name, parent_path) VALUES (2, 'Section 1.1', 'root.1');
INSERT INTO section (id, name, parent_path) VALUES (3, 'Section 2', 'root');
INSERT INTO section (id, name, parent_path) VALUES (4, 'Section 2.1', 'root.3');
INSERT INTO section (id, name, parent_path) VALUES (4, 'Section 2.2', 'root.3');
INSERT INTO section (id, name, parent_path) VALUES (5, 'Section 2.2.1', 'root.3.4');
Cool. So now we can make use of ltree's operators @> and <@ to answer our original question like:
SELECT * FROM section WHERE parent_path <@ 'root.3';
However we have introduced a few issues.
  • Our simple parent_id version ensured referential consistancy by making use of the REFERENCES constraint. We lost that by switching to ltree paths.
  • Ensuring that the ltree paths are valid can be a bit of a pain, and if paths become stale for some reason your queries may return unexpected results or you may "orphan" nodes.
Source
Reference


Menggunakan Array untuk memetakan jalur. Seperti yang dijelaskan pada artikel dibawah ini: Source

Modeling Hierarchies

There are a number of ways you can represent a tree in a relational database. The most obvious approach is for each node to have a reference to its parent. For instance if we might model a giant eight person company like this:
id | name
---+------------
1  | Harold
2  |   Arthur
3  |     Alice
4  |       Rose
5  |     Bob
6  |   Sally
7  |     Mortimer
8  |     Penny
One common approach is to simply store the id of each person's boss. Although this works for simple cases, it requires multiple queries to get all the parents or children of any node. For instance to find all the people who work under Arthur, you need at least one query per level beneath him.
An alternative is to encode the hierarchy as a materialized path. A materialized path is an array that contains all the ids of the record's parents.
CREATE TABLE employees (
    id        integer primary key,
    path      integer[],
    name      text
);
For example, Alice would have a materialized path of [1,2,3] since the people above her are Harold (id:1), Arthur (id:2), and Alice herself is (id:3).
In these examples, we also include the current record's id in its path, this is optional, but is often helpful if you want to get a record with its parents or children. If you do not encode the current record's id in its path, you may need to cast your empty arrays with ARRAY[]::integer[].

Depth



The depth of a record indicates how deeply nested it is. With a materialized path, this is just the length of the array. Postgres provides array_length(array, dim), which will return the length of an array in dimension dim. Since we are using one dimensional arrays, dim will always be 1.
We can now find all the people in the top two levels of our massive eight person company:
SELECT id, name, path, array_length(path,1) as depth
FROM employees 
WHERE array_length(path,1) <= 2
Doing this will give us back just 3 employees:
 id |  name    | path  | depth 
----+----------+-------+-------
  1 | Harold   | {1}   |     1
  2 |   Arthur | {1,2} |     2
  6 |   Sally  | {1,6} |     2

Children

Finding a record's children is easy with a materialized path. We can apply what we learned about the overlap operator from Tagging in Postgres and ActiveRecord to this problem. Since each record contains the ids of all the parent records, we just look for all the records containing the id we are interested in.


For example, if we want to find all the people who work under Arthur (id:2), we would look for all the paths that overlap him (path && ARRAY[2]):
SELECT id, name, path FROM employees
WHERE path && ARRAY[2]
As you can see, each of the employees returned have Arthur's id in their materialized path:
 id |  name      |   path    
----+------------+-----------
  2 | Arthur     | {1,2}
  3 |   Alice    | {1,2,3}
  4 |     Rose   | {1,2,3,4}
  5 |   Bob      | {1,2,5}

Parents

Finding the parents of a record is just as easy as finding their children. Instead of looking for a path that overlaps a given id, we can look for any id that overlaps a given path.
So if we wanted to find all of Alice's superiors, we would look for all the records whose idoverlaps her path ([1,2,3]).
SELECT id, name, path FROM employees
WHERE ARRAY[id] && ARRAY[1,2,3]
This will give you the three people listed in Alice's path:
 id |  name      |  path   
----+------------+---------
  1 | Harold     | {1}
  2 |   Arthur   | {1,2}
  3 |     Alice  | {1,2,3}
Alternatively, since you already know the ids you are looking for you could also query for those records directly:
SELECT id, name, path
FROM employees 
WHERE id in (1,2,3)
Although both approaches yield the same results, Postgres will use different indices, so if this is a performance critical query, benchmark both approaches in your application.

Moving Records

Up to this point, we have focused on querying a hierarchy. Updating a hierarchy can be a little tricky since child records need to be updated as well.
Let's imagine that Alice and everyone working for her are moved under Mortimer (path: [1,6,7]):
id | name         | path
---+--------------+----------
1  | Harold       | {1}
2  |   Arthur     | {1,2}
5  |     Bob      | {1,2,5}
6  |   Sally      | {1,6}
7  |     Mortimer | {1,6,7}
3  |       Alice  | {1,6,7,3}
4  |         Rose | {1,6,7,3,4}
8  |     Penny    | {1,6,8}
Alice's and her subordinates's path will now need to start with Mortimer's path instead of Arthur's. To perform this operation, we will slice off Arthur's path, and replace it with Mortimer's path:
UPDATE employees
SET path = ARRAY[1,6,7] || path[3:array_length(path,1)]
WHERE path && ARRAY[3]
There are a few things going on here, so let's work through this from the bottom up.


We limit the update to the employees who work under Alice by selecting her, and her child records. To slice off Arthur's path, we get the path from Alice's depth (3) to the end of the path. Now we can tack on Mortimer's path ([1,6,7]).
Now you can move people around your organization.

Menggunakan CTE (Common Table Expressions) untuk membentuk tree struktur: Source

USING RECURSIVE COMMON TABLE EXPRESSIONS TO REPRESENT TREE STRUCTURES

Printer Friendly


Oke cukup sekian, nanti kalo nemu trik lagi akan ane update rangkuman ini. Thanks for the authors.


Sunday, April 27, 2014

Codeigniter: Active Record, join with no condition (fix bug)

Fix untuk "DB_active_rec.php".
1. Edit function 
public function join($table, $cond, $type = '')
rubah menjadi,
public function join($table, $cond = '', $type = '')
 2. Masih di dalam function join, cari
// Assemble the JOIN statement 
$join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; 
rubah menjadi,
// Assemble the JOIN statement
if ($cond != '')
$join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond.' ';
else
$join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ';
3. Done

Saturday, September 7, 2013

Codeigniter: Benchmarking record count.

 Menggunakan database SQL Server 2012, dengan jumlah record diatas 1juta. Juga ditambah dengan menggunakan MemoryCache untuk process yang nomor 4.
function get_test() {
$this->benchmark->mark('one');
$this->db->select('*')->from('big_data')->order_by('id', 'asc');
$num_row1 = $this->db->get()->num_rows();
$this->benchmark->mark('two');
$this->benchmark->mark('three');
$this->db->flush_cache();
$this->db->select('COUNT(*) AS rec_count', FALSE)->from('big_data');
$num_row2 = $this->db->get()->row()->rec_count;
$this->benchmark->mark('four');
$this->benchmark->mark('five');
$this->db->flush_cache();
$num_row3 = $this->db->query("select count(*) as rec_count from big_data")->row()->rec_count;
$this->benchmark->mark('six');
$this->benchmark->mark('seven');
$num_row4 = $this->cache->memcached->get('num_row4');
if ( !$num_row4 ) {
$this->db->flush_cache();
$this->db->select('COUNT(*) AS rec_count', FALSE)->from('big_data');
$num_row4 = $this->db->get()->row()->rec_count;
$this->cache->memcached->save('num_row4', $num_row4, 60);
}
$this->benchmark->mark('eight');
$this->benchmark->mark('nine');
$this->db->flush_cache();
$num_row5 = $this->db->get('big_data')->num_rows;
$this->benchmark->mark('ten');
echo "PROCESS #1 : ".$this->benchmark->elapsed_time('one', 'two')."<br />";
echo "REC. COUNT #1 : $num_row1"."<br />";
echo "PROCESS #2 : ".$this->benchmark->elapsed_time('three', 'four')."<br />";
echo "REC. COUNT #2 : $num_row2"."<br />";
echo "PROCESS #3 : ".$this->benchmark->elapsed_time('five', 'six')."<br />";
echo "REC. COUNT #3 : $num_row3"."<br />";
echo "PROCESS #4 : ".$this->benchmark->elapsed_time('seven', 'eight')."<br />";
echo "REC. COUNT #4 : $num_row4"."<br />";
echo "PROCESS #5 : ".$this->benchmark->elapsed_time('nine', 'ten')."<br />";
echo "REC. COUNT #5 : $num_row5"."<br />"; }
RESULT #1
====================
PROCESS #1 : 8.0914
REC. COUNT #1 : 1077960
PROCESS #2 : 0.2156
REC. COUNT #2 : 1077960
PROCESS #3 : 0.1757
REC. COUNT #3 : 1077960
PROCESS #4 : 0.1782
REC. COUNT #4 : 1077960
PROCESS #5 : 7.8618
REC. COUNT #5 : 1077960

RESULT #2
====================
PROCESS #1 : 8.1435
REC. COUNT #1 : 1077960
PROCESS #2 : 0.2062
REC. COUNT #2 : 1077960
PROCESS #3 : 0.1807
REC. COUNT #3 : 1077960
PROCESS #4 : 0.0018
REC. COUNT #4 : 1077960
PROCESS #5 : 8.1332
REC. COUNT #5 : 1077960

Result #1, data di memorycache belum tersimpan (PROCESS #4 : 0.1782).
Result #2, data di memorycache sudah tersimpan (PROCESS #4 : 0.0018).

WOW.....!!!

Codeigniter: Active Record, or_like and brackets

Fix untuk "DB_active_rec.php".

Edit function "protected function _like", tambahkan yang saya beri color merah.
if ( ! is_array($field)) 
{ 
$field = array($field => $match); 
} 
$prefix = '';
Kemudian, masih di function yang sama dengan yang diatas, pindahkan yang saya beri color biru ke bagian bawah, masih didalam foreach. Lihat line yang saya beri color merah.


$prefix = (count($this->ar_like) == 0) ? '' : $type;
$v = $this->escape_like_str($v); 
if ($side == 'none') 
{ 
  $like_statement = $prefix." $k $not LIKE '{$v}'"; 
}

BECOME
$this->ar_like[] = $like_statement; 
if ($this->ar_caching === TRUE) 
{   
  $this->ar_cache_like[] = $like_statement; 
  $this->ar_cache_exists[] = 'like'; 
} 
$prefix = (count($this->ar_like) == 0) ? '' : $type;

Edit file "/system/database/DB_active_rec.php", tambah function dibawah ini :
var $ar_bracket_open = FALSE; 
var $last_bracket_type = 'where'; 
function bracket($type = NULL,$append='where') 
{

        if ( strtolower($type) == 'open' )
        {
            // fetch the key of the last entry added
            $key = key($this->ar_where);
            $this->ar_bracket_open = TRUE;

            // add a bracket close
            $this->ar_where[$key] = '('.$this->ar_where[$key];
        }
        elseif ( strtolower($type) == 'close' )
        {
            // fetch the key of the last entry added
            if ($append == 'like')    {
                end($this->ar_like);
                $key = key($this->ar_like);

                // add a bracket close
                   $this->ar_like[$key] .= ')';

                // update the AR cache clauses as well
                if ($this->ar_caching === TRUE)
                {
                    $this->ar_cache_like[$key] = $this->ar_like[$key];
                }
            } else {
                end($this->ar_where);
                $key = key($this->ar_where);

                // add a bracket close
                   $this->ar_where[$key] .= ')';

                // update the AR cache clauses as well
                if ($this->ar_caching === TRUE)
                {
                    $this->ar_cache_where[$key] = $this->ar_where[$key];
                }
            }
        }
        return $this;
 Dan cara menggunakannya seperti ini…
//bracket started
$this->db->bracket('open','like'); //bracket closed  
$this->db->or_like(array('field1'=>$filter1, 'field2'=>$filter2)); 
$this->db->bracket('close','like'); //bracket closed