search

rueian / pgbroker

A golang library for building PostgreSQL proxy
MIT License
31 stars 9 forks source link

Improvement of documentation in pkg.go.dev #2

Open kishaningithub opened 2 years ago

kishaningithub commented 2 years ago

First of all great job on handling the complexities of postgres wire protocol and creating this ! I feel it would be great examples and context can be added to the documentation in documentation here https://pkg.go.dev/github.com/rueian/pgbroker@v0.0.16/backend

Additional context

I am trying to build this https://github.com/kishaningithub/rdapp for talking in postgres wire protocol i tried to use https://github.com/jackc/pgproto3 but fell into lot of traps within the protocol which i am trying to find a way out

rueian commented 2 years ago

Hi @kishaningithub, thank you for reaching out to me.

In order to help you, I would like to know more about what kind of traps you have fallen into and what kind of examples you would like to see in the pgbroker documentation.

kishaningithub commented 2 years ago

Well the root of the problem is i don't fully understand it yet and i am looking for an higher level abstraction for a proxy server which talks in the protocol to the clients and gives out the SQL statements to the consumer

kishaningithub commented 2 years ago

I assume this library satisfies that but the problem i see is when I look into the methods exposed i still see the postgres protocol (Low level API)

It would be great to have a High level API which would enable things to be more pluggable. In my mind it should be like

Postgres protocol -> Pg broker -> Queries as string

Also the reverse

Query Result set -> Pg broker -> Post gres protocol

rueian commented 2 years ago

Hi @kishaningithub,

Actually, pgbroker does not satisfy your requirement, because it assumes your backend also talks the wire protocol and just proxies messages, mutated optionally, for you.

I checked https://github.com/kishaningithub/rdapp. I think you are pretty close to what you want to do without a higher-level abstraction.

kishaningithub commented 2 years ago

Just stumbled upon this one. What is your opinion on the same?

https://github.com/jeroenrinzema/psql-wire

rueian commented 2 years ago

Hi @kishaningithub,

Thank you for let me know the psql-wire. It indeed is much like what you need: a library for building a psql server, not just a proxy.

A server needs to do more works than a proxy. For example to support the extended query, the server needs to remember the Parse results and merge parameters from the Bind. pgbroker does not need to deal with this because it just proxies these messages to the backend.

Just for my curiosity, why you choose build a psql server over the Redshift data API instead of just exposing the psql interface of Redshift?

kishaningithub commented 2 years ago

Good question ☺️.. AWS Redshift is actually forked from postgres 8. So if it is reachable via network we can very well use psql. But there is a catch, Redshift is always deployed within a VPC for security reasons and it's the default way AWS provisions it. Yes there are ways to make it publicly accessible but it is not recommended for security reasons.

To solve the accessibility problem keeping the security in mind, AWS offers Redshift data API which gives public access to the data within the redshift cluster in a VPC to trusted entities via IAM roles. Using IAM roles it is very easy to grant or revoke access to a specific person without having to rotate the credential of a database.

From a usability point of view Redshift data api sucks when compared with psql or pgcli. Hence i am creating rdapp project.

Your feedback is most welcome ☺️

rueian commented 2 years ago

IMO, Handling IAM permission in a wire proxy will be much easier. You only need to customize the startup message and then just proxy the rest to the backend.

There are already some tools that do something similar, for example, https://goteleport.com/docs/database-access/guides/postgres-redshift/

kishaningithub commented 2 years ago

Correct me if i am wrong, but the catch i see is that i will have to install an additional component within the AWS env which might not be desirable in all environments especially production.

rdapp removes that need and one can access data using psql/pgcli even in restricted envs like prod(given you have access granted via IAM) from their local laptops without installing anything on the AWS envs.