Postgresql

The primary objects of interest are Role, and DefaultGrant (all other objects are support types which comprise these objects or else types which these objects produce).

Role

from sqlalchemy_declarative_extensions import Roles
from sqlalchemy_declarative_extensions.dialects.postgresql import Role

roles = Roles().are(Role('foo'), Role('bar', login=True), ...)

Define roles with Postgres-specific options

Grant

from sqlalchemy_declarative_extensions import Grants
from sqlalchemy_declarative_extensions.dialects.postgresql import DefaultGrant

grants = Grants().are(DefaultGrant.on_table_in_schema('public').grant('select', to='foo'), ...)

Define postgres grants. Note there is not currently a generic grant option, due to wild differences in details/semantics across dialects.

Note, DefaultGrant is most likely the intended object to make use of when declaring most grants. A vanilla Grant may encounter problems with the provenance of the objects it references (i.e. a defined Grant may be evaluated before the objects the grant references exist).

In comparison, a DefaultGrant generally implies the absolute set of grants one wants to exist in general and a DefaultGrant will, by default, project the implied grants against pre-existing objects and ensure they have the same sets of permissions.

Functions

There currently exists a generic Function object which contains all function behavior. Note that there do exist certain function definition options which are specific to Postgres, and should they be implemented you would be required to shift use to the dialect-specific version.

Additionally, not all function options are currently supported for Postgres. At current moment, only the options required to support the Audit Table feature have been implemented.

Triggers

from sqlalchemy_declarative_extensions import Triggers
from sqlalchemy_declarative_extensions.dialects.postgresql import Trigger

triggers = Triggers().are(
     Trigger.after("insert", on="foo", execute="gimme")
     .named("on_insert_foo")
     .when("pg_trigger_depth() < 1")
     .for_each_row()
     .with_arguments(["arg1", "arg2"]),
)

Trigger options and semantics differ across the different dialects that support them. In particular the TriggerTimes, TriggerForEach, and TriggerEvents options are all Postgres-specific.

Additionally, not all trigger options are currently supported for Postgres. At current moment, only the options required to support the Audit Table feature as well as basic support for arguments have been implemented.

API

class sqlalchemy_declarative_extensions.dialects.postgresql.role.Role

Define a role object.

postgres: https://www.postgresql.org/docs/current/sql-createrole.html

Note, the password field can be supplied, but it will be ignored when performing comparisons against existing roles. That is, changing the password field will not produce any (alembic) changes!

Note, a valid_until value of None implies that it should never expire, this translates into no net-change to the role if there is no expiration set already, however it translates to ‘infinity’ if there is an expiration being removed.

bypass_rls: bool | None = False

connection_limit: int | None = None

createdb: bool | None = False

createrole: bool | None = False

classmethod from_pg_role(r)

Return type:

Role

classmethod from_unknown_role(r)

Parameters:

r (sqlalchemy_declarative_extensions.role.generic.Role | Role)

Return type:

Role

inherit: bool | None = True

property is_dynamic: bool

Return type:

bool

login: bool | None = False

property options

password: sqlalchemy_declarative_extensions.role.generic.Env | str | None = None

replication: bool | None = False

superuser: bool | None = False

to_sql_create(raw=True)

Parameters:

raw (bool)

Return type:

list[str]

to_sql_drop(raw=True)

Parameters:

raw (bool)

Return type:

list[str]

to_sql_update(to_role, raw=True)

Parameters:

• to_role (Role)

• raw (bool)

Return type:

list[str]

to_sql_use(undo)

Parameters:

undo (bool)

Return type:

list[str]

valid_until: datetime.datetime | None = None

Abstract a postgresql GRANT statement.

See https://www.postgresql.org/docs/latest/sql-grant.html.

ALTER DEFAULT PRIVILEGES

[ FOR { ROLE | USER } target_role [, …] ] [ IN SCHEMA schema_name [, …] ] abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

[, …] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }

[, …] | ALL [ PRIVILEGES ] } ON SEQUENCES TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }

