Beyond the Time

Sometimes it's on our side, specially when we are young, yet sometimes it could be against our will. But it's unbeatable, unstoppable, for poor, rich, old and young. Its perpetual existence in our lives, almost gives and idea of a living entity, isn't it? But beyond our lyrical view, it's more practical than we generally imagine.

For us, Developers, from tasks that we need to finish of our sprints, to date and time computations that provide information from applications, time plays a big role.

I was truly wondering on some aspects about the perception that we can have from time, from science to software development, and this article came out. It could be an amalgamation of multiple subtopics, but it would be unfair to discuss about time computations in Python or any programming language, without writting about other time related things that precede the invention of the digital computer.

Earth's Spins

A complete rotation of Earth on its axis, it's a day (not exactly 24hs), divided in hours, minutes, seconds, etc., and Earth revolves in orbit around the sun, resulting in a Tropical or Solar Year. Even before knowing this astronomical perspective, Mexicas, Egyptians, Greeks and many other civilizations, were counting days, being by the cycle of Sun, Moon, or even by other more simpler events, creating their own devices for timekeeping.

Counting with Precision

In fact, Earth's revolution around the Sun has extra hours and minutes, which are later on added as the 29th day of February of a Leap Year. Worth to observe that Earth's rotation speed varies in response to climatic and geological events, resulting in Leap Seconds, which are gradually added to the Coordinated Universal Times (UTC), where Atomic Clocks have an important role on adjusting the time as we know, providing precise measure of a Second, based on the transitions of the Caesium-133 atoms.

Analogical clocks are useful, yes, even though they get out of sync. Before the Atomic Clocks, they were there, with great engineering on their manufacturing, like this UTC Clock designed and created by the great Mexican engineer, Alejandro Olvera Charolet, using cogs, pendulums, relying upon the force of gravity.

UTC Clock by Alejandro Olvera Charolet at Centanario Clocks factory (Zacatlán, Puebla - Mexico)

But since when should we count the days?

Besides the Gregorian Calendar, there are hundreds of calendar systems: Iranian, Japanese, Korean (South), Juche (North Korea), Tibetan, etc. What all of them have in common is: an epoch. A starting pointing for counting the time, generally being an event, an epoch.

On the Gregorian Calendar, the birth of Jesus Christ, is the epoch where the time starts to be counted, having the notion of time before (Before Christ) and after this event (Anno Domini). The fall of the Persian Empire was in 330 B.C., and the Colosseum was built in Rome between 70 ~ 80 AD: two events, one before and other after the birth of Jesus Christ.

That being said, computers should know from which epoch the time starts to be counted too for a matter of computations related with date and time. Operating Systems have different dates as the epoch. On Unix and GNU/Linux like OSes, January 1, 1970 00:00:00 is the epoch. The engineers Dennis Ritchie and Ken Thompson between the 60's and start of the 70's, where discussing the definition of an epoch for Unix and the New Year's Day was the option, later on inherited by all flavors of Unix 'n GNU/Linux created so until now.

Unfortunately, we don't have so much time until the Year 2038 problem, at least for the legacy 32-bits architectures, for the maximum signed integer supported by 32-bits computers, will reach the timestamp 03:14:07 on Tuesday, 19 January 2038. On the next second, 32-bits computers will start counting the time at 20:45:52 on Friday, 13 December 1901.

Software Development, Time and Scenarios

It's a challenge for us too. We have to deal with it, in terms of hard and soft skills, developing software that transforms and calculates time spent, while keeping an eye on the sprint, for time is running, and so the sprint. We have also Time Complexity serving to estimate the amount of computer time an instruction may consume, therefore understanding the worst case scenario of our code, helping us to optmize our code to the most efficient instructions, having some relation with Software Profiling (subject for another post).

Some tasks that we face, involving time computations:

• data ingestion, normalizing and storing data and timestamps in databases
• processing of programs/web services log files, measuring intervals between events
• exposure of database records and their timestamps through APIs

And by the way, hardware clocks/computers must be synchronized with Time Servers connected with Atomic Clocks via a network protocol called NTP. As long as we have NTP protocol working fine (no firewall filters) and Time Servers synced with Atomic Clocks, hardware clocks will always be synced, so as the date and time of our applications, their events, everything precise, from the moment that you buy a product, to the moment that you received the package in your house.

Time computations with Strings? Nope.

