Hello everyone, is there some way to switch GET requests to read/write transactions without hard recompiling PostgREST? I'm trying to use a pre_request function that extends user's session, does some logging, and switches the local role upon the user's session token, and most of those things will fail in a read only transaction. I tried doing 'set transaction' as a first thing in pre_request function, but it's already too late.
10 replies
@maxime-beck Right now it's only possible by base64 encoding the payload and then decoding it on the db. Check https://github.com/PostgREST/postgrest/issues/922#issuecomment-593945310.
Hi everyone, it try to use postgrest with some datatypes like inet and jsonb, how could i handle following operators?
'>>=' or '->>' from doc: https://www.postgresql.org/docs/9.3/functions-net.html or https://www.postgresql.org/docs/9.5/functions-json.html
'>>=' or '->>' from doc: https://www.postgresql.org/docs/9.3/functions-net.html or https://www.postgresql.org/docs/9.5/functions-json.html
>>= is not. It's unfortunate that pg didn't choose @> for the inet contains operation.
Only way for that to work would be an stored procedure. Or maybe you can create an operator alias for
>>=.
Hi everyone, I'm trying to use Postgrest on Ubuntu 1604 LTS. But, when I tried to access to Postgrest on the VPS in DigitalOcean.com I haven't access to port 3000. The params on config file are:
db-uri = "postgres://user:pass@IP:5432/database", db-schema = "public" ,db-anon-role = "postgres", db-pool = 10, db-pool-timeout = 10,
server-host = "*", server-port = 3000.
What I've doing wrong?
2 replies
I have:
api.view_of_table
api.view_of_anothertable_from_otherschema
api.view_of_yetanothertable_from_otherschema
public.table
otherschema.anothertable
otherschema.yetanothertable
public.table(somefk references otherschema.anothertable (the_fk))
otherschema.yetanothertable (somefk references otherschema.anothertable (the_fk))
Should a request like table?select=*,view_of_anothertable(*),view_of_yetanothertable_from_otherschema(*) work in such case? PostgREST complains, that there's no relationship between table and yetanothertable
2 replies
So I have been following tutorial 0
And I have a few doubts
this is what I did in the psql
CREATE SCHEMA api;
CREATE TABLE api.users(
last_name TEXT,
first_name TEXT,
user_name TEXT UNIQUE,
email TEXT UNIQUE,
has_premium BOOLEAN DEFAULT FALSE,
date_created TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
user_id BIGINT GENERATED ALWAYS AS IDENTITY
(START WITH 10000000 INCREMENT BY 1) PRIMARY KEY
);
CREATE TABLE api.passwords(
encrypted_password TEXT,
ref_user_id BIGINT,
FOREIGN KEY(ref_user_id) REFERENCES api.users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX idx_user_id ON api.users(user_id);
CREATE INDEX idx_user_name ON api.users(user_name);
CREATE ROLE web_anon NOLOGIN;
GRANT web_anon TO postgres;
GRANT USAGE ON schema api TO web_anon;
GRANT SELECT ON api.users TO web_anon;
CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'pwd here';
GRANT web_anon TO authenticator;
CREATE ROLE todo_user NOLOGIN;
GRANT todo_user TO authenticator;
GRANT USAGE ON schema api To todo_user;
GRANT ALL ON api.users TO todo_user;
I also use a python library for postgrest
this one
So this is a function I have to interact with postgrest
class UserManager:
"""This is the class that deals with events
revolving around StockUsers in the database"""
def __init__(self):
self.host = "http://localhost:3000/"
async def create_user(self, first_name, last_name, username, email, password):
"""Adds a user to the StockTools database"""
print('receiver end')
async with postgrest.Client(self.host, default_headers={"role": "todo_user"}) as client:
try:
print('inside try block')
user_insert = await client.insert("users", {'last_name': last_name, 'first_name': first_name,
'user_name': username, 'email': email},
returning='representation')
print('user insert complete')
print(user_insert)
await client.insert('passwords', {'encrypted_password': hashlib.sha3_512(password).hexdigest(),
'ref_user_id': user_insert})
print('inserted passwords')
except Exception as err:
print(err)
raise CannotManageUserData(err)
print('user created')
So In the default_headers parameter of postgrest.client I have defined the
todo_user role
So, it should have access to insert into the database right?
well, when I try to run the script I get this error:
401, message='permission denied for table users', url=URL('http://localhost:3000/users')
thanks
but after that I get this error
doing the same request given in the tutorial
C:\Users\iyapp>curl http://localhost:3000/todos -X POST ^
More? -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoidG9kb191c2VyIn0.nMv9RT-HXsUmQLz1fv91APpIvRobaoGiK4QJDwKo9hw" ^
More? -H "Content-Type: application/json" ^
More? -d "{\"task\": \"learn how to auth\"}"
{"hint":null,"details":null,"code":"42501","message":"permission denied for table todos"}
Why? pls help me
I have everything correct
this is my conf file
db-uri = "postgres://authenticator:mysecretpassword@localhost/test"
db-schema = "api"
db-anon-role = "web_anon"
jwt-secret = "testtttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttsecret"
-H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoidG9kb191c2VyIn0.nMv9RT-HXsUmQLz1fv91APpIvRobaoGiK4QJDwKo9hw"
so am using windows
how can i set a variable for it
waIT OMG IT ACTUALLY WORKED
THANKS
RUSLAN UR PRO
MAN THANKS