pREST – A fully RESTful API from any existing PostgreSQL database writting in Go | Hacker News Reader

pREST – A fully RESTful API from any existing PostgreSQL database writting in Go

github.com

176 pointsavelino85 comments

If the author is here, I'm curious about what they mean by this from the README:

> There is the PostgREST written in haskell, keep a haskell software in production is not easy job, with this need that was born the pREST.

Have people faced operational difficulties with PostgREST? From what I hear it's pretty battle-tested and performant.

Working with Haskell is not easy, finding Haskell developer is even worse. So we created the project

PostgREST is an executable. You are not supposed to change it any more then you change something like PostgreSQL or Nginx. It does not matter what language it's written in.

> You are not supposed to change it

You are not supposed to change software?

Do you use Linux in your stack? Do you often extend the kernel? How about nginx or Apache Webserver? Tomcat? Node.js?

If you want to contribute to and extend any of those projects you'll usually have to learn C, C++, or Java first if you don't already know them (or otherwise use the approved extension points if they have them, or link to them via some kind of FFI where available, etc, etc).

> If you want to contribute to and extend any of those projects you'll usually have to learn

Which is exactly why I don't use software written in java, haskell, nodejs and prefer things written in python or go.

Given the choice between PostgREST or pREST I would chose pREST every time simply because if it breaks or ends up unmaintained I would have a chance in fixing it myself.

I'm sure there are many people who have the exact opposite stance, and that's fine too. This shouldn't be surprising to anyone.

How often did you change the python/go compiler, how about an apache module? :)

edit: Point being every day you use a lot (i would say majority) of software which most probably it would be very hard for anyone to "change" and yet this seems to work. There's no point in making a decision to use some software by thinking "am i personally able to understand it's codebase". By this logic there is very little software one person should use, no one is able to know everything.

Let the experts (in that particular software) work on it, while you contribute to the things where you are an expert, everybody wins.

I remember an article here not long ago about the distinction between an expert and a smart person, interesting read.

If PostgREST was a niche project with a single maintainer, I'd argue you're right - it's too risky to go with if you can't maintain it yourself.

But once the project is large and popular enough (and PostgREST arguably is), you don't maintain it yourself anymore. Keep in mind than in any case you're probably using PostgreSQL and Linux: major pieces of complex C code that you probably can't and won't maintain yourself.

To be clear, I do think being able to understand the source code for the system that you're running on and even submit patches or pull request is nice, but this is not what you pointed out as your reason, and it's also never going to be my #1 priority for a production system. For instance, PostgREST looks more mature than pREST and that's a pretty big consideration for a production system.

> Which is exactly why I don't use software written in java, haskell, nodejs and prefer things written in python or go.

I also prefer Python and Go but to say I would not use the software because of the language it's written in just sounds silly to me. Heck, I really dislike PHP but would still use PHP-software when it suits my need.

> Do you use Linux in your stack? Do you often extend the kernel? How about nginx or Apache Webserver? Tomcat?

You're talking about a massively different scale of thing here, especially in terms of lifecycle and long term support guarantee.

You have a point there, but it was probably an exaggeration to give an example.

But talking about small things, and long term support. Remember when someone removed a package from npm and the (Node) world stopped for a second? Where is the long time support there and guarantee. But still, this is how all of it works. Take any language, build anything nontrivial, i bet you'll end up using libraries far less supported and much younger then PostgREST.

> Do you use Linux in your stack? Do you often extend the kernel? How about nginx or Apache Webserver?

I have done all of these at one point or another in my career, as well as several others, and I have not been a contributor back to them. These were either know issues with separate fixes and consequences or very specific to the company I was working for at the time.

Do I know C or C++. The answer is not really... However C and C++ (and to some extent java) are pervasive. Haskel doesn't fall into that camp.

If your a Golang shop and you have a need for this, then it might be something you choose incase you need to support it, or tune it or mould it to your needs.

