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?
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)?
Details:
I have a apache/python/pylons/sqlalchemy/ webapp system with many
tables with differing levels of permission on them across many users.
There are a number of table that are readonly to all users or to
certain users because of DB permissions. When certain SA operations
are run on tables that has the readonly tables as foreign keys bad
things happen. This is a simplified example to illustrate the problem.
(Please forgive syntax related typos that may be present below.)
Using Postgresql 8.4 DBMS, make a new db called "test", in the
"public" schema, create some tables, say:
(A) table "Users" with fields: user_id (primary key), user_name,
state_id (foreign key to state table state_id column)
(B) table "States" with fields: state_id (primary key), state_name
-- populate the States table with the 50 states via insert queries (or
just do 2 the number of states doesn't effect the outcome)
-- create some group roles (not login roles)
CREATE ROLE ro_owner NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
CREATE ROLE rw_owner NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
CREATE ROLE webuser LOGIN
PASSWORD 'password'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
-- Change owners and lock down the DB to prevent certain DB level
users from editing things they shouldn't (rw_user and ro_user are
group roles (not pg login roles)):
ALTER TABLE Users OWNER TO rw_owner;
REVOKE ALL on Users from webuser;
REVOKE ALL on Users from public;
GRANT ALL on Users to webuser;
ALTER TABLE States OWNER TO ro_owner;
REVOKE ALL on States from webuser;
REVOKE ALL on States from public;
GRANT SELECT on States to webuser;
-- create sqlalchemy Tables and Class for the above tables called
users_table, states_table, Users, and States respectively
user_table = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(255), nullable = False),
Column('state_id', Integer, ForeignKey('states.id'), nullable =
False)
)
class Users(Base):
pass
state_table = Table('states', metadata,
Column('state_id', Integer, primary_key=True),
Column('state_name', Unicode(255), nullable = False),
)
class State(Base):
pass
-- map them
mapper(User, user_table, properties = {
'state' : relation(State, primaryjoin =
(user_table.c.state_id == state_table.c.state_id), uselist = False)
})
mapper(State, state_table)
-- connect to the DB as the user using psql and test the permissions,
things will work as expected
select * from users;
select * from states;
INSERT INTO users (user_id,user_name,state_id) VALUES (1,'test by
hand',20);
-- application users SQLAlchemy and connects as the test db with the
postgresql login role "webuser"
now when application performs an insert using syntax below (or any
insert method for that matter):
mystmt = users_table.insert()
mystmt.execute(user_id=123,user_name='test number
1',state_id=10)
When this happens in our environment using various versions of
sqlalchmy including our primary version 0.7.3, we will get an
exception in our app something like:
ProgrammingError: (ProgrammingError) permission denied for relation
states
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."states" x WHERE
"id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
'INSERT INTO users (user_id, user_name, state_id) VALUES (%
(user_id)s, %(user_name)s, %(state_id)s) RETURNING
users.user_id' {'state_id': 100, , 'user_name': 'test number 1',
'user_id': 123}
The permission denied is unexpected because the statement shouldn't be
updating the states table only the users table. However even though
webuser has select rights on the states table, postgresql requires
update rights (which cannot be given for security reasons) for the FOR
SHARE OF locking.
I've explored with_lock on various queries and non_primary on the
mapper without success.
Also adding an extra column to the effected tables called fake_data
and granting update rights to only that column to webuser (which would
never be used in the application) while leaving all other columns read
only in order to have a grant of update on the table in order to get
FOR SHARE OF locking has been rejected as a viable solution.
So,
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?
All the best,
Ryan
--
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.
