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

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 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 = False#

connection_limit#

createdb = False#

createrole = False#

classmethod from_pg_role(r)#

Return type: Role

classmethod from_unknown_role(r)#

Parameters: r (generic.Role | Role) –
Return type: Role

inherit = True#

login = False#

property options#

password#

replication = False#

superuser = False#

to_sql_create()#

Return type: str

to_sql_update(to_role)#

Parameters: to_role (Role) –
Return type: list[str]

valid_until#

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

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

Parameters

grant (str | G | Grant) –
grants (str | G) –

grant_type#

in_schemas#

classmethod on_functions_in_schema(*in_schemas, for_role=None)#

Parameters

in_schemas (str | HasName) –
for_role (HasName | None) –

Return type

DefaultGrant

classmethod on_sequences_in_schema(*in_schemas, for_role=None)#

Parameters

in_schemas (str | HasName) –
for_role (HasName | None) –

Return type

DefaultGrant

classmethod on_tables_in_schema(*in_schemas, for_role=None)#

Parameters

in_schemas (str | HasName) –
for_role (HasName | None) –

Return type

DefaultGrant

classmethod on_types_in_schema(*in_schemas, for_role=None)#

Parameters

in_schemas (str | HasName) –
for_role (HasName | None) –

Return type

DefaultGrant

target_role#

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#

explode()#

for_role(role)#

Parameters: role (str | HasName) –
Return type: DefaultGrantStatement

grant#

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 = False#

grants#

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

Parameters

grant (str | G) –
grants (str | 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_ = False#

target_role#

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_type#

invert()#

Return type: GrantStatement

targets#

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#

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

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

Parameters

events (TriggerEvents | str) –
on (str) –
execute (str) –
name (str) –

condition#

events#

for_each#

for_each_row()#

for_each_statement()#

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

Parameters

events (TriggerEvents | str) –
on (str) –
execute (str) –
name (str) –

time#

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 )

when(condition)#

Parameters: condition (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#