Beyond that, projects like this are GREAT for go. You have a well defined problem space, an existing tool and a reimplementation like this is only going to grow the authors knowledge and expand the Golang universe.

There is a citizenship aspect of this that seems dubious, though. Expanding "the Golang universe" with a reimplementation isn't really expanding the computing capabilities available to all of us; or even to people doing Go.

With regards to growing "the authors knowledge" -- contributing to PostgREST would have done a lot more for them there, since Haskell is quite different from Go.

> There is a citizenship aspect of this that seems dubious, though. Expanding "the Golang universe" with a reimplementation

Maybe, but reimplementations can evolve differently... This is why we have forks and distros, and to some extent linux.

> With regards to growing "the authors knowledge" -- contributing to PostgREST would have done a lot more for them there, since Haskell is quite different from Go.

Your making the mighty assumption that one wants to learn a language, or that the language is good (NOT taking a pot shot at haskell here) or that it makes sense in their environment.

I think you raise some important points however, that do need to be part of the decision to undertake reimplmentaion of something that already exists.

> Your making the mighty assumption that one wants to learn a language, or that the language is good...

I am not making either of these assumptions at all -- I am only asserting that there is plenty to learn from a typed functional programming language, if it seems difficult at first. That doesn't mean it is good, nor that you want to learn it.

Wise men learn more from fools than fools learn from wise men. Some languages are bad but because they are significantly different from what I learned before, I learned a lot from them.

I have successfully made small patches to postgrest using haskell-knowledge from a single semester at university 11 years ago.

really? isn't it the point in open source software.

The choice of words might be unfortunate, but the point is that you don't have to change it any more the PostgreSQL/Nginx in order to use and develop apis with it.

Unless you have different requirements than the baseline, which is common

In my experience, most such people just solve this problem by either:

1. submitting an issue to the project's issue tracker, and waiting;

2. submitting an issue to the project's issue tracker, and becoming a project sponsor to expedite;

3. customizing the software using a scripting language it exposes;

4. using a third-party "plugin" for the project that someone else already developed to solve the problem;

5. developing such a plugin yourself, using the software's C FFI API to write your plugin library in your language of choice, rather than the language the software itself is written in;

6. wrapping the software in a single-purpose gateway that decorates or mutates its wire protocol/API with the new features.

In practice, actually modifying the server software itself is very rare. Needlessly rare, even. Some great, clean codebases (Postgres itself; Nginx) get strategies #3-#6 applied so often that there exists a whole ecosystem of features "around" them that really should be in their cores, because everyone was too afraid to touch them even when they're written in common languages.

Can you briefly talk about the issues you experienced working with Haskell and the even worse issues you experienced trying to find Haskell developers?

Did you post on Functional Jobs, haskell-cafe, and r/haskell?

To play devil's advocate (as a huge FP fan myself), it's undeniably harder to find a Haskell-literate programmer compared to a Go one. If an organization is going to be changing and maintaining software, too, it can also be unwarranted debt and risk to introduce a new language and ecosystem into their software stack. Seems like NuevoHQ is a Go shop.

I was just confused by whether or not this project was inspired because it's difficult to extend PostgREST or because of operation issues with PostgREST itself. Seems like the former...though I'm not sure what this does that PostgREST doesn't.

It's technically correct what you are saying but it's a "businessy" way of looking at the landscape. Developers (and orgs even more) should try and always expand their area of competence, not limit themselves to one area (not saying they should be experts in all). A good piece of tech should make you want to learn a language more (RabbitMQ/Erlang, PostgREST/Haskell, Pandoc/Haskell/).

> It's technically correct what you are saying but it's a "businessy" way of looking at the landscape.

For sure. Hence my business-oriented qualification "If an organization is going to be changing and maintaining software...".

> Developers (and orgs even more) should try and always expand their area of competence, not limit themselves to one area.

About developers: absolutely. Constant growth is a must.

About large and mature orgs: agree. That's what R&D budgets are for.

