Allowing use of postgres row level security in prest api by using set role translation

[jeremybusk]

The closest I could find is #549

I'm new to prest and newer to go but I don't think this will help me exactly with what I'd like.

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 or I might have to use the middleware but not sure how to do that exactly using middlewares.

This is a great project I've been hoping would start for years.

[avelino]

That's a great idea, it takes the responsibility off the provider to manage username and password, using the Postgres authentication gear

it is really not on the short-term roadmap to implement this functionality

I see this option being optional, the user (admin, dev, etc) configures what will be the authentication gear
https://docs.prestd.com/prestd/docs/deployment/server-configuration/#auth

Do you have the knowledge to implement it?

[jeremybusk]

I don't have time/expertise right now. Maybe if someone could help me that knew more about this project. My work is a lot of Kubernetes & platform these days & this is mainly hobby personal stuff so not as good. I've used postgrest in the past & used nginx+lua to do auto rest request inserts to add user_id but that was a long time ago. I'd like to use prest. Maybe I'll have more time in fall or someone else can run with it. I like doing development but just don't get much time to do pure dev work.
This is a postgrest way but not as elegant as I'd like.
https://postgrest.org/en/stable/auth.html
Also as noted in README.md I would prefer using Go than Haskell app if possible.

[avelino]

thanks for the feedback, we will prioritize it in the roadmap for future deliveries - particularly (for me) this support makes sense

[jeremybusk]

Awesome. Let me know if I can assist. I definitely would need a guide to help me add Go logic into code correctly but glad to do what I can.
Here is one last link for reference on handling sessions within Postgres for postgrest https://github.com/monacoremo/postgrest-sessions-example/blob/master/app.sql.md but we could easily adopt for Prest.
It might spark some other ideas for the Prest auth feature.

I see us just adding some more columns to users table like role.
docker-compose exec postgres psql -d prest -U prest -c "INSERT INTO prest_users (name, username, password, role) VALUES ('pREST Full Name', 'prest', MD5('prest'), 'username_with_only_underscores')"

You could add more columns for functions/permissions if you wanted to the role would be the biggest. Prest could also give an option to automatically handle the permissions sync the appropriate RLS policies to the database with an
RLS_TABLE_PERMISSIONS=auto
or something like that to apply Postgres create policy to all tables automatically. This could be done in go sql code or as a postgres function on the backend.

Thanks again for quick response and all your and others previous work on this project!