String representations of time are hard to be calculated. Converting number representations to integer data types, then performing calculations, nope, that doesn't work. Fortunately, standard libraries from many programming languages have their own implementations for converting string representations of date, time or timestamps, to objects, so we can access years, days, minutes, seconds, etc., as attributes, making it way more easier to access and compute these values.

While interacting with external data sources (REST APIs, .csv files, etc.), things start to get complicated, for we may encounter a variety of date/time presentations (some not following ISO 8601 standard), and the evaluation of how the timestamps are presented is mandatory, in order to convert these representations into datetime objects, these being informed about the timezone (AWARE) or not (NAIVE), doing the required computations and storing it on a database system along with all data processed.

Human Readable Timestamps

The opposite operation. While retrieving database records and performing the necessary computations, datetime objects must be converted to human readable strings. On Python, it could be using strftime() or isoformat(), it depends on the business requirements.

Possible Scenarios While Creating/Consuming APIs

Not so common, but an API with a single endpoint, pretty much representing the flow between User <-> Webservice, serving data and timestamps that participate on relevant computations, delivering more data to the user or to the Frontend of the application (User Interface):

On the other hand, here's a process that performs a request to the API, doing an extra computation, providing an information that wasn't exposed by the API's endpoint (observe the intentational computation between AWARE and NAIVE datetime objects, for a matter of demonstration):

Final Thoughts

It's funny to think about how many things are related with time, specially for us, Developers. Even though we know how to make time computations, extracting information and insights from events, we are just touching the surface of what time really means, beyond our professions.

We may ramble on about the philosophical and technical aspects that this 'entity' constantly plays in our lives, and we may find answers, methods and tricks to make our lives easier while dealing with this titan, but it's the best that we can do so far, in order to invest our efforts on things that most matter for us, for we will not live forever, and neither our loved ones.

I heard once that, if we could extend our existences, then we could beat the time (hahaha). Just making our Telomeres more resistant to the natural shortening, so it will delay our natural end, it's not immortality. We want to live longer, but why?

As Neil DeGrasse Tyson once said:

"It is the knowledge that I am going to die that creates the focus that I bring to being alive. The urgency of accomplishment. The need to express love — now, not later. If we live forever, why even get out of bed in the morning? Because we always have tomorrow. That's not the type of life I want to lead."

Therefore, this Grim Reaper, plays an important role on how do we understand our existence.

The existence of humanity expressed through counted astronomical events, understanding for how long things existed (or still exist), or in which period of our calenders an event has happened. There's no deity or entity here: it's just us, counting events while we get old. And if that bothers you, think for a moment and realize, that you still have time :).

Understanding how fast a code is with Timeit

One may find her/himself creating scripts with initial and final instances of datetime.now(), executing a block of code in between them and then obtaining a delta between the two timestamps, in order to understand how long the code will take for its execution, or measuring performance between two or more approaches. I remember doing this in my early days of programming. It's not a bad approach: it actually works pretty well, but Python is a "batteries-included" language, with an extensive set of libraries available on its installation, like timeit, that will do the job for plenty of tasks that we come across.

Timeit does an excellent job, indeed. It executes a block of code N times and it repeats this loop R times as well, providing the best execution time while, for example, running a piece of code 100000 times, in 5 separated rounds of execution.

Running via Command Line

Here are some quick examples of how to use via command line.

Default values

Defining 10 rounds

Defining 10 rounds of 100000 executions

With this understanding, for example, we can compare the different methods of joining strings, and how efficient each one them can be:

Testing Module Functions and Comparing Approaches

Here's a more formal approach, testing a module and a couple of functions from it. I'm now comparing 3 different ways of joining strings, being it via concatenation or interpolation:

Seems that the first approach is more efficient, indeed. To my surprise, I've always thought that ''.join() would be more efficient than normal string concatenation. Worth to notice that each execution of timeit.repeat(), returns a list, that we can use as series for plotting on a chart, so we can have a more pictorial presentation of time differences between each approach.

MORE Comparisons...

Lists have different ways of being extended, incremented, etc. Let's review this too.

There isn't so much difference but, it's fair to say that extend() isn't the fastest method of extending a list. For better spotting the difference (which is pretty small in nanoseconds), let's plot these series on a chart.

Plotting

Just a couple of general settings for a Matplotlib chart, easily configured by looking at Matplotlib official documentation:



Here the results:

Indeed, incrementing is the most efficient method for extending a list.

But what about the joining of strings through concatenation or interpolation?

