Thanks for looking into the SA codebase. I've continued debugging the issue since I posted the original post. I think I misread the exception and made an assumption that the statement was generated within SA, but after further debugging I concur. It's not begin generated by SQLAlchemy at all. It is some type of behavior within postgresql itself likely because of the multiple permissions. (I found a old post on the net about postgresql 7.3 which gave me the lead.) Since the post, I've been able to create a situation where it occurs solely in psql with an insert query. In my case, when the pg role public is missing the update on that table (even if the actual user has it).
In the event anyone else ever has this problem, we've also constructed the following work around: after setting up everything like before, we grant our public role update on the table's ID column only (since we have cascade off trying to change this doesn't succeed anyway in most cases because of the foreign key references) and then attached a psql trigger function to the tables that suppresses the actual updates to the data and instead raise pg warnings (because inserting/updating/deleting that table should never happen in the production system by that user). Thanks for taking time to read my post, -- Ryan On Thu, Jun 7, 2012 at 8:34 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Jun 7, 2012, at 5:59 PM, Ryan D wrote: > > > Question: > > > > Generally, How does one tell SQLAlchemy that a table is read-only (via > > DB permissions) so it doesn't try to do things to it that table > > implicitly that require more then select permission? > > SQLAlchemy does not do INSERT/UPDATE/DELETE unless instructed to, nor does > it use any kind of locking hints without specific instructions. > > > > > Or asked another way, How does one tell SQLAlchemy that a first table > > (say States) is read only because of DB level permissions, so that > > SQLAlchemy does not emit "...FOR SHARE..." locking on that table > > (States) when it is inserting data into a different table (say Users) > > that has a foreign key reference to the first read only table > > (States)? > > SQLAlchemy doesn't emit FOR SHARE unless specifically instructed to, and > not at all in the way that your SQL excerpt illustrates without hand-coding > that exact SQL string somewhere. > > For this query I see in your code: > > > "SELECT 1 FROM ONLY "public"."states" x WHERE > > "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x" > > that is not at all anything SQLAlchemy creates by itself. The syntax "FOR > SHARE OF" is not even part of the codebase, SQLA's "FOR SHARE" clause comes > out after the "SELECT" keyword, not in the WHRE clause, and does not > include "OF". The PG dialect also uses the "AS" keyword when it names the > alias of a table, such as "x" here, so this seems like a hand-coded SQL > statement embedded in your application or some library other than > SQLAlchemy. > > > > > > How do I tell SA that that the states table is read-only, OR make it > > shop trying to automatically lock states on insert into users, OR > > suppress the FOR SHARE locking on specific tables, OR do anything else > > that will allow the insert to run as expected with raw sql and w/o > > update permission on the states table, OR any combination of these > > things? > > I see nothing in the code excerpts you've given that would instruct > SQLAlchemy to emit such SQL in any way. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
