Postgresql 8.4 DB Permission and FOR SHARE OF locking problem when inserting into table A that has foreign key to table B while user accessing A&B has only select rights to B

[Ryan D]

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

[Michael Bayer]

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.

[Ryan Dibble]

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

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.