With Row Level security (RLS) you manage the access control at the row level within a database instead of the application. Row-Level Security allows you to define policies that determine which rows of data a particular user or role can access within a given table.
Postgres Tables For this demonstration we create a simple setup with a User table and a Item table using SQLAlchemy 2.0:
from sqlalchemy import Column, ForeignKey, Integer, String, create_engine, text from sqlalchemy.orm import declarative_base, relationship admin_engine = create_engine("postgresql://postgres:postgres@0.0.0.0:5432/postgres") Base = declarative_base() class Item(Base): __tablename__ = "items" id = Column(Integer, primary_key=True) name = Column(String) user_id = Column(Integer, ForeignKey("users.id")) user = relationship("User", back_populates="item_entries") class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) username = Column(String) password = Column(String) item_entries = relationship("Item", back_populates="user") Base.metadata.create_all(admin_engine) Using the PostgreSQL superuser for application access is not a great idea due to its extensive privileges and security risks. It’s advisable to create a dedicated user with limited permissions tailored to the application’s requirements for improved security and operational control.
...