ON FUNCTIONS TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }

ON TYPES TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.DefaultGrant

for_role(role)

Parameters:

role (HasName | str)

grant(grant, *grants, to, grant_option=False)

Parameters:

• grant (str | sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G | Grant)

• grants (str | sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G)

• to (HasName | str)

grant_type: sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.DefaultGrantTypes

in_schemas: tuple[str, Ellipsis]

classmethod on_functions_in_schema(*in_schemas, for_role=None)

Parameters:

• in_schemas (str | HasName)

• for_role (HasName | str | None)

Return type:

DefaultGrant

classmethod on_sequences_in_schema(*in_schemas, for_role=None)

Parameters:

• in_schemas (str | HasName)

• for_role (HasName | str | None)

Return type:

DefaultGrant

classmethod on_tables_in_schema(*in_schemas, for_role=None)

Parameters:

• in_schemas (str | HasName)

• for_role (HasName | str | None)

Return type:

DefaultGrant

classmethod on_types_in_schema(*in_schemas, for_role=None)

Parameters:

• in_schemas (str | HasName)

• for_role (HasName | str | None)

Return type:

DefaultGrant

target_role: str | None = None

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.DefaultGrantStatement

Abstract base class for generic types.

A generic type is typically declared by inheriting from this class parameterized with one or more type variables. For example, a generic mapping type might be defined as:

class Mapping(Generic[KT, VT]):
    def __getitem__(self, key: KT) -> VT:
        ...
    # Etc.

This class can then be used as follows:

def lookup_name(mapping: Mapping[KT, VT], key: KT, default: VT) -> VT:
    try:
        return mapping[key]
    except KeyError:
        return default

classmethod combine(grants)

Parameters:

grants (list[DefaultGrantStatement])

default_grant: DefaultGrant

explode()

for_role(role)

Parameters:

role (str | HasName)

Return type:

DefaultGrantStatement

grant: Grant[sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G]

invert()

Return type:

DefaultGrantStatement

to_sql()

Return type:

sqlalchemy.sql.elements.TextClause

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.DefaultGrantTypes

Generic enumeration.

Derive from this class to define new enumerations.

classmethod from_relkind(relkind)

Parameters:

relkind (str)

function = 'FUNCTION'

sequence = 'SEQUENCE'

table = 'TABLE'

to_grant_type()

to_variants()

type = 'TYPE'

sqlalchemy_declarative_extensions.dialects.postgresql.grant.G

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.Grant

Abstract base class for generic types.

A generic type is typically declared by inheriting from this class parameterized with one or more type variables. For example, a generic mapping type might be defined as:

class Mapping(Generic[KT, VT]):
    def __getitem__(self, key: KT) -> VT:
        ...
    # Etc.

This class can then be used as follows:

def lookup_name(mapping: Mapping[KT, VT], key: KT, default: VT) -> VT:
    try:
        return mapping[key]
    except KeyError:
        return default

grant_option: bool = False

grants: tuple[str | sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G, Ellipsis]

classmethod new(grant, *grants, to, grant_option=False)

Parameters:

• grant (str | sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G)

• grants (str | sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G)

• to (str | HasName)

Return type:

Grant

on_objects(*objects, object_type)

Parameters:

• objects (str | HasName)

• object_type (sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.GrantTypes)

on_schemas(*schemas)

Parameters:

schemas (str | HasName)

on_sequences(*sequences)

Parameters:

sequences (str | HasName)

on_tables(*tables)

Parameters:

tables (str | HasName)

revoke()

Return type:

Grant

revoke_: bool = False

target_role: str

with_grant_option()

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.GrantOptions

Generic enumeration.

Derive from this class to define new enumerations.

default()

Return the default grants given by postgres for the current grant kind.

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

Return type:

List[sqlalchemy_declarative_extensions.dialects.from_string.FromStringsSelf]

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.GrantStatement

Abstract base class for generic types.

A generic type is typically declared by inheriting from this class parameterized with one or more type variables. For example, a generic mapping type might be defined as:

class Mapping(Generic[KT, VT]):
    def __getitem__(self, key: KT) -> VT:
        ...
    # Etc.

This class can then be used as follows:

def lookup_name(mapping: Mapping[KT, VT], key: KT, default: VT) -> VT:
    try:
        return mapping[key]
    except KeyError:
        return default

classmethod combine(grants)

Parameters:

grants (list[GrantStatement])

explode()

for_role(role)

Parameters:

role (str | HasName)

Return type:

GrantStatement

grant: Grant[sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G]

grant_type: sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.GrantTypes

invert()

Return type:

GrantStatement

targets: tuple[str, Ellipsis]

to_sql()

Return type:

sqlalchemy.sql.elements.TextClause

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.GrantTypes

Generic enumeration.

Derive from this class to define new enumerations.

database = 'DATABASE'

foreign_data_wrapper = 'FOREIGN DATA WRAPPER'

foreign_server = 'FOREIGN SERVER'

foreign_table = 'FOREIGN TABLE'

classmethod from_relkind(relkind)

Parameters:

relkind (str)

function = 'FUNCTION'

language = 'LANGUAGE'

large_object = 'LARGE OBJECT'

schema = 'SCHEMA'

sequence = 'SEQUENCE'

table = 'TABLE'

tablespace = 'TABLESPACE'

to_variants()

type = 'TYPE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant.HasName

Base class for protocol classes.

Protocol classes are defined as:

class Proto(Protocol):
    def meth(self) -> int:
        ...

Such classes are primarily used with static type checkers that recognize structural subtyping (static duck-typing), for example:

class C:
    def meth(self) -> int:
        return 0

def func(x: Proto) -> int:
    return x.meth()

func(C())  # Passes static type check

See PEP 544 for details. Protocol classes decorated with @typing.runtime_checkable act as simple-minded runtime protocols that check only the presence of given attributes, ignoring their type signatures. Protocol classes can be generic, they are defined as:

class GenProto(Protocol[T]):
    def meth(self) -> T:
        ...

name: str

sqlalchemy_declarative_extensions.dialects.postgresql.grant.split_schema(tablename, *, schema=None)

Parameters:

• tablename (str)

• schema (str | None)

Return type:

tuple[str | None, str]

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.DatabaseGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

connect = 'CONNECT'

create = 'CREATE'

default()

Return the default grants given by postgres for the current grant kind.

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

temporary = 'TEMPORARY'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.DefaultGrantTypes

Generic enumeration.

Derive from this class to define new enumerations.

classmethod from_relkind(relkind)

Parameters:

relkind (str)

function = 'FUNCTION'

sequence = 'SEQUENCE'

table = 'TABLE'

to_grant_type()

to_variants()

type = 'TYPE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.ForeignDataWrapperGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

usage = 'USAGE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.ForeignServerGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

usage = 'USAGE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.ForeignTableGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

select = 'SELECT'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.FromStrings

Generic enumeration.

Derive from this class to define new enumerations.

classmethod from_string(string)

Parameters:

string (Union[str, FromStringsSelf])

Return type:

FromStringsSelf

classmethod from_strings(strings)

Parameters:

strings (Iterable[Union[str, FromStringsSelf]])

Return type:

List[FromStringsSelf]

sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.FromStringsSelf

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.FunctionGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

default()

Return the default grants given by postgres for the current grant kind.

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

execute = 'EXECUTE'

sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.G

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.GrantOptions

Generic enumeration.

Derive from this class to define new enumerations.

default()

Return the default grants given by postgres for the current grant kind.

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

Return type:

List[sqlalchemy_declarative_extensions.dialects.from_string.FromStringsSelf]

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.GrantTypes

Generic enumeration.

Derive from this class to define new enumerations.

database = 'DATABASE'

foreign_data_wrapper = 'FOREIGN DATA WRAPPER'

foreign_server = 'FOREIGN SERVER'

foreign_table = 'FOREIGN TABLE'