Here's an adaptation of the previous code, but using the string functions observed before:

And the difference in nanoseconds scale, is even bigger:

Any time difference represents a big difference, when we think about millions of executions of a instruction, even for the list extension methods. Using the most efficient method in order to achieve something, it's a practice to be maintained and it always pays off. But if the code isn't repeated so many times, it's ok to use whichever method you like, as long as the code is clean and objective (and documented, please),

Final Words

After using timeit, it's hard to think about other methods for measuring the time efficiency of blocks of code, or even small pieces of code. It's not intended to be a full profiler tool, but at least, gives you a clear perspective of how fast a approach or method can be, spotting minimal differences between similar approaches, and these small differences, will certainly represent more time consumption to your program, if the code is exposed to thousands of iterations.

A small review on Object Inheritance in Python (basics)

Over the years, developing software, I sometimes wonder how much do I know about OOP. There's so much components and applications that one builds, that are just functions or a coupĺe of classes from a regular library import. I guess I'll start some review here. Maybe this could serve to anyone, maybe not.

Chairs as Objects

Just like a chair can be assembled while reading the instruction manual, an Object can be "constructed" by a compiler, based on the definitions of a Class. You can think of a chair being an Object and the instruction manual being the Class that orients the individual on assembling it.

$ cat oop.py 
class Chair:

    def __str__(self):
        return "A chair was assembled."

chair = Chair()
print(chair)

$ python3 oop.py
A chair was assembled.

Any object or thing, has its own properties, or characteristics, just like a chair have a color.

$ cat oop.py 
class Chair:

    def __init__(self, color):
        self.color = color

    def __str__(self):
        return f"A {self.color} colored chair was assembled."

chair = Chair(color="black")
print(chair)

$ python3 oop.py
A black colored chair was assembled.

Actions, Features, Properties

Objects can have functionalities or actions, and so Chairs can have, like a lever to lower and raise the seat, or cannot. Therefore, we can assume that a regular chair is different from an office chair, which can rotate or lever the seat. That means, an office chair has a particular set of actions, that a regular chair doesn't have.

$ cat oop.py 
class Chair:

    def __init__(self, color):
        self.color = color

    def __str__(self):
        return f"A {self.color} colored chair was assembled."


class OfficeChair:

    def __init__(self, color):
        self.color = color

    def lever_down(self):
         return "Office chair seat goes down."

    def lever_up(self):
        return "Office chair seat goes up."

    def rotate_left(self):
        return "Office chair rotates to the left."

    def rotate_right(self):
        return "Office chair rotates to the right."

    def __str__(self):
        return f"A {self.color} colored chair was assembled."


chair = Chair(color="black")
print(chair)

office_chair = OfficeChair(color="red")
print(office_chair)
print(office_chair.lever_down())
print(office_chair.rotate_right())

$ python3 oop.py
A black colored chair was assembled.
A red colored chair was assembled.
Office chair seat goes down.
Office chair rotates to the right.

Code Reusability and Organization: The Power of OOP and Inheritance

Although being different types of chairs, both share a common attribute: color.

An office chair is a chair after all, so it's fair to say that an office chair is a derivation of a regular chair: it inherits characteristics and has the same purpose of regular chair.

Therefore, an inheritance can be established from Chair to OfficeChair, eliminating the need of defining a specific color attribute to OfficeChair or even defining a specific __str__ method.

For color attribute, with a little bit of polymorphism, we can redefine the implementation of __init__ method inherited from Chair class, calling __init__() from Chair via super() method, setting the color attribute of Chair, without need of setting specific color for OfficeChair.

Observation: it can also be said that OfficeChair, inherits attributes and methods from Chair.

$ cat oop.py 
class Chair:

    def __init__(self, color):
        self.color = color

    def __str__(self):
        return f"A {self.color} colored chair was assembled."


class OfficeChair(Chair):

    def __init__(self, color):
    	# __init__ was already inherited by Chair and here, it is modified.
        # Instead of setting a class attribute of color for OfficeChair, it just
        # sets the attribute of the upper class (Chair)
        super().__init__(color)

    def lever_down(self):
         return "Office chair seat goes down."

    def lever_up(self):
        return "Office chair seat goes up."

    def rotate_left(self):
        return "Office chair rotates to the left."

    def rotate_right(self):
        return "Office chair rotates to the right."

    # This method is unnecessary, for OfficeChair inherits __str__ method from Chair class.
    #
    # def __str__(self):
    #   return f"A {self.color} colored chair was assembled."