About startups: disagree. Invest only in what you need to ship product and gain differentiated competitive advantage. When there's an infinite queue of features to build and bugs to fix, not taking on unwarranted technical debt is key - and it's easiest to take on accidental technical debt when you don't have experience in a tool, language, or ecosystem.

I think a point expressed elsewhere in this thread is worth reiterating: language agnostic APIs (e.g. a HTTP API) mean that I don't have to care what language something is implemented in to use it. That's a Good Thing. Learn Erlang to hack on RabbitMQ? Sure, for fun. But if I'm putting it in prod for customers, I care more about how my application code - in a language I'm strong with - interfaces with it than the internals.

This doesn't speak to the question that was asked, though. The original post -- "keep a haskell software in production is not easy job" -- implies PostgREST presents operational difficulties, which is quite different from what you are saying here.

There is plenty of software that people use without direct competency in the underlying language -- for example, running Jenkins to do builds for a RoR shop does not require a Java programmer.

I have been running PostgREST in production for about a year with zero issues. ProTip: One neat PostgREST feature is that it will output swagger specs for your db.

I know nothing about Haskell and that has not been an problem. I am always happy to see more open source software, though.

Where are you using it, can you share a link?

Not GP, but a listing of groups using PostgREST in production can be found here: https://postgrest.com/en/v4.1/intro.html#in-production

Ditto. ~1.5 years in one project, ~1y in another. Recent update is excellent -- now you can see HTTP headers & cookies inside Postgres functions, so it's possible to save user IP address, user agent, etc. So easy, quick, scalable and with great performance control.

Given the Swagger/OpenAPI spec for the DB, one can use Swagger Codegen [1] (free, open-source) to generate API clients, server stubs and API documentations.

