0

I need to create a set of views in which a database user with select permissions will query data, but I want to create a generic view, so if other users want to query the same data, I wouldn't need to create more views based on theirs "user_id". An example would be:

CREATE OR REPLACE VIEW user_data AS SELECT * FROM users JOIN something WHERE user_id = ?

Is there any way to set that if the database user querying data is "db_user1", then the view with the above select would query using 1 as the user_id? And if the database user is db_user2 than the where user_id would be set to 2?

Matheus Miliorini
  • 5
  • 2
  • 1
    Paramatrized view <=> function [Pass In “WHERE” parameters to PostgreSQL View?](https://stackoverflow.com/a/11402004/5070879) – Lukasz Szozda Aug 14 '19 at 16:46

1 Answers1

0

For this specific use case, you could do something as simple as this:

create or replace view user_data AS 
  select * from users 
  where user_id = (select substring(current_user, length(current_user))::int);

However, this will return an error if the user's name does not end with something that can be turned into an int.

You may want to look into row level security for something a bit more robust and configurable.

Jeremy
  • 6,313
  • 17
  • 20