Vhl~VddlmZmZmZeZdZdZdZddl Z ddl Z ddl Z ddl m Z ddlmZddlmZdd lmZmZdd lmZmZmZmZmZmZmZgZGd d eZd Z dZ!dZ"dZ#dZ$dZ%ddZ&dZ'dZ(dZ) d dZ* d!dZ+d"dZ,dZ-dZ.ddZ/dZ0e1dk(re0yy)#)absolute_importdivisionprint_functiona --- module: postgresql_db short_description: Add or remove PostgreSQL databases from a remote host description: - Add or remove PostgreSQL databases from a remote host. options: name: description: - Name of the database to add or remove. type: str required: true aliases: [ db ] owner: description: - Name of the role to set as owner of the database. type: str default: '' template: description: - Template used to create the database. type: str default: '' encoding: description: - Encoding of the database. type: str default: '' lc_collate: description: - Collation order (LC_COLLATE) to use in the database must match collation order of template database unless C(template0) is used as template. type: str default: '' lc_ctype: description: - Character classification (LC_CTYPE) to use in the database (e.g. lower, upper, ...). - Must match LC_CTYPE of template database unless C(template0) is used as template. type: str default: '' icu_locale: description: - Specifies the ICU locale (ICU_LOCALE) for the database default collation order and character classification, overriding the setting locale. - The locale provider must be ICU. The default is the setting of locale if specified; otherwise the same setting as the template database. type: str default: '' version_added: '3.4.0' locale_provider: description: - Specifies the provider to use for the default collation in this database (LOCALE_PROVIDER). - Possible values are icu (if the server was built with ICU support) or libc. - By default, the provider is the same as that of the template. type: str default: '' version_added: '3.4.0' 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 state: description: - The database state. - C(present) implies that the database should be created if necessary. - C(absent) implies that the database should be removed if present. - C(dump) requires a target definition to which the database will be backed up. (Added in Ansible 2.4) Note that in some PostgreSQL versions of pg_dump, which is an embedded PostgreSQL utility and is used by the module, returns rc 0 even when errors occurred (e.g. the connection is forbidden by pg_hba.conf, etc.), so the module returns changed=True but the dump has not actually been done. Please, be sure that your version of pg_dump returns rc 1 in this case. - C(restore) also requires a target definition from which the database will be restored. (Added in Ansible 2.4). - The format of the backup will be detected based on the target name. - Supported compression formats for dump and restore determined by target file format C(.pgc) (custom), C(.bz2) (bzip2), C(.gz) (gzip/pigz) and C(.xz) (xz). - Supported formats for dump and restore determined by target file format C(.sql) (plain), C(.tar) (tar), C(.pgc) (custom) and C(.dir) (directory) For the directory format which is supported since collection version 1.4.0. - "Restore program is selected by target file format: C(.tar), C(.pgc), and C(.dir) are handled by pg_restore, other with pgsql." - "." - DEPRECATED (see the L(discussion,https://github.com/ansible-collections/community.postgresql/issues/820)). C(rename) is used to rename the database C(name) to C(target). To rename a database, use the M(community.postgresql.postgresql_query) module. type: str choices: [ absent, dump, present, rename, restore ] default: present force: description: - Used to forcefully drop a database when the I(state) is C(absent), ignored otherwise. type: bool default: False target: description: - File to back up or restore from. - Used when I(state) is C(dump) or C(restore). type: path default: '' target_opts: description: - Additional arguments for pg_dump or restore program (pg_restore or psql, depending on target's format). - Used when I(state) is C(dump) or C(restore). type: str default: '' maintenance_db: description: - The value specifies the initial database (which is also called as maintenance DB) that Ansible connects to. type: str default: postgres conn_limit: description: - Specifies the database connection limit. type: str default: '' tablespace: description: - The tablespace to set for the database U(https://www.postgresql.org/docs/current/sql-alterdatabase.html). - If you want to move the database back to the default tablespace, explicitly set this to pg_default. type: path default: '' dump_extra_args: description: - Provides additional arguments when I(state) is C(dump). - Cannot be used with dump-file-format-related arguments like ``--format=d``. type: str version_added: '0.2.0' trust_input: description: - If C(false), check whether values of parameters I(owner), I(conn_limit), I(encoding), I(db), I(template), I(tablespace), I(session_role) 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 database. - To reset the comment, pass an empty string. type: str version_added: '3.3.0' seealso: - name: CREATE DATABASE reference description: Complete reference of the CREATE DATABASE command documentation. link: https://www.postgresql.org/docs/current/sql-createdatabase.html - name: DROP DATABASE reference description: Complete reference of the DROP DATABASE command documentation. link: https://www.postgresql.org/docs/current/sql-dropdatabase.html - name: pg_dump reference description: Complete reference of pg_dump documentation. link: https://www.postgresql.org/docs/current/app-pgdump.html - name: pg_restore reference description: Complete reference of pg_restore documentation. link: https://www.postgresql.org/docs/current/app-pgrestore.html - module: community.postgresql.postgresql_tablespace - module: community.postgresql.postgresql_info - module: community.postgresql.postgresql_ping notes: - State C(dump) and C(restore) don't require I(psycopg) since ansible version 2.8. attributes: check_mode: support: full author: "Ansible Core Team" extends_documentation_fragment: - community.postgresql.postgres a@ - name: Create a new database with name "acme" community.postgresql.postgresql_db: name: acme comment: My test DB # Note: If a template different from "template0" is specified, # encoding and locale settings must match those of the template. - name: Create a new database with name "acme" and specific encoding and locale # settings community.postgresql.postgresql_db: name: acme encoding: UTF-8 lc_collate: de_DE.UTF-8 lc_ctype: de_DE.UTF-8 locale_provider: icu icu_locale: de-DE-x-icu template: template0 # Note: Default limit for the number of concurrent connections to # a specific database is "-1", which means "unlimited" - name: Create a new database with name "acme" which has a limit of 100 concurrent connections community.postgresql.postgresql_db: name: acme conn_limit: "100" - name: Dump an existing database to a file community.postgresql.postgresql_db: name: acme state: dump target: /tmp/acme.sql - name: Dump an existing database to a file excluding the test table community.postgresql.postgresql_db: name: acme state: dump target: /tmp/acme.sql dump_extra_args: --exclude-table=test - name: Dump an existing database to a file (with compression) community.postgresql.postgresql_db: name: acme state: dump target: /tmp/acme.sql.gz - name: Dump a single schema for an existing database community.postgresql.postgresql_db: name: acme state: dump target: /tmp/acme.sql target_opts: "-n public" - name: Dump only table1 and table2 from the acme database community.postgresql.postgresql_db: name: acme state: dump target: /tmp/table1_table2.sql target_opts: "-t table1 -t table2" - name: Dump an existing database using the directory format community.postgresql.postgresql_db: name: acme state: dump target: /tmp/acme.dir - name: Dump an existing database using the custom format community.postgresql.postgresql_db: name: acme state: dump target: /tmp/acme.pgc # name: acme - the name of the database to connect through which the recovery will take place - name: Restore database using the tar format community.postgresql.postgresql_db: name: acme state: restore target: /tmp/acme.tar # Note: In the example below, if database foo exists and has another tablespace # the tablespace will be changed to foo. Access to the database will be locked # until the copying of database files is finished. - name: Create a new database called foo in tablespace bar community.postgresql.postgresql_db: name: foo tablespace: bar z executed_commands: description: List of commands which tried to run. returned: success type: list sample: ["CREATE DATABASE acme"] version_added: '0.2.0' N) to_native) AnsibleModule) shlex_quote) SQLParseError check_input) connect_to_dbensure_required_libsget_conn_paramsget_server_versionpg_cursor_argspostgres_common_argument_spec set_commentc eZdZy)NotSupportedErrorN)__name__ __module__ __qualname__v/home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/postgresql/plugins/modules/postgresql_db.pyrr-srrcbd|d|d}tj||j|y)NALTER DATABASE "z " OWNER TO ""Texecuted_commandsappendexecute)cursordbownerquerys r set_ownerr%5s)35u =EU# NN5 rc`d|d|}tj||j|y)Nrz" CONNECTION LIMIT Tr)r!r" conn_limitr$s rset_conn_limitr(<s)9;Z HEU# NN5 rcTd}|j|d|i|jdS)Nz8SELECT pg_char_to_encoding(%(encoding)s) AS encoding_id;encoding encoding_id)r fetchone)r!r*r$s rget_encoding_idr-Cs, FE NN5:x01 ?? ] ++rct|j}|dk\rd}n|dk\r|dkrd}nd}|j|d|i|jS)Niaz SELECT rolname AS owner, pg_encoding_to_char(encoding) AS encoding, encoding AS encoding_id, datcollate AS lc_collate, datctype AS lc_ctype, datlocale AS icu_locale, CASE datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS locale_provider, pg_database.datconnlimit AS conn_limit, spcname AS tablespace, pg_catalog.shobj_description(pg_database.oid, 'pg_database') AS comment FROM pg_database JOIN pg_roles ON pg_roles.oid = pg_database.datdba JOIN pg_tablespace ON pg_tablespace.oid = pg_database.dattablespace WHERE datname = %(db)s Ia} SELECT rolname AS owner, pg_encoding_to_char(encoding) AS encoding, encoding AS encoding_id, datcollate AS lc_collate, datctype AS lc_ctype, daticulocale AS icu_locale, CASE datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS locale_provider, pg_database.datconnlimit AS conn_limit, spcname AS tablespace, pg_catalog.shobj_description(pg_database.oid, 'pg_database') AS comment FROM pg_database JOIN pg_roles ON pg_roles.oid = pg_database.datdba JOIN pg_tablespace ON pg_tablespace.oid = pg_database.dattablespace WHERE datname = %(db)s aE SELECT rolname AS owner, pg_encoding_to_char(encoding) AS encoding, encoding AS encoding_id, datcollate AS lc_collate, datctype AS lc_ctype, null::char AS icu_locale, null::text AS locale_provider, pg_database.datconnlimit AS conn_limit, spcname AS tablespace, pg_catalog.shobj_description(pg_database.oid, 'pg_database') AS comment FROM pg_database JOIN pg_roles ON pg_roles.oid = pg_database.datdba JOIN pg_tablespace ON pg_tablespace.oid = pg_database.dattablespace WHERE datname = %(db)s r")r connectionr r,)r!r"server_versionr$s r get_db_infor2Isa'(9(9:N   6 !nv&=     NN54*% ?? rcLd}|j|d|i|jdk(S)Nz.SELECT * FROM pg_database WHERE datname=%(db)sr")r rowcount)r!r"r$s r db_existsr6vs( pg_backend_pid()zSELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE pg_stat_activity.datname=%(db)s AND procpid <> pg_backend_pid()z@UPDATE pg_database SET datallowconn = false WHERE datname=%(db)sz; r")rr0r )r!r"query_terminate query_blockr$s r db_dropconnsr:|sS&++,5=_ >cUK $  0E NN54*%rct||rXd|z}|r*t|jdk\rd|z}n t||tj ||j |yy)NzDROP DATABASE "%s"izDROP DATABASE "%s" WITH (FORCE)TF)r6rr0r:rrr )r!r"forcer$s r db_deleter=s`$r) !&"3"34>:R?VR(  'urc t|||||| | } t|jdk\}d|zg}|r|jd|z|r|jd|z|r|jd|r|jd|r|jd|r|r|jd |r|r|jd | r|jd | z| r|jd d | izdj |}t j|j || |j|| | rt|| d|| t y)N)enccollatectype iculocalelocaleproviderr' tablespacer/zCREATE DATABASE "%s"z OWNER "%s"z TEMPLATE "%s"zENCODING %(enc)szLC_COLLATE %(collate)szLC_CTYPE %(ctype)szICU_LOCALE %(iculocale)sz"LOCALE_PROVIDER %(localeprovider)szTABLESPACE "%s"zCONNECTION LIMIT %(conn_limit)sr' databaseT) dictrr0rjoinrmogrifyr r)r!r"r#templater* lc_collatelc_ctype icu_localelocale_providerr'rDcomment check_modeparams icu_supportedquery_fragmentsr$s r db_createrTsQ h (jixFP')F'v'8'89VCM-23O |e349:127834m9:=CD0:=>@LR\C]]^ HH_ %EV^^E6:; NN5&!FGZZARS rc 0t||} |r#t||| dk7rtd| dz|r|| dk7rtd| dz|r|| dk7rtd| dz|r|| dk7rtd | dz|r|| d k7rtd | d zd } | d d| d <|r|| dk7r t|||} |r|t | dk7r t |||} | r| | dk7r t ||| } | | | d k7rt|| d|| t} | S)Nr+zAChanging database encoding is not supported. Current encoding: %sr*rKz> Z8jGL$99 Z8w')*<<fgz2zK\] Nrc 6t||syt||} | dd| d<|rt||| dk7ry|r || dk7ry|r || dk7ry|r || dk7ry|r || dk7ry|r || d k7ry| r| t| d k7ry| r | | d k7ry| | | dk7ryy ) NFrOrVr+rKrLrMrNr#r'rDT)r6r2r-rW) r!r"r#rJr*rKrLrMrNr'rDrOrYs r db_matchesr]s VR fb) 9  %!#GI  :gm>TT J',*?? (gj&99 J',*?? G {2} & {0} >{3}z {0} -f {1}z {0} > {1}) login_flags get_bin_pathospathsplitextrrHformattmpdirmkfifordo_with_password)moduletarget target_optsr"dump_extra_argsuserpasswordhostportkwflagscmdcomp_prog_pathfifos rdb_dumprs D$ >E   i .CN ww#v- ]#   & !" % / ]#   & !" % / ]# ww#u,   v &#00>N#00>N   & !" % /,,Wd;   & !" % .,,T482775>C w~~o.. w~~k**ww||FMM95 $)00nkRXFY[_` C %%c;v+>?C$$S+f*=>C FC 22rc (t||||} d} |jddtjj |ddk(r"| j dj |ntjj |ddk(r%| j d|jddn9tjj |dd k(r$| j d |jddntjj |dd k(r$| j d |jddntjj |dd k(r|jdd} notjj |ddk(r|jdd} n7tjj |ddk(r|jdd} dj| z |rdj |z | r!tjj} |rd|i} tj| |gtjtj} tj| jtjtjd| } | j\}}| jj| j!| j"dk7r*| j$j'}| j"d|dfS| j"d|dfSt)fddDrdj t+|ndj t+|t-||S)NpsqlTraz.sqlz --file={0}rbz --format=Tar pg_restorercz --format=Customrdz --format=DirectoryrezcatrhbzcatrjxzcatrVrl PGPASSWORD)stdoutstderr)stdinrrshellenvrz cmd: ****c3&K|]}|v yw)Nr).0 substringrs r zdb_restore..{s[IyC[s)z--format=Directoryz--format=Customz{0} {1}z {0} < {1})rnrorprqrrrrsrHenvironcopy subprocessPopenPIPEr communicateclosewait returncoderreadanyrrv)rwrxryr"r{r|r}r~rrrrp1p2stdout2stderr2stderr1rs @r db_restorerDs D$ -EN   fd +C ww#v- ]))&12   & !" % / _%!!,5   & !" % / '(!!,5   & !" % / *+!!,5   & !" % .,,VT:   & !" % /,,Wd;   & !" % .,,Wd;2775>C w~~k**jjoo *C   ~v6zWaWfWf g   c:??S]SbSbjntw x^^-'   ==A iinn&G=="g{: :=="g{: : [1Z[ [""3 F(;C FC 22rcg}|rU|r*|jdjt|n)|jdjt||r |jdj||r |jdj||r |jdj||S)a, returns a list of connection argument strings each prefixed with a space and quoted where necessary to later be combined in a single shell string with `"".join(rv)` db_prefix determines if "--dbname" is prefixed to the db argument, since the argument was introduced in 9.3. z --dbname={0}z {0}z --host={0}z --port={0}z --username={0})rrsr)r"r}r~r{r_rs rrnrns E  LL// B@ A LL{27 8  ]))$/0  ]))$/0  &--d34 Lrcxi}|rd|i}tj||j|d|\}}}||||fS)NrT)use_unsafe_shellenviron_update)rr run_command)rwrr|rrcrrs rrvrvsP CX&S!++C$WZ+[B vvs ""rcbd|d|d}tj||j|y)Nrz" SET TABLESPACE "rTr)r!r"rDr$s rrXrXs)9;Z HEU# NN5 rct||}t||}|r|r|jd|s|ry|s|s|jd|r6|s3|ryd|d|d}tj||j |yyy) Nz/Both the source and the target databases exist.msgFz1The source and the target databases do not exist.Trz " RENAME TO "r)r6 fail_jsonrrr )rwr!r"rxrP source_db target_dbr$s r rename_dbrs&"%I&&)IYNO   YPQ  9;FC  'u#yrc t}|jtdddgtddtddtddtddtddtddtddtddgd td dtddtdd td tddtd dtddtd dtd dtddt|d}|jd}|jd}|jd}|jd}|jd}|jd}|jd}|jd} |jd} |jd} |jd} d} |jd}|jd}|jd}|jd}|jd }|jd!}|jd"}|jd#}| d$k(rS|j d%| s|j d&'|| k(r|j d('||k(r|j d)'|st||||||||| | d*v}|s t|| dk(rCd+jtj|} tjj| } |j}|jd|d<t||d,}|s>t||d-\}}|j d9it"}|r |j%d.|z |j.r\| d1k(r t1|} n2| dk(rt3|||||||| ||| } n| d$k(rt5||| d2} |j7| |t83| d1k(rt;||} n| dk(rOt1|s"t=||||||||| ||||j. } nt?|||||||| ||||j. } n|rt||jd,}| d4k(xrt@xstB}| d4k(r||| | ||fi|\}}} }!n||| | |fi|\}}} }!|d5k7r|j | |||!6n/|j7d|| ||!t87n| d$k(rt5||| } |s jKjK|j7| |t83y#t&$r<}|j d/t)|zt+j,0Yd}~d}~wwxYw#tDtFf$r8}|j t)|t+j,0Yd}~d}~wtH$rt&$r<}|j d8t)|zt+j,0Yd}~d}~wwxYw):NrWTname)typerequiredaliasesrV)rdefaultpresent)absentdumprrenamerestore)rrchoicesrqpostgres)rboolF)r"r#rJr*rKrLrMrNstaterxrymaintenance_db session_roler'rDrz trust_inputr<rO) argument_specsupports_check_moder"r#rJr*rKrLrMrNrxryrrrr'rDrzrr<rOrzThe rename choice of the state option is deprecated and will be removed in version 5.0.0. Use the community.postgresql.postgresql_query module instead.zEThe "target" option must be defined when the "rename" option is used.rz@The "name/db" option and the "target" option cannot be the same.zHThe "maintenance_db" option and the "name/db" option cannot be the same.)rrz {0}/{1}.sql)warn_db_default) autocommitz SET ROLE "%s"zCould not switch role: %s)r exceptionr)rP)rZr"rrr)rrrr)rZrrrrrzDatabase query failed: %sr)&rupdaterGrrQwarnrr r rsrpgetcwdrq expanduserr r r!rr Exceptionr traceback format_excrPr6r]r exit_jsonrr=rTr[rrr r SystemExitr)"rrwr"r#rJr*rKrLrMrNrxryrrZrrr'rDrzrr<rOraw_connection params_dict conn_params db_connectiondummyr!emethodrrrrs" rmainrs13M UTF8 <r*5"-5"-UB/5"-UB/%4yMO,eR0 ;u%UB/VR0%6fd3.%.).# F t B MM' "E}}Z(H}}Z(H|,J}}Z(H|,Jmm$56O ]]8 $F-- .K MM' "EG]]#34N==0L|,J|,Jmm$56O-- .K MM' "EmmI&G  f g   !h  i <   !c  d R    !k  l FE:xj, A11N V$ |%%biik26##F+--K &67K"&+uMK ,V[TR u%%%77  s=>7k    #FB/)#(UHhPZ\d)3_jR\^egg("#FFB4P   WGX  Y H E2G i VR(#FBx:$,j/:$.9J9JL$FBx$,j/:$.9J9JL)&&--QVWKf_0>JF*0bRa*qep*q'FFC*0b*`T_*`'FFCQw  VFrs K  6&RUX3D!Fh F;G   W?PQA s  %@9Q<%O[d[o[o[q rr sh , -MYq\Y5I5I5KLL  k89Q<GS\SgSgSijjks=T#E1U U1UUW*&.VW*.1W%%W*__main__)F)rVNNNNNN)rVNNNNN)T)2 __future__rrrr __metaclass__ DOCUMENTATIONEXAMPLESRETURNrprransible.module_utils._textransible.module_utils.basicransible.module_utils.six.movesrFansible_collections.community.postgresql.plugins.module_utils.databaser r Fansible_collections.community.postgresql.plugins.module_utils.postgresr r r rrrrrrrr%r(r-r2r6r:r=rTr[r]rrrnrvrXrrrrrrrsA@ h TT l  046  , *Z & D/d@)+  33l,. <3~0#>]R@ zFr