DanCardin
feature request: add SECURITY DEFINER to the postgresql Function
It would be nice to have the possibility to provide the SECURITY DEFINER directive as a field to the sqlalchemy_declarative_extensions.dialects.postgresql.Function, along with language or returns fields.
Because right now, we have to add it manually in our migration scripts.
op.execute(
"""
CREATE FUNCTION my_view()
RETURNS void
SECURITY DEFINER -- execute with the privileges of the owner [MANUALLY ADDED DIRECTIVE]
LANGUAGE plpgsql AS $$
BEGIN
REFRESH MATERIALIZED VIEW my_view WITH DATA;
END
$$;
"""
)
language and returns are both already available, unless i'm missing a particular aspect of your question.
security definer at least seems straightforward to add. almost certainly security=FunctionSecurity.defineer and/or function.security_definer()
I'd appreciate it if you could test the PR out ahead of my merging it, to make sure it does what you're anticipating it do.
Thank you.
Everything generated correctly.
- I checked the case when the function was created for the first time.
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.execute(
"""CREATE FUNCTION refresh_my_view() RETURNS void SECURITY DEFINER LANGUAGE plpgsql AS $$
BEGIN
REFRESH MATERIALIZED VIEW my_view WITH DATA;
END
$$;"""
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.execute("""DROP FUNCTION refresh_my_view();""")
# ### end Alembic commands ###
- I checked the case when I had the function 'refresh_my_view' without SECURITY DEFINER. I added FunctionSecurity.definer to the existing function and ran
alembic revision --autogenerate
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.execute(
"""CREATE OR REPLACE FUNCTION refresh_my_view() RETURNS void SECURITY DEFINER LANGUAGE plpgsql AS $$
BEGIN
REFRESH MATERIALIZED VIEW my_view WITH DATA;
END
$$;"""
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.execute(
"""CREATE OR REPLACE FUNCTION refresh_my_view() RETURNS void LANGUAGE plpgsql AS $$
BEGIN
REFRESH MATERIALIZED VIEW my_view WITH DATA;
END
$$;"""
)
# ### end Alembic commands ###
- I checked the case when I had the function 'refresh_my_view' with SECURITY DEFINER. I removed FunctionSecurity.definer from the existing function and ran
alembic revision check. As expected, I received an error.