classmethod from_relkind(relkind)

Parameters:

relkind (str)

function = 'FUNCTION'

language = 'LANGUAGE'

large_object = 'LARGE OBJECT'

schema = 'SCHEMA'

sequence = 'SEQUENCE'

table = 'TABLE'

tablespace = 'TABLESPACE'

to_variants()

type = 'TYPE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.LanguageGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

default()

Return the default grants given by postgres for the current grant kind.

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

usage = 'USAGE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.LargeObjectGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

select = 'SELECT'

update = 'UPDATE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.SchemaGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

create = 'CREATE'

usage = 'USAGE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.SequenceGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

select = 'SELECT'

update = 'UPDATE'

usage = 'USAGE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.TableGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

delete = 'DELETE'

insert = 'INSERT'

references = 'REFERENCES'

select = 'SELECT'

trigger = 'TRIGGER'

truncate = 'TRUNCATE'

update = 'UPDATE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.TablespaceGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

create = 'CREATE'

class sqlalchemy_declarative_extensions.dialects.postgresql.grant_type.TypeGrants

Generic enumeration.

Derive from this class to define new enumerations.

classmethod acl_symbols()

default()

Return the default grants given by postgres for the current grant kind.

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

usage = 'USAGE'

class sqlalchemy_declarative_extensions.dialects.postgresql.Trigger

Describes a PostgreSQL trigger.

Some trigger options are not currently supported.

classmethod after(*events, on, execute, name='')

Parameters:

• events (TriggerEvents | str)

• on (str)

• execute (str)

• name (str)

arguments: tuple[str, Ellipsis] | None = None

classmethod before(*events, on, execute, name='')

Parameters:

• events (TriggerEvents | str)

• on (str)

• execute (str)

• name (str)

condition: str | None = None

events: list[TriggerEvents]

for_each: TriggerForEach

for_each_row()

for_each_statement()

property identity: tuple[str, str]

Return the dialect-specific identity used to compare triggers.

Return type:

tuple[str, str]

classmethod instead_of(*events, on, execute, name='')

Parameters:

• events (TriggerEvents | str)

• on (str)

• execute (str)

• name (str)

new_table: str | None = None

old_table: str | None = None

referencing_new_table_as(table_name)

Parameters:

table_name (str)

referencing_old_table_as(table_name)

Parameters:

table_name (str)

time: TriggerTimes

to_sql_create(replace=False)

Return a trigger CREATE statement.

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR … ] } ON table_name [ FROM referenced_table_name ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ … ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

to_sql_drop()

to_sql_update(connection=None)

Parameters:

connection (sqlalchemy.engine.Connection | None)

when(condition)

Parameters:

condition (str)

with_arguments(*arguments)

Parameters:

arguments (str)

class sqlalchemy_declarative_extensions.dialects.postgresql.TriggerEvents

Generic enumeration.

Derive from this class to define new enumerations.

delete = 'DELETE'

classmethod from_bit_string(bitstring)

Parse a postgres “tgtype” bit string into a concrete variant.

Per https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_trigger.h

Parameters:

bitstring (str)

Return type:

list[TriggerEvents]

insert = 'INSERT'

truncate = 'TRUNCATE'

update = 'UPDATE'

class sqlalchemy_declarative_extensions.dialects.postgresql.TriggerForEach

Generic enumeration.

Derive from this class to define new enumerations.

classmethod from_bit_string(bitstring)

Parse a postgres “tgtype” bit string into a concrete variant.

Per https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_trigger.h

Parameters:

bitstring (str)

row = 'ROW'

statement = 'STATEMENT'

class sqlalchemy_declarative_extensions.dialects.postgresql.TriggerTimes

Generic enumeration.

Derive from this class to define new enumerations.

after = 'AFTER'

before = 'BEFORE'

classmethod from_bit_string(bitstring)

Parse a postgres “tgtype” bit string into a concrete variant.

Per https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_trigger.h

Parameters:

bitstring (str)

instead_of = 'INSTEAD OF'