chair = Chair(color="black")
print(chair)

office_chair = OfficeChair(color="red")
print(office_chair)
print(office_chair.lever_down())
print(office_chair.rotate_right())
print("The color of the office chair is: ", office_chair.color)

$ python3 oop.py
A black colored chair was assembled.
A red colored chair was assembled.
Office chair seat goes down.
Office chair rotates to the right.
The color of the office chair is:  red

Grandfather, Father, Son: more inheritance...

Thinking on office chairs, there are ergonomic office chairs, that have a couple more of movements and adjustments. They can lean back or forward, and even have arms that go up and down. But after all, they have also the same functionalities or actions as a regular office chair.

We can now think about three types of chairs:

• regular chair (Chair)
• office chair (OfficeChair)
• ergonomic office chair (ErgonomicOfficeChair)

They are all chairs, so the inheritance easily applies from Chair to ErgonomicOfficeChair, which has its own set of actions like lean back or lean forward, but it also rotates or the seat can go up and down. In other words, an ErgonomicOfficeChair has the same functions as an OfficeChair, and through inheritance, we can use the inherited methods, instead of writting them again.

Also, from ErgonomicOfficeChair, we set the color attribute from Chair and we can use it, for the inheritance of Chair to OfficeChair, is accessible via ErgonomicOfficeChair, which inherits from OfficeChair.

$ cat oop.py 
class Chair:

    def __init__(self, color):
        self.color = color

    def __str__(self):
        return f"[INFO]: A {self.color} colored chair was assembled."


class OfficeChair(Chair):

    def __init__(self, color):
        super().__init__(color)

    def lever_down(self):
         return "Office chair seat goes down."

    def lever_up(self):
        return "Office chair seat goes up."

    def rotate_left(self):
        return "Office chair rotates to the left."

    def rotate_right(self):
        return "Office chair rotates to the right."

    # This method is unnecessary, for OfficeChair inherits __str__ method from Chair class.
    #
    # def __str__(self):
    #   return f"A {self.color} colored chair was assembled."


class ErgonomicOfficeChair(OfficeChair):

    def __init__(self, color):
        super().__init__(color)

    def lean_back(self):
        return "Office chair leans back."

    def lean_forward(self):
        return "Office chair leans forward."



chair = Chair(color="black")
print(chair)

office_chair = OfficeChair(color="red")
print(office_chair)
print(office_chair.lever_down())
print(office_chair.rotate_right())
print("The color of the office chair is: ", office_chair.color)

ergo_office_chair = ErgonomicOfficeChair(color="blue")
print(ergo_office_chair)
print(ergo_office_chair.lean_back())
print(ergo_office_chair.lean_forward())
print(ergo_office_chair.lever_down())

$ python3 oop.py
[INFO]: A black colored chair was assembled.
[INFO]: A red colored chair was assembled.
Office chair seat goes down.
Office chair rotates to the right.
The color of the office chair is:  red
[INFO]: A blue colored chair was assembled.
Office chair leans back.
Office chair leans forward.
Office chair seat goes down.

All this review deserves more writting, for a next article. For now, this pretty much gives an idea of the power of code reusability and organization that OOP provides.

Even though Python is not fully object-oriented due to lack of strong encapsulation, isn't a week language in terms of OOP. That will be something more for another article. See ya.

Postgresql and Where the Wild Things Are: databases, tables and everything

I'd generally just search some directory inside /var, which might be quite obvious, but nothing will be better than reviewing the configuration that Postgresql is considering for its internal management. We don't want to do things in a remaining folder from a previous installation, so better know where we are. Plus, it's a safe way to access configuration files, independently of which distro or OS are you using. In my case, I want to know: where are the databases and tables inside the filesystem?

These are my databases:

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 instagram | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

Postgresql run-time configuration can be inspected via SHOW command on a PSQL shell. The configuration that matters here is data_directory:

postgres=# SHOW data_directory;
      data_directory      
--------------------------
 /var/lib/postgresql/data
(1 row)

Since I'm using Postgresql in a Docker container, I'll spawn a shell inside the container in order to navigate through the filesystem:

~ $ docker ps | grep lab_postgres
24999bd8c9ef   postgres   "docker-entrypoint.s…"   2 months ago   Up 5 hours   5432/tcp   lab_postgres
~ $ docker exec -it lab_postgres bash
root@24999bd8c9ef:/# cd /var/lib/postgresql/data

