postgrest / postgrest-docs Goto Github PK

Having documentation live with the code has some benefits:

• Discoverability: I spent a while digging through begriffs/postgrest looking for the docs to submit a docs correction, before I found this repo.
• Synchronization: Commits and pull-requests would atomically change source and documentation together.
• History: Finding old docs, or docs specific down to a specific commit in the source, would be the same as finding that version's source-code -- no cross-referencing or guesswork.
• Precedent: Many other projects do it this way. (I'm lazy but can get a list of examples if you want).

@begriffs summarized the original reasoning behind having this separate docs repo in begriffs/postgrest#805

1. Might want to store big assets like pictures in there (for instance all those pictures for the Heroku docs in 0.3) and don't want to slow people or CI down during the git clone.
2. Didn't want to litter the root of the project with docs configuration, but might not be a big deal.

In my opinion, the costs outweigh the benefit of those two points, but I guess it's somewhat subjective.

If a pull-request to move the docs to the main repo would be accepted, I'd be happy to start working on it :)

Original issue: PostgREST/postgrest#531

Assume that when a record is created in a table, an email needs to be sent out. Also, some front-end admin tool (like admin-on-rest) is operating against the api created by postgrest. Where would the logic go that sends the email after the record is created?

I assume in this case we need an additional backend on (Node.js for example) that proxies the rest interface by postgrest (effectively sits between the postgrest api and the admin tool), and will intercept some operations and do additional stuff (like sending the email).

Can you share some thoughts on this topic?

How to hook up a queue to listen/notify and read items from the queue. Alternately how to log events to be done in an internal table. Crib from articles online about how to get the locking semantics right in sql.

Show how the Server response header contains version info. Very helpful for debugging and people ask about this.

For instance how to proxy localhost:8000 to api.example.com:80.

Original issue: PostgREST/postgrest#457

More details in original issue: PostgREST/postgrest#443

This blog post presents a great approach for auth:
https://blog.2ndquadrant.com/application-users-vs-row-level-security/

Postgrest does not allow arbitrary user queries, so the article's approach becomes really simple. Our docs need a clear example of how to do auth.

Fill in the missing example in https://postgrest.com/en/v0.4/admin.html#count-header-dos

Running multiple postgrest instances on different ports and delegating to them via nginx

It can interpolate env vars, and also allows copying in an entirely new config file

Maybe make a new section about running in docker

Most people will want to use it for logging users out before their JWT has expired. The current example doesn't correspond to any use case.

https://postgrest.com/en/v0.4/auth.html#custom-validation

I have a view that a coworker created based on the authentication example for postgrest. I was wondering now if it should use the WITH local CHECK OPTION. It currently uses a trigger. It does not work as expected. The view is:

CREATE OR REPLACE VIEW users AS
  SELECT
    actual.role       AS role,
    '***' :: TEXT     AS pass,
    actual.id         AS id,
    actual.email      AS email,
    actual.first_name AS first_name,
    actual.last_name  AS last_name,
    actual.verified   AS verified,
    actual.blocked    AS blocked
  FROM basic_auth.users AS actual,
    (SELECT rolname
     FROM pg_authid
     WHERE pg_has_role(current_user, oid, 'member')
    ) AS member_of
  WHERE actual.role = member_of.rolname
-- needed for update/insert ???
-- WITH local CHECK OPTION;

If I use the WITH local CHECK OPTION, I get an error - WITH CHECK OPTION is supported only on automatically updatable views. I assume because of the sub-select?

The trigger is:

DROP TRIGGER IF EXISTS update_users
ON users;

CREATE TRIGGER update_users
INSTEAD OF INSERT OR UPDATE OR DELETE ON
  users FOR EACH ROW EXECUTE PROCEDURE update_users();

I use my browser UI to sign up/register a new user. Then if I go to users view in the DB (through my Postico postgres GUI client), edit the view's record so that I make it "verified = true", and change the role from "verfication_pending" to "verfiied_user". The underlying table does get updated, BUT I can't signin/login with that user (invalid credentials error from postgREST). If I update the basic_auth.users table directly with the exact same edits as I do to the view (again through my postico GUI), I can sign in successfully with that user through the browser. So there is something different happening when the update occurs through the view.

Another thing I was concerned about with the trigger: Is that looping over every user in the users view? Or is it looping over a set of just the user record that is being updated? (I wish there was some optional, well-defined, built in auth mechanism for postgREST)

Postgrest will allow

DELETE /foos

which without any filters will happily delete the entire table. We should document an nginx config which will prevent deletes or patches that lack filters (or at least require some other confirmation parameter in the request).

It would be helpful to have multi language version 😄
Is there anything I can do? I'd like to contribute with it.

Use ideas from
#42

It appears in PostgREST/postgrest#899 that centos7 may not include a localhost alias in its /etc/hosts file, so postgrest is unable to bind there. The default binding of *4 does work, but investigate if there's a suitable alternative for people who want to host locally but not expose the server to the outside world. Perhaps 127.0.0.1 works. Document this in the configuration section.

I am implementing CSP reporting using a RPC callback.

Such a report looks like:

{
  "csp-report": {
    "document-uri": "http://example.com/signup.html",
    "referrer": "",
    "blocked-uri": "http://example.com/css/style.css",
    "violated-directive": "style-src cdn.example.com",
    "original-policy": "default-src 'none'; style-src cdn.example.com; report-uri /_/csp-reports"
  }
}

Unfortunately, the standard requires using hyphens in the key name and PostgreSQL does not support using hyphens in named arguments. The standard only allows supplying an URI. Note that in this use case I do not control the request headers.

Possible solutions are:

• Normalize named arguments by replacing - with _ (and perhaps some other chars).
• Allow RPC calls without named arguments, instead taking one verbatim JSON argument.

The later has my preference: it is intuitively simple and can handle all JSON input. It would require some means of deciding whether to map the JSON to named arguments or not. Headers are not an option in my use case. Perhaps we can special case functions that have one named argument postgrest_raw_json json.

discussed here PostgREST/postgrest#908 and here PostgREST/postgrest#913

Use unlogged tables with strict permissions.

@deinspanjer will provide a specific sql example.

At the bottom of the table of contents at postgrest.com there is a version number selector that shows all branches/tags with version numbers that I have marked as "active." If we port the v0.3.2 branch from markdown to restructured text then I can activate it as a version that is selectable. That's how new versions will be too, so it it would be consistent that way. There are many people who are not ready to upgrade to v0.4 because of its backward incompatible changes, and these people would like easy access to the docs.

@uniphil you said you'd like to send a pull request to fix up that branch?

To review your changes locally you can

1. Install sphinx on your development machine.
2. sphinx-build -b html -a -n . _build then open _build/index.html in your browser.
3. Alternately sphinx-autobuild -b html -a -n . _build will run a temporary server on localhost which refreshes the browser on every change.

You'll want to add _build to .gitignore because it gets generated fresh on the Read The Docs server.

Config file needs documentation.

Is /etc/postgrest.conf correct in the docker file?
https://github.com/begriffs/postgrest/blob/master/Dockerfile#L14

To document extra libraries or whatever that must be installed

Existing information is out of date

Document how PostgREST uses read-only transactions when procedures are marked as stable or immutable. These type of transactions are required on read replicas of the database.

I'm talking with staff at Vimeo to resolve the privacy configuration conflict which prevents the video from playing.

For resource embedding.

This project no longer exists: https://github.com/PierreRochard/angular2-postgrest

Linking to https://github.com/PierreRochard/postgrest-angular or even https://github.com/PierreRochard/postgrest-boilerplate may be a suitable replacement?

When a singular response is requested but no entries are found, the server responds with an empty body and 404 status code rather than the usual empty array and 200 status.

For empty results we actually respond with HTTP 406 because the response is incompatible with the content type requested.

See PostgREST/postgrest#811 (comment)

PostgREST/postgrest#854

Not having the binary on the path causes an error like in PostgREST/postgrest#898

HI,
I will only ask, if is it possible to make a request of a procedure with an array in parameters?

CREATE OR REPLACE FUNCTION public.test_array(input text[])
  RETURNS integer[] AS
$BODY$declare 
ret integer[];
val integer;
txt text;
begin


FOREACH txt in array  test_array.input
loop
 val = cast( txt as integer ) ;
 ret = array_append(ret,val);
end loop;
return ret;
end;$BODY$

Thanks

Original issue: PostgREST/postgrest#585

As I'm connecting remotely to a my postgrest server, would logs still be saved there?

It is renamed in v0.4 and the docs still mention the old way in places

At https://postgrest.com/en/v0.4/api.html it says:

PostgREST can also detect relations going through join tables. Thus you can request the Actors for Films (which in this case finds the information through Roles).

That is awesome, and just what I need for something I'm building right now. Please show what the query/request looks like to do this! I can imagine a case where there are multiple tables that are join tables, and I'm not sure how PostgREST distinguishes between them.

The issue:
As stated several places in the docs, you can return a full object from a POST.
But this is especially difficult in regards to the "Multiple Tables Insertion or Update" guide section.

This is because a Trigger returns the "NEW" data, which is only the data passed in the actual POST.
If your trigger, for example, does an INSERT that creates a PK for a row, that data will not be in the "NEW" data.

A very easy fix for this would be to do a SELECT into the "NEW" before returning "NEW".

SELECT * INTO NEW FROM [table] WHERE [field]=[row PK];
RETURN NEW;

As someone in the Gitter channel pointed out, this might be something others have issues with, so this could be something to add into the docs.
See pastebin link for Gitter conversation.

Get a cert and configure Read The Docs to use it

Hi

I used the centos 6 build for release 0.3.2.0 on a RHEL 6 machine and started postgrest in the same way as the documentation describes (https://github.com/begriffs/postgrest-docs/blob/master/docs/install/server.md#running-the-server).

Doing this, however, does not work. The service starts but as soon as one does a request it fails to connect to the db and returns a rather cryptic error message: {"details":"missing \"=\" after \"postgres://db_owner@db-host:5432/db_name\" in connection info string\n","code":"","message":"Connection error"}.

After some discussion with @ruslantalpa on gitter he suggested that I use a different form of connection string. Turns out that passing the connection string like this "host=hostname user=username port=5432 dbname=dbname" to postgrest works fine.

I would like to do a pull request to make other users aware of this detail for centos 6 builds but I am not sure where this should fit into the documentation. One possibility would be to add a small subsection or a note in https://github.com/begriffs/postgrest-docs/blob/master/docs/install/server.md#running-the-server.

If you would like me to add this to the docs please let me know where.

cheers
Leon

Document the use of PostgREST/postgrest#849

On rhel7 I had to use this:
tar Jxf postgrest-0.4.1.0-centos.tar.xz
Instead of this (from install doc):
tar zxf postgrest-[version]-[platform].tar.xz

I'm not a unix wizard so I could be missing something

• Using the deploy to heroku button on the repo
• Describe available options
• Show the anatomy of a postgresql connection string
• Mention RDS IP permissions
• Keeping the DB geographically near the heroku dyno (virginia region)

We already document Accept: application/octet-stream in https://postgrest.com/en/v4.1/api.html?highlight=octet#binary-output, but we should also note how it works with RPC. (Functions returning scalars work fine, and those returning rows need to be filtered to a single column)

Link to https://postgrest.com/en/v0.4/api.html#computed-columns because that part has info about the topic but is not very well exposed.

Its vimeo privacy settings prevent it from playing.

Is this even the video we want to present? It's pretty old. Do we want a video on the front page?

Original issue: PostgREST/postgrest#578

Could add it to https://postgrest.readthedocs.io/en/v0.4/admin.html#https

Suggested in PostgREST/postgrest#37

The web standards use percent encoding for unicode values. Document some examples from https://github.com/begriffs/postgrest/blob/master/test/Feature/UnicodeSpec.hs