begriffs/postgrest

REST API for any PostgreSQL database.

Alastair McKinley

@a-mckinley

i have been using postgrest for sometime, I haven't seen this one before

Alastair McKinley

@a-mckinley

going back to 7.0.1 for now has fixed this

Josef Gabrielsson

@aramean

Is it possible to get a random row from the table without creating a view?

1 reply

laurenceisla

@laurenceisla

@a-mckinley, Remo answered to this issue here:
https://github.com/PostgREST/postgrest/issues/1921#issuecomment-903083220
Let me know how it went.

ƊŗęДdϝul Ȼʘɗɇ

@DreadfulCode

Hi all! Just "discovered" Postgrest today and successfully installed the Postgres binary - and it looks really cool! and it seems really snappy. I am looking forward to trying this out with Flutter.

1 reply

Alastair McKinley

@a-mckinley

@a-mckinley, Remo answered to this issue here:
https://github.com/PostgREST/postgrest/issues/1921#issuecomment-903083220
Let me know how it went.

thanks @laurenceisla , issue sorted. My issue was caused by bad interpolation of the PGRST_DB_URI env variable in my docker-compose.yml. Thanks for your help

2 replies

Edmund Cape

@EdmundsEcho

First day looking at postgREST - Do I need to create a schema for postgrest separate from what I use to build the underlying db? If so, where is the map between the types used in SQL vs postgrest: e.g., VARCHAR -> Text. Thanks in advance!

4 replies

Edmund Cape

@EdmundsEcho

I'm making solid progress on the db api. PostgREST is truly well designed.

I bumped into something: I'm able to list out the users using the api schema. However, for some reason I can't seem to repeat the success for the projects endpoint of the api.

The api code:

CREATE VIEW api.projects as
    select id, owner_id, redux_config
    from tnc_db.projects;

-- webuser access
GRANT
      select,
      insert,
      update(owner_id, redux_config),
      delete
  on api.projects
  to webuser;

As mentioned, using the same strategy for users I'm able to hit the endpoint and get what is expected... not so for the projects endpoint.

It seems that the api view is not accessing the underlying tnc_db version of the table.

When using psql as the superuser:

select id, owner_id, redux_config
from tnc_db.projects;
> ...
> (4 rows)

# but not so in the `api.projects`, a view
select id, owner_id, redux_config
from api.projects;
> (0 rows)

The question

Any ideas as to what might prevent the api wrapper from pulling the records from the source table given?

PS: It's does not seems to be an access/role issue; supported by the problem despite the user of a superuser role. I get the same (0 rows) response using the webuser role.

6 replies

SagarJoveoGIT

@SagarJoveoGIT

Hi ProstgREST Team,
I am trying to connect to Amazon Aurora PostgreSQL but after connection, i am unable to get data using API it says not connected.

1 reply

DavidFreire-FEUP

@DavidFreire-FEUP

Hey guys, does anyone know if PostgREST is supported in raspbian? Trying to get it running on my Raspberry Pi 4

1 reply

Edmund Cape

@EdmundsEcho

What is the difference between granting references to a role, vs creating a policy on a table for select?

GRANT references, select(id, email, active) on table db.users to auth;

-- versus
CREATE policy webuser_read_users
     on db.users
     for select

The policy allows for row-level security; that seems unique to what a policy can do.

Does `references` grant more than a read/select?

When do you know that a role needs to be granted `references`?

1 reply

Edmund Cape

@EdmundsEcho

I'm getting closer. I have the views up and running. Now I'm working on the login capacity.

For some reason, whilst swagger is able to see the endpoint specified using a function, it returns the following error:

TypeError: NetworkError when attempting to fetch resource.

Postman returns message:

"relation \"api.login\" does not exist"

Anyone have this happen?

3 replies

Edmund Cape

@EdmundsEcho

@laurenceisla Thank you for your responses.

timlamec

@timlamec

Hi All, I am running the postgrest tutorial. I have set up the tutorial.conf file to read my remote machine IP but I am not getting any data when I do a curl request. What could be the problem?

5 replies

Edmund Cape

@EdmundsEcho

re Foreign key detection for embedding:

I have a users table
there are 4 tables (permissions, profiles, tokens and sessions) that reference the users.id as a foreign key (so alter table permissions add constraint...)

Will postgREST embed all of these tables?

I seem to recall reading somewhere this could be a problem... and might explain what I'm observing with the following endpoint:

/users?select=email,permissions(permission)

that returns "could not find a relationship between users and user_permissions in the schema cache".

10 replies

riderinred

@riderinred

Hello, I have set up a remote Postgres DB and successfully connected with pgadmin. Now I want to connect my node js program via port forwarding. I have tried port 5432 without success, could someone give me a hint or point me to a ressource? Would be much appreciated.

1 reply

Max Buck

@buckmaxwell

Hi - playing around with this project this evening. Very cool! I went through the tutorial the other day but am wondering how you all usually track db changes in code / version control? (the tutorial suggests running sql directly against the db)

5 replies

István PONGRÁCZ

@pongraczi

Hi! Could you tell me, is that possible to access the client's public IP address via the env_var somehow? Background: pgsql+postgrest on a remote server behind a public ip address. Clients want to login from an other location, behind a public ip somewhere. The Apache on the server proxies the request from client to the postgrest port.
I would like to access to the client's public IP inside postgresql. The main goal would be: block brute force login attempts by detecting too many unsuccessful login attempts from the same IP.

1 reply

