Totally agree, especially with larger companies there are integrations to ERP, data ware houses, partners etc. and then it's often a good idea to have communicated lengths on your domain data (title, email, ...).

In case more than one service writes to the same table, either both should be under control of the same organization, or a stored procedure layer should be introduced for the external org (there will probably be more things to check that just the limit). The phrase "untrusted users" is concerning in GP's post.

For users of older postgres deployments (<9.2) it's worth noting that changing the limit on varchar is not free, it triggers a table rewrite.

With Postgres, you can have code running on the user's machine access the database directly, either via postgrest, or even via an old-fashioned database driver. This is not a problem because Postgres has a very fine-grained rights management and supports row-level security, preventing the end user from accessing any data they are not authorized to.

Of course, you mention the stored procedure layer that is required to make this work, so you're probably already aware of this. In that case, remember that you might not need views over literally every table in the database, and even in the case where you do use a view or stored procedure, it might still be much more convenient to do the length limit check on the table rather than cluttering every stored procedure that writes to the table with the same length limit check.