Currently the project supports more than 40+ API client generators (C#, Swift, C++, TS, JS, etc) and 20+ server stub generators (C# Nancy, Python Flask, etc). For a full list, please refer to the project README.

[1] https://github.com/swagger-api/swagger-codegen

Discloure: I'm a top contributor to the project.

Additional security is absolutely necessary. The docs have table and field level permissions but that's not enough. A user should only be able to edit their own row in the database, and their password should always be one-way encrypted, etc. Also don't want a user to be able to add themselves to a user group, say add themselves to the "Administrator" user group by inserting a table row.

This framework may simply be too "thin" - there needs to be a place to code business logic and access control, neither of which map neatly to how RDMS treat users and security permissions. In which case might as well use a framework like Django or Rails or Bottle or Laravel or any other of the dozens of mature systems available.

> This framework may simply be too "thin" - there needs to be a place to code business logic and access control, neither of which map neatly to how RDMS treat users and security permissions.

It's certainly to thin since it doesn't even leverage db-level RBAC, but, used fully, I think PostgreSQL roles and permissions are reasonably robust. (Particularly, all the specific examples, including row-level permissions, you point to are addressed by PostgreSQL roles and permissions.)

All authorisation requirements are solved easier and better at the db level. Since you have permissions by columns and row level security, it means you can define permissions down to a cell.

As someone said, people just don't read the manual of their database past page 100 :) and use "root" when connection to the database from their code.

https://blog.2ndquadrant.com/application-users-vs-row-level-...

Does anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?

SET ROLE "user"

The biggest issue with this is roles are identifiers, which means you can't use parameters for them.

Can I do that inline with a statement? I don't think I'd mind outsourcing rights to the DB.

No, you'll need to call SET/RESET role as their own statements. The easiest way to do this in Java is to ensure RESET ROLE is called when checking a connection back into the pool, and calling SET ROLE in a servlet interceptor (probably the same one that handles authentication).

So, if I'm following this correctly, you'd need to start a new connection with the end-users credentials? Which would be slow and rule out connection pooling and other such niceties?

You can use connection pooling, the user you are connected with needs to be granted the role you are switching to (preferably with the NOINHERIT flag) - that's the purpose behind the SET ROLE and RESET ROLE statements.

Yeah, but users are a connection level concept, meaning you have to abandon your connection pooling system in order to use the db's user authorization and authentication system. Every new query is a new connection.

The link Ruslan shared explains how to use row level security with application users as opposed to db users.

Basically PostgREST translates cryptographically signed JWT claims from the client request into local SQL variables accessible by postgresql's "current_setting" function. The additional claims can identify the user beyond their db role. They can specify, for instance, the user's email address.

Row level security policies can use current_setting to refer to the extra claims. So multiple users can share a db role and connection pooling works fine.

> Every new query is a new connection

> users are a connection level concept

That is not true. There are users in the db that do not have login privileges.

PostgREST connects in with only one user (authenticator), then for each authenticated request it does `set local role alice` within a transaction, where alice is a db user with no login privileges.

A table starts out with zero rights, except to the user who made the table. Another user cannot even see its name.

You might first make database views, and grant only those to users, not granting any user rights to a table. PostgreSQL lets you run an update to a view and then translates it to an update on the table. So reads and writes on a view would both work.

PostgreSQL now has security by column and row. So you could do it that way too.

anyone with the privilege to login can see the names and relations of all the entities in the database.

Yeah, but if you are using a wrapper similar to this, you can easily prevent that while still using the underlying DB security mechanisms.

I'm not arguing using db for authorisation is bad, i was just pointing out the statement is incorrect (about not seeing other tables names). The fact that you can see the schema even without privileges is used by PostgREST and this weirdly contributes to security because the user with which it connects to the database (authenticator) does not need to have any rights, just the right to switch to other (specific) users.

You're right. I'm sorry! I remembered wrong.

But that's only if you can first connect to the database with a general-purpose client program, like psql or one of the GUIs. By default Postgres allows connections only from localhost. So even if you had a user account, you couldn't connect.

So how would something like this REST interface even work with accounts for each user? The REST program would have to be installed on the same server as Postgres, or you would have tell Postgres to allow network connections only from the server that the REST program is on. The REST program first connects with its own user account, say "resty". resty is a member of your account, say "jdoe" (users and groups are the same entity, and they're really called "roles", and one role can be a member of another role). If a role is member of another role, then it can become it, like this:

  set role jdoe;

Now you been downgraded to the rights of jdoe.

The REST program could pass through your username and password and relogin as you. Or it could trust that your web server authenticated the REMOTE_USER and just set itself to that variable.

This is probably part of the ongoing craze and ridiculous propaganda of let's just rewrite everything in Go. And they are all headlined as "written in Go" like somehow this now just fixed everything.

Yep, share project you know this project today

And seems now this propaganda is even borderline unethical, using FUD(claiming production issues with PostgREST) to promote their own projects. I'm fine with the fact that people become fanatics of their language, but that kind of behavior is just harmful for OSS.

Can someone explain why it is necessary or useful to have a RESTful API for a database that is never exposed to the Web? If I am going to use a library to access the database, why not just use the native driver? Why the JSON indirection?

So that others may consume the service

Because it is possible to call it directly from your front end app, no need for a server. And you can add logic into the DB using procedures. Ideal for simple apps and prototyping.

It's not really necessary because as you say, there are other ways.

But it's useful if what you want to do is create a simple API to access your data. If your use case includes buildinga CRUD app, or API, you basically get that out of the box.

If you have several components/clients (in different languages) which need to access your database it allows you keep a common interface instead of having to work with different specific libraries/frameworks for each language.

(PostgREST user)

The point is to allow untrusted clients (browser/mobile) to run (safe/predictable) queries against your database without the need for "backend/api" as a middle layer because nowadays most of the business logic is mostly on the frontend and it needs a way to get to the database.

It's not necessarily useful if you have trusted code running on your servers, it should just connect to the database. But if this works for you ... why not.

(At least that's how i think of it, others may use it in different ways)

> nowadays most of the business logic is mostly on the frontend

This is a design flaw imho and should not be used as a reason to motivate the use of such projects. If your logic is in the frontend you should rather build a backend that captures the logic instead of just putting a rest layer on your DB and call it a day.

Unless you are writing an app that should also work offline. In this case, most of your logic _must_ be your frontend, and your backend is mostly a data repository for data backup and synchronization. In such a scenario, database-as-an-API seems to be exactly the right approach.

The point is to expose it to the web. It's primary role is to be accessed by browsers/mobile apps, not code running in the backend

pREST is not vulnerable to SQL injection, recommend you run tests Check access level (permissions) in https://github.com/nuveo/prest#table-permissions

Is there a wiki for security? It sounds its performance is good to not have a middle layer between interface and database, but then I have no idea how do you apply SQL-injection prevention, rate limit and other QoS.

idk about this thingy but the original PostgREST is not vulnerable to SQL injection. You're not writing SQL, you're writing URL query strings, and PostgREST processes them correctly (I think it uses prepared statements for most things, and careful filtration for table names and such).

pREST is not vulnerable to SQL injection, recommend you run tests

postgrest.com vs postgres.rest: this is a bit confusing. Why not pick a different name?

i'd say all 3 projects have unfortunate names :) PostgreSQL/PostgREST/postgres.rest Some say that's why MySql is more popular ...

programming language writter, postgrest.com = haskell postgres.rest = golang

Now we wait for somebody to port PostGraphQL to Go. Tools like these are great. Having a single executable binary is even better.

It's not RESTful if it doesn't use hypermedia. Look into HAL, it actually probably makes this server a lot easier to use and discover when dealing with things like foreign keys.

There is not a single hyperlink or hypermedia control to be seen, prest is thoroughly disappointing.

HAL gives you both links, so you can discover resources and transit between them, and URI templates, which are the controls to make the filtering stuff work.

Very few people refer to the pedantic HATEOS version of "REST" when referring to REST these days (it's really just a repetitive no true scotsman argument now) , and that's fine. Almost nobody implements it as part of what's now considered a restful api.

What is the non-HATEOS version of REST? Can you usefully define it?

In practice it means "HTTP, plus certain conventions about Method and Path that David Heinemeier Hansson happens to like".

I still cannot figure out why anyone would ever want to meet such a standard, other than to trick the ignorant.

What do you consider a RESTful api?

Looks good, but only for direct table access. I only use functions to read and write to Postgres. An API for calling functions seems to be missing, so is useless for me. Have I missed something? Obviously could be achieved by executing SQL scripts, but this is not very elegant. Any plans to add a function API?

Is there something mature along these lines for MySQL?

Why would you want that? Genuine question: why would you or anyone use MySQL for a new project?

I have an existing MySQL database that isn't going anywhere soon, and might want to utilize something like this with it.

check out sandman, https://github.com/jeffknupp/sandman2

Today pREST not accept MySQL, more pREST developer working adapter environment, check in https://github.com/nuveo/prest/tree/master/adapters

I recommend you open issue on pREST and recommend MySQL support

If you're putting REST in front of your database, you're gonna have a bad time.

Performance suffers, and transactionality is a non-starter.

Best to forego this style and instead go with more of an RPC system.

I am guessing you did even look an how PostgREST works :)

For the lazy, would you mind summarizing?

EDIT: actually, after a few minutes on their docs page, I'm still not sure "how it works" regarding perf, so this could be useful even for the not-so-lazy :-)

Writing docs is hard ... You can think of PostgREST as one big pure(ish) function It translates ONE http request to ONE SQL query and executes it in a transaction (after setting some context). There is very little overhead. 90%+ of the time of a request is consumed by the db running the query. So usually, a request is about 5ms-10ms (without network time).

Interesting. Thanks for explaining!

Yes, I didn't even click the link.

Exist issue to support RPC https://github.com/nuveo/prest/issues/18

Reply on news.ycombinator.com