root@24999bd8c9ef:/var/lib/postgresql/data# ls -l
total 124
drwx------ 6 postgres postgres  4096 Jan 29 20:23 base
drwx------ 2 postgres postgres  4096 Jan 29 20:28 global
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_commit_ts
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_dynshmem
-rw------- 1 postgres postgres  4782 Nov 28 19:21 pg_hba.conf
-rw------- 1 postgres postgres  1636 Nov 28 19:21 pg_ident.conf
drwx------ 4 postgres postgres  4096 Jan 29 20:43 pg_logical
drwx------ 4 postgres postgres  4096 Nov 28 19:21 pg_multixact
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_notify
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_replslot
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_serial
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_snapshots
drwx------ 2 postgres postgres  4096 Jan 29 20:18 pg_stat
drwx------ 2 postgres postgres  4096 Jan 30 01:26 pg_stat_tmp
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_subtrans
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_tblspc
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_twophase
-rw------- 1 postgres postgres     3 Nov 28 19:21 PG_VERSION
drwx------ 3 postgres postgres  4096 Jan 29 20:40 pg_wal
drwx------ 2 postgres postgres  4096 Nov 28 19:21 pg_xact
-rw------- 1 postgres postgres    88 Nov 28 19:21 postgresql.auto.conf
-rw------- 1 postgres postgres 28085 Nov 28 19:21 postgresql.conf
-rw------- 1 postgres postgres    36 Jan 29 20:18 postmaster.opts
-rw------- 1 postgres postgres    94 Jan 29 20:18 postmaster.pid

There are config files and directories, including a particular directory called "base":

root@24999bd8c9ef:/var/lib/postgresql/data# cd base/
root@24999bd8c9ef:/var/lib/postgresql/data/base# ls -l
total 32
drwx------ 2 postgres postgres  4096 Nov 28 19:21 1
drwx------ 2 postgres postgres  4096 Nov 28 19:21 13394
drwx------ 2 postgres postgres 12288 Jan 29 20:27 13395
drwx------ 2 postgres postgres 12288 Jan 29 20:36 16736

These directories are listed on the system catalog pg_database.

The numbers are OIDs for the available databases:

  oid  |  datname  | datdba | encoding | datcollate |  datctype  | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl                
-------+-----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
     1 | template1 |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | t            |           -1 |         13394 |          479 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13394 | template0 |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | f            |           -1 |         13394 |          479 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13395 | postgres  |     10 |        6 | en_US.utf8 | en_US.utf8 | f             | t            |           -1 |         13394 |          479 |          1 |          1663 | 
 16736 | instagram |     10 |        6 | en_US.utf8 | en_US.utf8 | f             | t            |           -1 |         13394 |          479 |          1 |          1663 | 
(4 rows)

Let's enter on the directory that corresponds to the postgres database:

