Huh, FD's take away was different from mine. My impression is that there's two use cases. One for the primary system and one for plugin usage.

The way I understood it is that there are a set of tables, which we'll call CORE, that the system will need to read from and write to. At the same times, there are plugins to the system that also need tables in the database. But he doesn't want these plugins to be able to write to these CORE tables, only read from them. But they do need to be able to write to other tables that belong to the plugin.

Possible solution would be to have two users: one for the system itself, and one for use by the plugin. Then, don't let the plugins initiate the connection directly. Have the plugin request a connection from the application, which would then return a connection using the plugin user. The plugin user would then only have read permissions to the CORE tables, while having R/W permissions to the other tables.

As long as any given transaction is under either any ONE of those two connections, it should work. But as FD noted, a transaction can't be split across two user connections. (there's actually a caveat to this, but doesn't apply in this case).

-tg