I think views are great way of abstracting out business logic. The one thing keeping me from using it more frequently is that it doesn’t work well with Row Level Security.
Creating a separate "data owner" and "api owner" to own the api and data schema+tables/views respectively alleviates this problem. Your API schema owner != your table owner unless you `ALTER TABLE FORCE ROW LEVEL SECURITY` in addition to just enabling it.
What issues are you running into with RLS?
We can test this.
```sql
create role data;
create role api;
create role bob;
create role alice;
create schema api_v1 authorization api;
create schema data authorization data;
set role data;
create table data.user_profile (
id int generated always as identity primary key,
username text,
value text
)
grant usage on schema data to api;
grant select on data.user_profile to api;
insert into data.user_profile (username, value) values
('bob', 'Bob''s First Value'),
('bob', 'Bob''s Second Value'),
('alice', 'Alice''s First Value');
create policy "Users can only see their own data"
on data.user_profile
for select using (current_user = username);
alter table data.user_profile enable row level security;
select * from data.user_profile; -- shows 3 record, both bob and alice's
set role api;
create view api_v1.user_profile as
select * from data.user_profile;
grant usage on schema api_v1 to bob;
grant usage on schema api_v1 to alice;
grant select on api_v1.user_profile to bob;
grant select on api_v1.user_profile to alice;
select current_user; -- currently api
select * from api_v1.user_profile; -- shows 0 records
set role bob;
select * from api_v1.user_profile; -- shows 2 records, both bob's
set role alice;
select * from api_v1.user_profile; -- shows 1 record, alice's.
-- both alice and bob cannot access the data schema or it's contents. RLS is respected for the api_v1 schema.
```
CREATE OR REPLACE FUNCTION my_view() RETURNS TABLE (...columns) AS $$
SELECT ...;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_view();