Skip to main content

2 posts tagged with "postgres"

View All Tags

· 3 min read
Poonai

image of an elephant

As a university student, I was more familiar with NodeJS andmongoose than SQL, so I went with MongoDB, instead of SQL. When I started working in the real world, I became familiar with postgres. Postgres has always been my go-to database for my projects since then ❤️.

here is my mental model, why I choose Postgres for all my projects?

  • tested and proven
  • third party extension
  • JSON for other needs
  • tooling around around postgres
  • community

tested and proven

For more than two decades, Postgres has been developed and is now being used by many large corporations. It's a basic human instinct to stick with tried-and-true methods. Take a look at the screenshot below to see how an instagram engineer flexing his postgres usage.

postgres at instagram

extension ecosystem

Postgres allows developers to extends it's capabilites by writing an extensions, since some unique usecase can't be solved by general purpose database.

I used pg_cron extenstion to solve my unique usecase myself

I wanted to do historic aggregation of a numeric column. Usual scenario would be building a ETL pipeline, but I found a solution using pg_cron. You can check this link to know the entire story.

Fellow OSS engineers have opensourced their extensions for the community to use. Here are some of my favourite extensions:

  • zomboDB integrates elastic search with Postgres for full text search.
  • pg_cron cron jobs inside postgres
  • pg_storm accelerate analytics query performance by offloading analytics computation to GPU

you can always write your own extension, if you don't find extenstion for your usecase. Now you can write extension in rust as well using pgx

JSON for other needs

Usual question that comes while choosing Postgres is that, can we store complex relationship?. But unknow fact to most of the developers is that, postgres let developers to store and query JSON data.

postgres json tweet

Tooling around Postgres

Having a good database alone won't solve the problem, there are other scenarios that we need to consider. For eg: backup, runnnig an HA setup. Postgres have all sort of tooling to run a production database.

  • patroni - running a HA postgres on k8s
  • kubesdb - running postgres on k8s
  • dexter - automatic indexer to optimize db query performance
  • timescale - turn your postgres into timeseries database
  • supabase - instant graphql api from postgres databases

Community

postgres community is very welcoming and have precense in all the popular social communities:

Ofc, you can join our community as well to talk about postgres :P

Not only does the community have a presence on various social media platforms, but it is also friendly and helps you instantly if you come across any issue.

Closing Notes

Postgres isn't just a database; it's an entire ecosystem of development, research, and innovation that's impossible to fathom. I want to end the essay by saying

Postgres doing its justification for its elephant mascot

· 3 min read
Poonai

Introduction

TLS is the trusted way of sending messages over a TCP connection. TLS by default encrypts the payload before sending and decrypts after receiving the payload. But if you send plain text on a normal connection, then it can be easily spoofed.

So, if we send a password as plain text in the normal tcp connection, then the attacker can view the password and use the same password to take control of the resource.

This raises us the question that, how do we authenticate the user on a in-secure connection without revealing the password.

SASL comes to rescue

This has been solved using SASL (Simple Authentication And Security Layer). If you come from a devops background, you might have noticed SASL error. SASL is used in popular projects like Postgres, MongoDB, Kafka...

I got to know about SASL, while creating postgres support in inspektor.

In this blog, I'll explain how SCRAM(Salted Challenge Response Authentication Mechanism) works, which is part of SASL family.

Working of SCRAM

SCRAM establishes an authenticated connection through a four-step handshake:

Step 1:

Cliend sends nonce (nonce is nothing but randomly chosen bytes) and user's username to the server to initiate the handshake. This message is called client-first message.

client first message. 
n,,n=user,r=fyko+d2lbbFgONRv9qkxdawL

Step 2:

Server after receiving client-first message, it replies back with its own nonce, salt and iteration count. This message is called the server-first message.

server first message
r=fyko+d2lbbFgONRv9qkxdawL3rfcNHYJY1ZVvWVs7j,s=QSXCR+Q6sek8bf92,
i=4096

Step 3:

Now, client will create ClientProof using the parameter from the server-first message to prove that client has right password to authenticate. After creating the ClientProof. Client will send the proof to the server. It's called client-final message.

If you are curious about how the proof has been calculated, you can refer the section 3 of SASL RFC (https://datatracker.ietf.org/doc/html/rfc5802#section-3)

client final message
c=biws,r=fyko+d2lbbFgONRv9qkxdawL3rfcNHYJY1ZVvWVs7j,
p=v0X8v3Bz2T0CJGbJQyF0X+HI4Ts=

Step 4:

As a final step, server with verify the ClientProof that the client has access to the password. After that proof verification completed by the server. server will send ServerSignature to the client

server final message 
v=rmF9pqV8S7suAoZWja4dJRkFsKQ=

The ServerSignature is used to compare against the ServerSignature calculated by the client. This ensures that the client is talking to the correct server.

Now the client has established an authenticated connection without exchanging the password with the server.

Conclusion

SASL is not an alternative to TLS, but it can be used along with TLS to harden the authentication process.