root@24999bd8c9ef:/var/lib/postgresql/data/base# cd 16736/
root@24999bd8c9ef:/var/lib/postgresql/data/base/16736# ls
112	   16739_vm   16871_fsm  2607_vm   2656  2704	   3351      3601      4160
113	   16746      16871_vm	 2608	   2657  2753	   3379      3601_fsm  4161
1247	   16748      16875	 2608_fsm  2658  2753_fsm  3380      3601_vm   4162
1247_fsm   16749      16877	 2608_vm   2659  2753_vm   3381      3602      4163
1247_vm    16751      16889	 2609	   2660  2754	   3394      3602_fsm  4164
1249	   16753      16891	 2609_fsm  2661  2755	   3394_fsm  3602_vm   4165
1249_fsm   16753_fsm  16891_fsm  2609_vm   2662  2756	   3394_vm   3603      4166
1249_vm    16753_vm   16891_vm	 2610	   2663  2757	   3395      3603_fsm  4167
1255	   16761      16896	 2610_fsm  2664  2830	   3429      3603_vm   4168
1255_fsm   16768      16898	 2610_vm   2665  2831	   3430      3604      4169
1255_vm    16770      174	 2611	   2666  2832	   3431      3605      4170
1259	   16770_fsm  175	 2612	   2667  2833	   3433      3606      4171
1259_fsm   16770_vm   2187	 2612_fsm  2668  2834	   3439      3607      4172
1259_vm    16776      2224	 2612_vm   2669  2835	   3440      3608      4173
13245	   16788      2328	 2613	   2670  2836	   3455      3609      4174
13245_fsm  16790      2336	 2615	   2673  2837	   3456      3712      5002
13245_vm   16790_fsm  2337	 2615_fsm  2674  2838	   3456_fsm  3764      548
13247	   16790_vm   2579	 2615_vm   2675  2838_fsm  3456_vm   3764_fsm  549
13249	   16796      2600	 2616	   2678  2838_vm   3466      3764_vm   6102
13250	   16798      2600_fsm	 2616_fsm  2679  2839	   3467      3766      6104
13250_fsm  16815      2600_vm	 2616_vm   2680  2840	   3468      3767      6106
13250_vm   16817      2601	 2617	   2681  2840_fsm  3501      3997      6110
13252	   16817_fsm  2601_fsm	 2617_fsm  2682  2840_vm   3502      4143      6111
13254	   16817_vm   2601_vm	 2617_vm   2683  2841	   3503      4144      6112
13255	   16823      2602	 2618	   2684  2995	   3534      4145      6113
13255_fsm  16825      2602_fsm	 2618_fsm  2685  2996	   3541      4146      6117
13255_vm   16837      2602_vm	 2618_vm   2686  3079	   3541_fsm  4147      826
13257	   16839      2603	 2619	   2687  3079_fsm  3541_vm   4148      827
13259	   16839_fsm  2603_fsm	 2619_fsm  2688  3079_vm   3542      4149      828
13260	   16839_vm   2603_vm	 2619_vm   2689  3080	   3574      4150      pg_filenode.map
13260_fsm  16844      2604	 2620	   2690  3081	   3575      4151      pg_internal.init
13260_vm   16846      2605	 2620_fsm  2691  3085	   3576      4152      PG_VERSION
13262	   16858      2605_fsm	 2620_vm   2692  3118	   3596      4153
13264	   16860      2605_vm	 2650	   2693  3119	   3597      4154
1417	   16860_fsm  2606	 2651	   2696  3164	   3598      4155
1418	   16865      2606_fsm	 2652	   2699  3256	   3599      4156
16737	   16867      2606_vm	 2653	   2701  3257	   3600      4157
16739	   16869      2607	 2654	   2702  3258	   3600_fsm  4158
16739_fsm  16871      2607_fsm	 2655	   2703  3350	   3600_vm   4159

There are a lot of files and all these numbers are (again) OIDs listed in another system catalog called pg_class. The files that have just a number on its name, are the pure tables inside the postgres database (16736).

For example, from these files, which one could be the one that is the table pg_user? Let's find out:

postgres=# SELECT oid, relname FROM pg_class WHERE relname = 'pg_user';
 12098 | pg_user

Let's inspect this table:

postgres=# \d pg_user;
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     |           |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          | 

postgres=# SELECT * FROM pg_user;
 postgres|10  | t      | t      | t      | t         | ******** |          |

Indeed, is the table that holds user data from Postgres. I don't think I'm going to use this knowledge that much, to be honest. But at least, from now and on, I can have an idea of where all Postgres data is stored and how Postgres catalogs the data for its internal management. One more lesson learned :).

A reminder about SSH key pair access

I remember doing this (setting up SSH key pair access) like a ninja, without ServerFault or anything else. But these last years as Software Developer, have rusted the SysAdmin sword, let's say. I was getting crazy here, trying to understand what I was missing, and of course, it gotta be something silly. It's always something silly, and indeed it was. So, here's the first note of 2022, with high hopes on having better notes on this diary, discussing anything more interesting.

I generated my keypair...

I defined PubkeyAuthentication on the sshd daemon of the remote server...

But the access still fails...

That was really bugging me. It can't be Netfilter/IPTABLES, for the connection is established and under negotation. It can't be the user either, for my user exists on the remote server, so I don't need to pass the user while defining the remote IP address, and the PubkeyAuthentication is enabled.

But wait: did I added the pubkey of my user on the authorized_keys file of my user on the remote machine? (...)

And NOW, I can access the remote server..

Observe that the prompt has changed, now informing that a passphrase should be passed for the secret key. Without the authorized_keys defined on the remote server, the authentication was being negotiated via PasswordAuthentication option as yes, being a fallback authentication, which you should disable on your ssh server, for you don't want ssh access without recognized keys (protected by passwords, please).

Without that option, probably sshd daemon would complain about lack of authorized_keys file for my user.

May this note stay here as reminder, for who knows what the future holds...