Probably somebody already implemented this, I just started to search the net, so, this is an in-progress research :)

thank you in advance, any hint welcome of course

Hi - playing around with this project this evening. Very cool! I went through the tutorial the other day but am wondering how you all usually track db changes in code / version control? (the tutorial suggests running sql directly against the db)

@buckmaxwell I started to use sqitch and I am very happy with it.

http://sqitch.org/ combined with git

Brad Dudra

@bdudra

Greetings, does anyone have any examples or guides for getting PostgREST running on Kubernetes?

1 reply

Nick Retallack

@nickretallack

Hi. I'm on an M1 macbook. I tried following the instructions here: https://postgrest.org/en/v8.0/install.html#containerized-postgrest-and-db-with-docker-compose

But the output of this is not promising.

db_1       | 2021-09-11 18:52:04.151 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1       | 2021-09-11 18:52:04.151 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1       | 2021-09-11 18:52:04.153 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1       | 2021-09-11 18:52:04.159 UTC [26] LOG:  database system was shut down at 2021-09-11 18:52:01 UTC
db_1       | 2021-09-11 18:52:04.162 UTC [1] LOG:  database system is ready to accept connections
swagger_1  | 2021/09/11 18:52:05 [emerg] 71#71: io_setup() failed (38: Function not implemented)

Nick Retallack

@nickretallack

Hm. Maybe it is working? Swagger is serving, but it fails to access postgrest. Also it'd be nice if it was clear how to get back into the tutorial if you went the docker-compose route. At this step:
https://postgrest.org/en/v8.0/tutorials/tut0.html#step-4-create-database-for-api
I actually ended up running docker exec -it postgrest-tutorial_db_1 psql -U app_user app_db, and yeah I know this command is affected by the parent directory of the docker-compose.yml file.

Nick Retallack

@nickretallack

I tried doing it without docker but no luck there either. There's no prebuilt binary for M1 macbooks. Here's what happens when I try to build one: https://gist.github.com/nickretallack/dd6ed554e0840e0abdb9fe3b0752b355

Nick Retallack

@nickretallack

When I run the docker-compose thing, the server container exits with code 137. It doesn't log anything.

It's probably because I'm on an M1 macbook.

Nick Retallack

@nickretallack

Seems this is a known issue?
supabase/cli#33
PostgREST/postgrest#1785
Anyway, brew install postgrest succeeded.

Nick Retallack

@nickretallack

https://postgrest.org/en/v8.0/tutorials/tut0.html#step-5-run-postgrest in this step it has postgres' port as 5433 but postgres' default port is 5432

Max Buck

@buckmaxwell

Hi, what are your suggestions for supporting self-serve user creation with a postGREST app? (I need this basically https://github.com/PostgREST/postgrest/issues/260#issuecomment-139843716).

1 reply

laurenceisla

@laurenceisla

@nickretallack Hey Nick! I opened an issue (PostgREST/postgrest-docs#434) to include more installation methods to the "Tutorial 0", not only Docker. Thanks for the feedback on the tutorial.

James Carr-Saunders

@jamescs50

hi. I'm not very good at computer.

so - I'm trying to install postgrest on a raspberry pi. Ive used the ubuntu-arch64.tar binary.

once extracted `sudo apt install ./posgrest

gives me "unsupported file"

and./postgrest gives me "Cannot execute binary file: Exec format error.

so - will postgrest work on a pi? could I try building from source (I know nothing about building Haskell) or am I just being a dumbo?

laurenceisla

@laurenceisla

@jamescs50 Hi, James. What's the architecture of your Raspberry Pi? Execute this command lscpu | grep Architecture to verify.

James Carr-Saunders

@jamescs50

@laurenceisla it's a 4b

armv7l

James Carr-Saunders

@jamescs50

@laurenceisla thanks for looking at this

6 replies

Nikolai Lebedev

@nextstopsun

How does postgrest set its client time zone? Are there environment variables to control it?

1 reply

Evan Kaufman

@EvanK

Is there a GUC setting to get the current user's JWT? Not the claims, but the encoded token string

2 replies

Vladconnectearth

@Vladconnectearth

Hi guys, is there any way to make postgrest execute some SQL after opening a connection to pg? Or make pg itself do that. Context: I'm trying to integrate Apache AGE (pg graph extension) with postgrest and it requires a LOAD 'age'; to be executed first in every session. Any way to achieve that?

2 replies

servaldeneptuno

@servaldeneptuno:matrix.org

[m]

hey there! is there any way I can call computed columns with an extra argument?

1 reply

servaldeneptuno

@servaldeneptuno:matrix.org

[m]

thanks!

Pallab

@Pallab29995421_twitter

Which url i can use as livenessProbe & readinessProbe? I don't want to put extra burden on the database.

1 reply

Vladconnectearth

@Vladconnectearth

is it possible to do row filtering on a procedure result? I can't get it to work, query like /rpc/match_merchants?similarity.gte.0.5 just returns everything and when I turn on postgres query logging similarity does not propagate into generated SQL at all

1 reply

Alastair McKinley

@a-mckinley

Apologies if this has been addressed in the past, is there a reason why the ~ operator family (~*,!~*) etc. are not implemented as row filters?

Where communities thrive

People

Repo info

Activity

The question

Does references grant more than a read/select?

When do you know that a role needs to be granted references?

re Foreign key detection for embedding:

Will postgREST embed all of these tables?

Does `references` grant more than a read/select?

When do you know that a role needs to be granted `references`?