What is recommended way to grant permissions using peewee?

Question

As far as I know from documentation, peewee can create tables from given models, e.g.:

database = SqliteDatabase(DATABASE)

class BaseModel(Model):
    class Meta:
        database = database

class User(BaseModel):
    username = CharField(unique=True)

database.connect()
database.create_table(User, safe=True)

But what I haven't found yet, is recommended way to change permissions or grant access to the tables. I know that I can simply execute raw SQL like this:

database.execute_sql('GRANT ALL ON TABLE user TO ro_user;')

But I would like to know, whether there better/correct way to do that?

Answer 1

No, this is a fine way to do it. Peewee doesn't "natively" support creating GRANT statements. In fact, no ORM I've used does -- even SQLAlchemy which is very featureful recommends the raw SQL way of doing it. Part of the reason for this is that permissions handling and GRANT syntax varies widely between different databases.

So your use of Peewee's database.execute_sql() is just fine.

One thing to keep in mind is if you want to dynamically specify the username and it's coming from user input or a source that's not 100% trusted, you'll want to escape it properly (in PostgreSQL, as a double-quoted, delimited identifier) or ensure username.isidentifier() is True or something:

if not username.isidentifier():
    raise ValueError('bad username: {}'.format(username))
database.execute_sql('GRANT ALL ON TABLE user TO {};'.format(username))