VhddlmZmZmZeZdZdZdZddl m Z ddl m Z ddl mZmZddlmZmZmZmZmZmZmZmZd Zd Zd Zd Zd ZdZGddeZ GddZ!dZ"e#dk(re"yy))absolute_importdivisionprint_functiona --- module: postgresql_publication short_description: Add, update, or remove PostgreSQL publication description: - Add, update, or remove PostgreSQL publication. options: name: description: - Name of the publication to add, update, or remove. required: true type: str login_db: description: - Name of the database to connect to and where the publication state will be changed. - The V(db) alias is deprecated and will be removed in version 5.0.0. aliases: [ db ] type: str columns: description: - List of tables and its columns to add to the publication. - If no columns are passed for table, it will be published as a whole. - Mutually exclusive with I(tables) and I(tables_in_schema). type: dict version_added: '3.8.0' rowfilters: description: - Optional dictionary of row filters to apply to I(tables) or I(columns) of the publication. - Mutually exclusive with I(tables_in_schema). type: dict version_added: '3.12.0' tables: description: - List of tables to add to the publication. - If no value is set all tables are targeted. - If the publication already exists for specific tables and I(tables) is not passed, nothing will be changed. - If you need to add all tables to the publication with the same name, drop existent and create new without passing I(tables). - Mutually exclusive with I(tables_in_schema) and I(columns). type: list elements: str tables_in_schema: description: - Specifies a list of schemas to add to the publication to replicate changes for all tables in those schemas. - If you want to remove all schemas, explicitly pass an empty list C([]). - Supported since PostgreSQL 15. - Mutually exclusive with I(tables) and I(columns). type: list elements: str version_added: '3.5.0' state: description: - The publication state. default: present choices: [ absent, present ] type: str parameters: description: - Dictionary with optional publication parameters. - Available parameters depend on PostgreSQL version. type: dict owner: description: - Publication owner. - If I(owner) is not defined, the owner will be set as I(login_user) or I(session_role). type: str cascade: description: - Drop publication dependencies. Has effect with I(state=absent) only. type: bool default: false session_role: description: - Switch to session_role after connecting. The specified session_role must be a role that the current login_user is a member of. - Permissions checking for SQL commands is carried out as though the session_role were the one that had logged in originally. type: str version_added: '0.2.0' trust_input: description: - If C(false), check whether values of parameters I(name), I(tables), I(owner), I(session_role), I(params) are potentially dangerous. - It makes sense to use C(false) only when SQL injections via the parameters are possible. type: bool default: true version_added: '0.2.0' comment: description: - Sets a comment on the publication. - To reset the comment, pass an empty string. type: str version_added: '3.3.0' notes: - PostgreSQL version must be 10 or greater. attributes: check_mode: support: full seealso: - name: CREATE PUBLICATION reference description: Complete reference of the CREATE PUBLICATION command documentation. link: https://www.postgresql.org/docs/current/sql-createpublication.html - name: ALTER PUBLICATION reference description: Complete reference of the ALTER PUBLICATION command documentation. link: https://www.postgresql.org/docs/current/sql-alterpublication.html - name: DROP PUBLICATION reference description: Complete reference of the DROP PUBLICATION command documentation. link: https://www.postgresql.org/docs/current/sql-droppublication.html author: - Loic Blot (@nerzhul) - Andrew Klychkov (@Andersson007) - George Spanos (@grantanplan) extends_documentation_fragment: - community.postgresql.postgres a - name: Create a new publication with name "acme" targeting all tables in database "test" community.postgresql.postgresql_publication: login_db: test name: acme comment: Made by Ansible - name: Create publication "acme" publishing only prices and vehicles tables community.postgresql.postgresql_publication: name: acme tables: - prices - vehicles - name: Create publication "acme" publishing only prices table and id and name from vehicles tables community.postgresql.postgresql_publication: name: acme columns: prices: vehicles: - id - name - name: Create publication "acme" publishing id and name from vehicles tables, with a row filter community.postgresql.postgresql_publication: name: acme columns: vehicles: - id - name rowfilters: vehicles: (id > 100) - name: > Assuming publication "acme" exists, publishing id and name from vehicles table with a row filter (id > 100), remove and re-add the table to the publication, with the updated row filter community.postgresql.postgresql_publication: name: acme columns: vehicles: - id - name rowfilters: vehicles: WHERE (id > 100) AND (id < 200) - name: Create a new publication "acme" for tables in schema "myschema" community.postgresql.postgresql_publication: login_db: test name: acme tables_in_schema: myschema - name: Remove all schemas from "acme" publication community.postgresql.postgresql_publication: login_db: test name: acme tables_in_schema: [] - name: > Create publication "acme", set user alice as an owner, targeting all tables Allowable DML operations are INSERT and UPDATE only community.postgresql.postgresql_publication: name: acme owner: alice parameters: publish: 'insert,update' - name: > Assuming publication "acme" exists and there are targeted tables "prices" and "vehicles", add table "stores" to the publication community.postgresql.postgresql_publication: name: acme tables: - prices - vehicles - stores - name: Remove publication "acme" if exists in database "test" community.postgresql.postgresql_publication: login_db: test name: acme state: absent a exists: description: - Flag indicates the publication exists or not at the end of runtime. returned: success type: bool sample: true queries: description: List of executed queries. returned: success type: str sample: [ 'DROP PUBLICATION "acme" CASCADE' ] owner: description: Owner of the publication at the end of runtime. returned: if publication exists type: str sample: "alice" tables: description: - List of tables in the publication at the end of runtime. - If all tables are published, returns empty list. returned: if publication exists type: list sample: ["\"public\".\"prices\"", "\"public\".\"vehicles\""] alltables: description: - Flag indicates that all tables are published. returned: if publication exists type: bool sample: false parameters: description: Publication parameters at the end of runtime. returned: if publication exists type: dict sample: {'publish': {'insert': false, 'delete': false, 'update': true}} ) AnsibleModule) iteritems) check_inputpg_quote_identifier) connect_to_dbensure_required_libsexec_sqlget_conn_paramsget_server_versionpg_cursor_argspostgres_common_argument_spec set_commenti'cxd|vrtd|jzdSt|jdS)zAdd 'public.' to name of table where a schema identifier is absent and add quotes to each element. Args: table (str): Table name. Returns: str: Normalized table name. .z public.%stable)r strip)rs /home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/postgresql/plugins/modules/postgresql_publication.pynormalize_table_namers7 %";#>HH"5;;='::cHt|D]\}}t|||<|S)zAdd 'public.' to names of tables where a schema identifier is absent and add quotes to each element. Args: tbl_list (list): List of table names. Returns: tbl_list (list): Changed list. ) enumerater)tbl_listirs rtransform_tables_representationr s1h'25*51 2 Orchi}|D]*}||rtd||Dnd|t|<,|S)zAdd quotes to each element of the columns list. Args: columns (dict): Dict with tables and columns. Returns: columns (dict): Changed dict. c3<K|]}|jywN)r).0cs r z)transform_columns_keys..;s9\!'')9\sN)setr)columnsrevmap_columnsrs rtransform_columns_keysr'0sMNy`ghm`nc9\WUZ^9\6\tx+E23y rci}t|D]X\}}|j}|s|ddjdk(r|ddj}t||t |<Z|S)a2Add quotes to each element of the rowfilters list and removes any "WHERE" clauses from the filter, since it's not retained in the publication `rowfilter` column Args: rowfilters (dict): Dict with tables and row filter conditions. Returns: rowfilters (dict): Changed dict. Nwhere)rrlower RowFilterr) rowfiltersrevmap_filtersrfltrs rtransform_rowfilters_keysr0@stN ,J tzz| BQx~~7*ABx~~':CD/N/6 7 J rct|}|s|S|Dcgc]}t|d}}|ddj|d}|Scc}w)zConvert a list of columns to a string. Args: table (str): Table name. columns (list): List of columns. Returns: str: String with columns. columnz (, ))rr join)rr%colquoted_columns quoted_sqls rpg_quote_column_listr9UsP ! 'E  DKLS)#x8LNL#TYY~%>?J MsAc"eZdZdZdZdZdZy)r,aRepresents a row filter `WHERE` clause on a particular `table` We overload the `==` and `!=` operators so that when comparing: 1. any possible quoting on columns is not considered 2. whitespace is not considered This makes possible to identify identical row filters and not perform any changes, regardless of insignificant (for Postgres) syntactic differences. e.g. '( id > 10)' will be equivalent to '("id" > 10)' c||_yr )rfilter)selfr<s r__init__zRowFilter.__init__us  rc|jjddjdd|jjddjddk(S)N" )r<replacer=others r__eq__zRowFilter.__eq__xsM $$S"-55c2>%--BWBWX[]_B`BhBhilnpBqqrrc&|j| Sr )rFrDs r__ne__zRowFilter.__ne__{s;;u%%%rN)__name__ __module__ __qualname____doc__r>rFrHrrr,r,is s&rr,ceZdZdZdZdZdZddZddZddZ dZ d Z d Z d Z d Zd ZddZddZddZddZddZddZddZddZddZddZy) PgPublicationaClass to work with PostgreSQL publication. Args: module (AnsibleModule): Object of AnsibleModule class. cursor (cursor): Cursor object of psycopg library to work with PostgreSQL. name (str): The name of the publication. Attributes: module (AnsibleModule): Object of AnsibleModule class. cursor (cursor): Cursor object of psycopg library to work with PostgreSQL. name (str): Name of the publication. executed_queries (list): List of executed queries. attrs (dict): Dict with publication attributes. exists (bool): Flag indicates the publication exists or not. c||_||_||_||_g|_dgidgiid|_|j |_y)NFrA) alltablestables parametersownerschemasr%r-)modulecursorname pg_srv_verexecuted_queriesattrs check_pubexists)r=rVrWrXrYs rr>zPgPublication.__init__sT   $ "  nn& rcD|j|_|jS)z[Refresh the publication information. Returns: ``self.attrs``. )r\r]r[)r=s rget_infozPgPublication.get_infos nn& zzrcj|j}|sy|jd|jd<|jd|jdnd|jd<i|jdd<|jdd|jddd <|jd d|jddd <|jd d|jddd <|jdr$|jd|jddd<|jds|j}t |D]\}}t |dd||<||jd<|j dk\r|j|jd<|j}i}|D]}t|d|t|d<!||jd<|j}i} |D]}t|d| t|d<!| |jd<yd|jd<y)zCheck the publication and refresh ``self.attrs`` publication attribute. Returns: True if the publication with ``self.name`` exists, False otherwise. FpubownerrTcommentrArSpublish pubinsertinsert pubupdateupdate pubdeletedelete pubtruncatetruncate puballtablesschema_dot_tablerrRIrUr% rowfilterr-TrQ) $_PgPublication__get_general_pub_infogetr[#_PgPublication__get_tables_pub_inforr rY#_PgPublication__get_schema_pub_info$_PgPublication__get_columns_pub_infor$r'_PgPublication__get_rowfilters_pub_infor,) r=pub_info table_inforschema_and_table column_infor%row filters_infofilterss rr\zPgPublication.check_pubsJ..0&ll:6 7;C<< ;R;^ Y 7df 9/1 < +8@ [RW8X < +H58@ [RW8X < +H58@ [RW8X < +H5 << &>Fll=>YDJJ| $Y / ;||N+335J'0'< c## 34DEW4XZa b 1  c$.DJJx &((,(B(B(D 9%"99; &aCMPQTU^Q_M`G05G1HIJa(/ 9%#==? 'iCMVWZ[fWgMhG05G1HIJi+2 <( '+DJJ{ #rc d} dt|jdzg} |r\g} |D]1} t| || } | |vr | d|| zz } | j| 3| jddj | zn|rYg} |D].} t| d}| |vr |d|| zz }| j|0| jddj | znP|r=|Dcgc]}t|d}}| jd dj |zn| jd |rOg}t |D]\}}|j|d |d | jd dj |z|j dj | |} |r|j|||.t|j|d|j||j| Scc}w)aCreate the publication. Args: tables (list): List with names of the tables that need to be added to the publication. tables_in_schema (list): List of schema names of the tables that need to be added to the publication. params (dict): Dict contains optional publication parameters and their values. owner (str): Name of the publication owner. comment (str): Comment on the publication. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: changed (bool): True if publication has been created, otherwise False. TzCREATE PUBLICATION %s publication WHERE %sz FOR TABLE %sr3rschemazFOR TABLES IN SCHEMA %szFOR ALL TABLES = ''z WITH (%s)rB check_mode) r rXr9appendr5r_PgPublication__exec_sql_PgPublication__pub_set_ownerrrWrZ)r=rRtables_in_schemar%r-paramsrTrbrchangedquery_fragments table_stringsr quoted_colstbl_strr params_listkeyvals rcreatezPgPublication.creates"25HTa5bbc M  225'%.I J&K*U2C$CDK$$[1  2  " ">DIIm4L#L M M .-eW=J&{Z->>>G$$W-  .  " ">DIIm4L#L M Tde& 3FH Ee e  " "#    Wm :t/D/D F3 fsG c d} |rF|jds6d} |D]} | |jdvr|| s+|j| } | |jd| k7sAd} nj|jd| || k7rd} nN|| |jd| k(sz| |vs| |jdvs|| |jd| k7sd} n| r|j|||} n|D],} | |jdvs|j| || ||} .|jdD](} | |j vs|j | |} *n%|r#|jdr|j|||} |r|jds|D]} | |jdvr|j | ||} )| |vr|| |jd| k7s| |jdvsW| |vs\|j | |} | sr|j | ||} |jdD]} | |vs|j | |} n|r$|jdr|j|||} nZ|X|D]'}||jdvs|j||} )|jdD]}||vs|j||} |rt|D]\}}|jd j|r|d k(rr|jd d j}|jd }|D]}||vrd||< d||<||jd d k7s|j|||} |jd ||k7s|j|||} |j|||} |r%||jd k7r|j||} |@||jd k7r.t!|j"|d|j$||j&} | S)aUpdate the publication. Args: tables (list): List with names of the tables that need to be presented in the publication. tables_in_schema (list): List of schema names of the tables that need to be presented in the publication. params (dict): Dict contains optional publication parameters and their values. owner (str): Name of the publication owner. comment (str): Comment on the publication. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: changed (bool): True if publication has been updated, otherwise False. FrQrRr%Tr-rrUrSrc,rTrbr~)r[!_PgPublication__get_table_columns_PgPublication__pub_set_columns_PgPublication__pub_add_columnskeys_PgPublication__pub_drop_table_PgPublication__pub_add_table_PgPublication__pub_set_tables_PgPublication__pub_add_schema_PgPublication__pub_drop_schemarrqcopysplit_PgPublication__pub_set_paramrrrWrXrZ)r=rRrr%r-rrTrbrrneed_set_columnsr all_columnstblrrrval_dictval_listvs rrgzPgPublication.updates" 4::k2$    8 44 "&":":5"AK"djj&;E&BB+/(ZZ *51WU^C'+$U^tzz)'!)4A H}.2 .3 4$tzz,'? 'JJ&*&:&:3PZ&:&[GL1#6#="&"6"6sCJ"6"W#223 2SG9 T> Udjj11**5Z*HG  7djj.C#C!$++w "&))Z9N9NPGrc|jr_g}|jdt|jdz|r|jd|j dj ||Sy)aDrop the publication. Kwargs: cascade (bool): Flag indicates that publication needs to be deleted with its dependencies. check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: changed (bool): True if publication has been updated, otherwise False. zDROP PUBLICATION %sr~CASCADErBrN)r]rr rXrr5)r=cascaderrs rdropzPgPublication.dropsc ;; O  " "#8;NtyyZg;h#h i&&y1??388O#<?T T rcrt|dd}|rd}nd}t||d|jid}|r|dSy) zGet and return general publication information. Returns: Dict with publication information if successful, False otherwise. zlSELECT 1 FROM information_schema.columns WHERE table_name = 'pg_publication' AND column_name = 'pubtruncate'F)add_to_executedaSELECT obj_description(p.oid, 'pg_publication') AS comment, r.rolname AS pubowner, p.puballtables, p.pubinsert, p.pubupdate , p.pubdelete, p.pubtruncate FROM pg_publication AS p JOIN pg_catalog.pg_roles AS r ON p.pubowner = r.oid WHERE p.pubname = %(pname)szSELECT obj_description(p.oid, 'pg_publication') AS comment, r.rolname AS pubowner, p.puballtables, p.pubinsert, p.pubupdate , p.pubdelete FROM pg_publication AS p JOIN pg_catalog.pg_roles AS r ON p.pubowner = r.oid WHERE p.pubname = %(pname)spname query_paramsrrr rX)r= pgtrunc_supqueryresults r__get_general_pub_infoz$PgPublication.__get_general_pub_infosYt'HZ_a  3E3E$Wdii4HZ_` !9 rc<d}t||d|jidS)zGet and return tables that are published by the publication. Returns: List of dicts with published tables. znSELECT schemaname || '.' || tablename as schema_dot_table FROM pg_publication_tables WHERE pubname = %(pname)srFrrr=rs r__get_tables_pub_infoz#PgPublication.__get_tables_pub_info% He7DII2FX]^^rc<d}t||d|jidS)zGet and return any row filters for each table that are published by the publication. Returns: List of dicts with row filters for each table. zSELECT schemaname || '.' || tablename as schema_dot_table, rowfilter FROM pg_publication_tables WHERE pubname = %(pname)s AND rowfilter is not NULLrFrrrs r__get_rowfilters_pub_infoz'PgPublication.__get_rowfilters_pub_infos' Ge7DII2FX]^^rc<d}t||d|jidS)zGet and return columns that are published by the publication. Returns: List of dicts with published columns. zSELECT schemaname || '.' || tablename as schema_dot_table, attnames as columns FROM pg_publication_tables WHERE pubname = %(pname)srFrrrs r__get_columns_pub_infoz$PgPublication.__get_columns_pub_inforrcd}t||d|jid}g}|D]!}|j|j#|S)zhGet and return schemas added to the publication. Returns: List of schemas. zSELECT n.nspname FROM pg_namespace AS n JOIN pg_publication_namespace AS pn ON n.oid = pn.pnnspid JOIN pg_publication AS p ON p.oid = pn.pnpubid WHERE p.pubname = %(pname)srFr)r rXextendvalues)r=r list_of_dictslist_of_schemasds r__get_schema_pub_infoz#PgPublication.__get_schema_pub_infosW /!uGTYY;O168  /A  " "188: . /rcjd}t||d|id}t|Dcgc]}|d c}Scc}w)zaGet and return columns names of the table. Returns: Set of columns. zySELECT attname as column_name FROM pg_attribute WHERE attrelid = %(table)s::regclass and attnum > 0 AND NOT attisdropped;rFr column_name)r r$)r=rrrrzs r__get_table_columnsz!PgPublication.__get_table_columns s> ]$We4DV[\&93C &9::9s 0ct|d}||vr |d||zz }dt|jdd|}|j||S)aLAdd a table to the publication. Args: table (str): Table name. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: True if successful, False otherwise. rrALTER PUBLICATION r~ ADD TABLE rr rXr)r=rr-r quoted_tblrs r__pub_add_tablezPgPublication.__pub_add_tablesZ)8 J  ;E):: ;J8KDIIWd8e8BDu<>? $$Z0  1 9LDIIWd8e8< -8PRu<K " j.? ?@   k *  +!M : YYz " $  u<dii*W*=fh*OQu<r_r\rrgrrprrrurtrsrrrrrrrrrrrrMrrrOrOs~ '"2h?BDLU(@_ __$ ;=(="=.=$=.=$=$=$=";rrOc8t}|jtdtddgddddgtdd d d g td d tdtdtddtdtddtddtd ddtddtdd t|dddg}|jd}|jd}|jd}|jd}|jd}|jd}|jd}|jd} |jd } |jd!} |jd"} |jd#} | s8|sd}n"t |Dcgc] \}}|d$|}}}t ||||||| |d k(r9|r|jd%|r|jd&|r|jd'|d k(r|r|jd(t|t||j}t||d)\}}|jd3it}t|j}|tkr|j!d*+| |d,kr|j!d-+| r|d,kr|j!d.+| |d,kr|j!d/+d}t#||||}|r t%|}| r t'| } | r t)| ni} |d k(rT|j*s$|j-|| | | ||| |j.0}nF|j|| | | ||| |j.0}n"|d k(r|j1||j.1}i}|d k(s|d k(r|j.r|j3}n|d k(r|j.sd|_|j5|j5|j6d3||j8|j*d2|ycc}}w)4NT)requiredstrdbz5.0.0zcommunity.postgresql)rXversioncollection_name)typealiasesdeprecated_aliasespresentabsent)rdefaultchoiceslist)relementsdict)rboolF)rr)rrr) rXlogin_dbstaterRrSrTr session_role trust_inputrbrr%r-)rRrr%)r-r) argument_specsupports_check_modemutually_exclusiverXrrRrSrTrrrrbrr%r-z = z1parameter "tables" is ignored when "state=absent"z5parameter "parameters" is ignored when "state=absent"z0parameter "owner" is ignored when "state=absent"z3parameter "cascade" is ignored when "state=present") autocommitz3PostgreSQL server version should be 10.0 or greater)msgrnzHPublication of tables in schema is supported by PostgreSQL 15 or greaterz?Publication of columns is supported by PostgreSQL 15 or greaterz6Row filtering is supported by PostgreSQL 15 or greaterr)rr)rqueriesr]rM)rrgrrrrrwarnr r r rWrr connectionSUPPORTED_PG_VERSION fail_jsonrOrr'r0r]rrrr_close exit_jsonrZ)rrVrXrrRrrTrrrrbrr%r-rkr conn_params db_connectiondummyrWrYrr~ pub_fin_infos rmainrsI13M 4 54&"#9 F y8Y:OP%0V$&%0u%fd3%.6E4H&$/VT2'*# E>@F == D MM' "E ]]8 $F ]]< (F MM' "EmmI&G==0L-- .KmmI&G}}%78mmI&G|,J K:CF:KL$!Q1-LKLFD&% +w 8   KKK L  KKO P  KKJ K g IJ !&&--8K(NM5 !] ! ! 3N 3F$F$5$56J((RS# V(;gh:&^_*v"5UVG jAK 08(1:D*:6"J !!!((1A7JX^`e)0V=N=N)PG"((1A7JX^`e)0V=N=N)PG ( ""7v?P?P"QL ex/F4E4E"++- ( 6#4#4"  LLNFvWk.J.JS^SeSeviuvWMsP__main__N)$ __future__rrrr __metaclass__ DOCUMENTATIONEXAMPLESRETURNansible.module_utils.basicransible.module_utils.sixrFansible_collections.community.postgresql.plugins.module_utils.databaserr Fansible_collections.community.postgresql.plugins.module_utils.postgresr r r r rrrrrrrr'r0r9rr,rOrrIrMrrrsA@ x tQ f# L5.&  ;   *(&&,V ;V ;||w~ zFr