Role-based authentication and row-level security

[davenquinn]

We need to standardize and stabilize our authentication system, to allow us to start building authenticated functionality in a more "controlled" fashion.

This includes several near-term steps:

• Integrate authentication code from macrostrat.auth_system #114
• Logically split auth API from the rest of the map ingestion API #120

Additionally, it may be useful to build out some proof-of-concept routes for several levels of functionality:

• Saved locations functionality can allow testing Row-level security (people should be able to see only their locations)
• Admin routes (e.g., a route to refresh PostgREST schemas) can test admin vs. user gating
• Updating and merging Column builder authentication / role based access control #156 would provide RLS functionality to column creation

#99 may be a useful reference

[amyfromandi]

@davenquinn here's a new PR which introduces and tests Row-Level Security (RLS) for the user locations feature. The goal is to ensure that users can only access or modify data associated with their own user_id, as determined by the JWT issued via the /api/v3/security endpoint. When implementing RLS, I've come up with generalized steps below to implement on future tables.

Generalized steps to implement PostgREST and RLS:

1. Grant Base Permissions
   Grant web_user and/or web_admin table-level access to your table after enabling RLS. Include access to any sequences used for SERIAL/IDENTITY columns.

GRANT SELECT, INSERT, UPDATE, DELETE ON my_schema.my_table TO web_user;
GRANT USAGE, SELECT ON SEQUENCE my_schema.my_table_id_seq TO web_user;

2. Expose the Table via a View
   Expose the table through a view in the macrostrat_api schema for PostgREST access:

CREATE OR REPLACE VIEW macrostrat_api.my_table_view AS
SELECT * FROM my_schema.my_table;

Grant access to the view

GRANT SELECT, INSERT, UPDATE, DELETE ON macrostrat_api.my_table_view TO web_user;

Reload PostgREST schema

NOTIFY pgrst, 'reload schema';

3. Enable RLS

ALTER TABLE my_schema.my_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_my_table_web_user
ON my_schema.my_table
TO web_user
USING      (user_id = current_app_user_id())
WITH CHECK (user_id = current_app_user_id());

Next Steps for RLS architecture:

• Modularize PostgREST role and RLS logic into a shared library. See related implementation.
• Extend PostgREST + RLS support for web_admin by allowing web_admin full CRUD access to column-related tables.
• Investigate ways to associate user_id with existing db columns to support RLS in the future.
• Once the user_id is scoped, begin implementing RLS for web_user.

Projects Planned for RLS Integration:

• columns
• map_ingestion
• user_locations

[amyfromandi]

Update

We are going to inhibit bulk updates and deletes via postgREST to avoid accidental data loss or unintended modifications.

To do this we can implement pg_safe_update globally.
Or we can create trigger functions that disable users to bulk update/delete a specific table.

CREATE TRIGGER prevent_unsafe_update
BEFORE UPDATE ON user_features.user_locations
FOR EACH STATEMENT
WHEN (current_user IN ('web_user', 'web_admin'))
EXECUTE FUNCTION prevent_unsafe_update_delete();

CREATE TRIGGER prevent_unsafe_delete
BEFORE DELETE ON user_features.user_locations
FOR EACH STATEMENT
WHEN (current_user IN ('web_user', 'web_admin'))
EXECUTE FUNCTION prevent_unsafe_update_delete();

Also, added user_id to not default using the jwt. Instead, the frontend will need to GET api/v3/security/me with the Cookie as a header in order to parse the user_id.

[amyfromandi]

Latest updates to implement secure RLS for web_user. Working on web_admin privileges and restricting bulk updates with pg-safeupdate