Prevent writes to specific tables (read-only tables)

Question

Is there a way to mark tables as read-only when using Pyramid with SQLAlchemy?

Some brief background: I am working on an app with an existing database which is very complicated. My app adds a few new tables to this database, but updating (update, insert, delete) other tables is unsafe because the schema is not well understood (and isn't documented).

My basic aim is to fail-fast before any unsafe db updates so the offending code can be found and removed, so I and future developers don't cause any data issues with this app.

My naive approach: add a pyramid_tm commit veto hook that checks objects to be updated (new, dirty, and deleted in the db session) and vetos if there are any read-only tables.

Is there an existing mechanism to handle this? Or a better approach?

Answer 1

If you're using the "declarative" approach, you can try making the classes themselves "read-only":

class MyBase(object):
    """
    This class is a superclass of SA-generated Base class,
    which in turn is the superclass of all db-aware classes
    so we can define common functions here
    """

    def __setattr__(self, name, value):
        """
        Raise an exception if attempting to assign to an atribute of a "read-only" object
        Transient attributes need to be prefixed with "_t_"
        """
        if (getattr(self, '__read_only__', False)
          and name != "_sa_instance_state"
          and not name.startswith("_t_")):
            raise ValueError("Trying to assign to %s of a read-only object %s" % (name, self))
        super(MyBase, self).__setattr__(name, value)



Base = declarative_base(cls=MyBase)

Similarly you can try to override __init__ method to prevent the objects from being instantiated.

However, solving this problem at the application level feels a bit fragile - I would just look at creating a special user in the database for your app to connect with, which would save limited permissions on the tables you want to keep intact.