feature request: add SECURITY DEFINER to the postgresql Function

[veledzimovich-iTechArt]

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
        $$;
        """
)

[DanCardin]

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()

[DanCardin]

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.

[veledzimovich-iTechArt]

Thank you.

Everything generated correctly.

1. 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 ###

2. 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 ###

3. 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.