Prest - Postgre rest

Prest

• https://docs.prestd.com/
• https://github.com/prest/prest
• https://github.com/prest/prestd-buildui
• https://github.com/prest/bgworker

https://github.com › prest › prest prest/prest: PostgreSQL ➕ REST, low-code, simplify and accelerate ...

https://eltonminetto.dev/en/post/2021-08-31-prest/

https://github.com/qor/admin

https://www.reddit.com/r/golang/comments/8xp13l/any_dbcrud_admin_package_similar_to_flaskadmin_or/?utm_source=share&utm_medium=android_app&utm_name=androidcss&utm_term=1&utm_content=share_button

Issues

Set role

• https://github.com/prest/prest/discussions/
• https://github.com/prest/prest/discussions/549
• https://github.com/prest/prest/discussions/new?category=api-server

https://github.com/prest/prest/discussions/new?category=api-server

I would like an easy way in prest to set mapping for authenticated users in order to use postgres row level security. You could have prest database owner/admin user of database that could do set role {{row level security user}}. This way you could use native row level security in postgres without having to use other mechanisms. Users could query rest api or postgres sql depending on their needs.

Say you have a Postgres database with a table like below and you are logged in as owner or super user of database.

CREATE TABLE items (
  item_uuid    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  updated_at    TIMESTAMP NOT NULL DEFAULT now(),
  current_user    NAME      NOT NULL DEFAULT current_user,
  name   TEXT
);

You then enable row level security, user and add some inserts to the table.

ALTER TABLE items ENABLE ROW LEVEL SECURITY;

CREATE ROLE user1 WITH LOGIN PASSWORD 'user1';
GRANT SELECT, UPDATE, DELETE ON items to user1;

CREATE ROLE user2 WITH LOGIN PASSWORD 'user2';
GRANT SELECT, UPDATE, DELETE ON items to user2;

set role user1;
INSERT INTO items (name) VALUES ('test item 1 by user 1');

set role user2;

INSERT INTO items (name) VALUES ('test item 2 by user2');


Add my users to prest_users (this could be some different database if desired if wanted serperate.

docker-compose exec postgres psql -d prest -U prest -c "INSERT INTO prest_users (name, username, password) VALUES ('user1', 'prest', MD5('user1'))"

docker-compose exec postgres psql -d prest -U prest -c "INSERT INTO prest_users (name, username, password) VALUES ('user1', 'prest', MD5('user2'))"

docker-compose exec postgres psql -d prest -U prest -c "select * from prest_users"

Now when I run my query I would only run as my prest_user name, equivalent of set role command in postgres.

TOKEN=$(curl -s -X POST http://127.0.0.1:3000/auth -H "Content-Type: application/json" -d '{"username": "user1", "password": "user1"}' | jq -r .token)

curl -i -X GET http://127.0.0.1:3000/prest/public/items -H "Accept: application/json" -H "Authorization: Bearer {TOKEN}"

Any thoughts on this. I'm just trying to push as much functionality to postgres in order to make security and management easier. This might not be in scope of app. This is a great project I've been hoping would start for years.