
    Vhdh                         d dl mZmZmZ eZdZdZdZd dl	Z	d dl
m
Z
 d dlmZ d dlmZ d d	lmZ d d
lmZ d dlmZmZmZmZmZmZ  G d de      Z G d de      Zd Zedk(  r e        yy)    )absolute_importdivisionprint_functiona  
---
module: postgresql_info
short_description: Gather information about PostgreSQL servers
description:
- Gathers information about PostgreSQL servers.
options:
  filter:
    description:
    - Limit the collected information by comma separated string or YAML list.
    - Allowable values are C(version),
      C(databases), C(in_recovery), C(settings), C(tablespaces), C(roles),
      C(replications), C(repl_slots).
    - By default, collects all subsets.
    - You can use shell-style (fnmatch) wildcard to pass groups of values (see Examples).
    - You can use '!' before value (for example, C(!settings)) to exclude it from the information.
    - If you pass including and excluding values to the filter, for example, I(filter=!settings,ver),
      the excluding values will be ignored.
    type: list
    elements: str
  login_db:
    description:
    - Name of database to connect.
    type: str
    aliases:
    - db
  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
  trust_input:
    description:
    - If C(false), check whether a value of I(session_role) is potentially dangerous.
    - It makes sense to use C(false) only when SQL injections via I(session_role) are possible.
    type: bool
    default: true
    version_added: '0.2.0'

attributes:
  check_mode:
    support: full

seealso:
- module: community.postgresql.postgresql_ping

author:
- Andrew Klychkov (@Andersson007)

extends_documentation_fragment:
- community.postgresql.postgres
a  
# Display info from postgres hosts.
# ansible postgres -m postgresql_info

# Display only databases and roles info from all hosts using shell-style wildcards:
# ansible all -m postgresql_info -a 'filter=dat*,rol*'

# Display only replications and repl_slots info from standby hosts using shell-style wildcards:
# ansible standby -m postgresql_info -a 'filter=repl*'

# Display all info from databases hosts except settings:
# ansible databases -m postgresql_info -a 'filter=!settings'

- name: Collect PostgreSQL version and extensions
  become: true
  become_user: postgres
  community.postgresql.postgresql_info:
    filter: ver*,ext*

- name: Collect all info except settings and roles
  become: true
  become_user: postgres
  community.postgresql.postgresql_info:
    filter: "!settings,!roles"

# On FreeBSD with PostgreSQL 9.5 version and lower use pgsql user to become
# and pass "postgres" as a database to connect to
- name: Collect tablespaces and repl_slots info
  become: true
  become_user: pgsql
  community.postgresql.postgresql_info:
    login_db: postgres
    filter:
    - tablesp*
    - repl_sl*

- name: Collect all info except databases
  become: true
  become_user: postgres
  community.postgresql.postgresql_info:
    filter:
    - "!databases"
a  
version:
  description: Database server version.
  returned: success
  type: dict
in_recovery:
  description: Indicates if the service is in recovery mode or not.
  returned: success
  type: bool
  sample: false
databases:
  description: Information about databases.
  returned: success
  type: dict
repl_slots:
  description:
  - Replication slots (available in 9.4 and later).
  returned: if existent
  type: dict
replications:
  description:
  - Information about the current replications by process PIDs.
  returned: if pg_stat_replication view existent
  type: dict
tablespaces:
  description:
  - Information about tablespaces.
  returned: success
  type: dict
roles:
  description:
  - Information about roles.
  returned: success
  type: dict
pending_restart_settings:
  description:
  - List of changed settings that will take effect upon restart .
  returned: success
  type: list
settings:
  description:
  - Information about run-time server parameters.
  returned: success
  type: dict
N)fnmatch)	to_native)AnsibleModule)	iteritems)check_input)connect_to_dbensure_required_libsget_conn_paramsget_server_versionpg_cursor_argspostgres_common_argument_specc                   $    e Zd ZdZd ZddZd Zy)PgDbConnzAuxiliary class for working with PostgreSQL connection objects.

    Arguments:
        module (AnsibleModule): Object of AnsibleModule class that
            contains connection parameters.
    c                 .    || _         d | _        d | _        y )N)moduledb_conncursor)selfr   s     x/home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/postgresql/plugins/modules/postgresql_info.py__init__zPgDbConn.__init__   s        c                    t        | j                         t        | j                  | j                  j                  d      }t	        | j                  ||      \  | _        }| j
                  y | j
                  j                  di t        S )zConnect to a PostgreSQL database and return a cursor object.

        Note: connection parameters are passed by self.module object.
        F)warn_db_defaultfail_on_connN )r   r   r   paramsr   r   r   r   )r   r   conn_paramsdummys       r   connectzPgDbConn.connect   sn     	T[[)%dkk4;;3E3EW\]+DKKS_`e<<"t||""4^44r   c                    | j                   | j                   j                          || j                  j                  d<   || j                  j                  d<   || j                  j                  d<   | j	                  d      S )zReconnect to another database and return a PostgreSQL cursor object.

        Arguments:
            dbname (string): Database name to connect to.
        dbdatabaselogin_dbFr   )r   closer   r    r#   )r   dbnames     r   	reconnectzPgDbConn.reconnect   sm     <<#LL  $*4 )/:&)/:&|||//r   N)T)__name__
__module____qualname____doc__r   r#   r*   r   r   r   r   r      s    
50r   r   c                   ~    e Zd ZdZd ZddZd Zd Zd Zd Z	d Z
d	 Zd
 Zd Zd Zd Zd Zd Zd Zd Zd Zd Zy)PgClusterInfozClass for collection information about a PostgreSQL instance.

    Arguments:
        module (AnsibleModule): Object of AnsibleModule class.
        db_conn_obj (psycopg.connect): PostgreSQL connection object.
    c           
          || _         || _        |j                         | _        | j	                         | _        i d i i i i i i g d	| _        y )N)	versionin_recoverytablespaces	databasesreplications
repl_slotssettingsrolespending_restart_settings)r   db_objr#   r   _PgClusterInfo__get_current_db
default_dbpg_info)r   r   db_conn_objs      r   r   zPgClusterInfo.__init__   sT    !!))+//1(*

r   c           	         | j                   | j                  | j                  | j                  | j                  | j
                  | j                  | j                  d}g }g }|r|D ]<  }|d   dk7  r|j                  |       |j                  |j                  d             > |r'|D ]!  }|D ]  }t        ||      s ||            ! # nB|r@d}|D ]'  }|D ]  }t        ||      sd} |s ||           &d}) n|D ]  } ||            | j                  j                          | j                  j                  j                          | j                  S )z-Collect information based on 'filter' option.)r2   r3   r4   r5   r6   r7   r8   r9   r   !FT)get_pg_versionget_recovery_stateget_tablespacesget_db_infoget_repl_infoget_rslot_infoget_settingsget_role_infoappendlstripr   r   r(   r;   r   r>   )	r   val_list
subset_map	incl_list	excl_listisfoundes	            r   collectzPgClusterInfo.collect   su    **22//)) ..--))''	

 		
  4Q43;$$Q'$$QXXc]3	4 # "A& ""1a=)JqMO!""
 # &A& )"1a=$(E) !%
1 %&    
1  	!!#||r   c                    d}| j                  |      }|r|D cg c]  }t        |       }}ni S i }|D ]B  }|j                  |d         si ||d   <   t        |      D ]  \  }}|dk7  s|||d      |<    D |S c c}w )zGet publication statistics.zzSELECT p.*, r.rolname AS ownername FROM pg_catalog.pg_publication AS p JOIN pg_catalog.pg_roles AS r ON p.pubowner = r.oidpubname)_PgClusterInfo__exec_sqldictgetr	   )r   queryresultrowpublicationselemkeyvals           r   get_pub_infozPgClusterInfo.get_pub_info.  s    )
 '+12Cd3i2F2I 	=D##DO402T)_-%dO =S)#9<Li1#6=		=  3s   A<c                    d}| j                  |      }dj                  |D cg c]
  }d|d   z   c}      }d|z  }| j                  |      }|r|D cg c]  }t        |       }}ni S i }|D ]p  }	|j                  |	d         si ||	d   <   ||	d      j                  |	d         r:i ||	d      |	d   <   t	        |	      D ]  \  }
}|
dvs|||	d      |	d      |
<    r |S c c}w c c}w )	zGet subscription statistics.zwSELECT column_name FROM information_schema.columns WHERE table_schema = 'pg_catalog' AND table_name = 'pg_subscription'z, zs.%scolumn_namezSELECT %s, r.rolname AS ownername, d.datname AS dbname FROM pg_catalog.pg_subscription s JOIN pg_catalog.pg_database d ON s.subdbid = d.oid JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oidr)   subname)rd   r)   )rW   joinrX   rY   r	   )r   columns_sub_tablecolumns_resultcolumncolumnsrZ   r[   r\   subscr_infor^   r_   r`   s               r   get_subscr_infozPgClusterInfo.get_subscr_infoH  s6   B ):;)).YVf]&;;YZ)
 ,33 '+12Cd3i2F2I 		PD??4>2.0DN+tH~.224	?C?ADN+DO< )$ PHC"77LODN3DODSIP		P ; Z 3s   C%C*c                     | j                  d      }|sd}nd}| j                  |      }i }|D ]7  }|d   }t        |d   |d   r|d   nd      }|r|d	   r|d	   ng |d	<   |||<   9 || j                  d
<   y)z"Get information about tablespaces.ztSELECT column_name FROM information_schema.columns WHERE table_name = 'pg_tablespace' AND column_name = 'spcoptions'zlSELECT s.spcname, pg_catalog.pg_get_userbyid(s.spcowner) as rolname, s.spcacl::text FROM pg_tablespace AS s zzSELECT s.spcname, pg_catalog.pg_get_userbyid(s.spcowner) as rolname, s.spcacl::text, s.spcoptions FROM pg_tablespace AS s spcnamerolnamespcacl )spcownerro   
spcoptionsr4   NrW   rX   r>   )r   optrZ   rests_dictrP   ts_namets_infos           r   rD   zPgClusterInfo.get_tablespacesn  s     oo ? @
 0E0E ooe$ 		'A	lG9&'kq{rG ;<\?,PR%&GG		' '.]#r   c           
      |   | j                  d      }|d   d   syd}| j                  |      }i }|D ]  }|d   }t        j                  d|d         ddg}nv|d   j                  d	      }t        j                  d
|d         |d   j                  d
      }n4	 t        j                  d
|d         |d   j                  d
      d   |d<   t        t        |d   rt        |d         nd|d   rt        |d         nd|      |d   |d         ||d   <    |S # t        $ r |j                  d       Y lw xY w)z*Get information about existing extensions.zYSELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'pg_extension')r   existsTa  SELECT e.extname, e.extversion, n.nspname, c.description FROM pg_catalog.pg_extension AS e LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description AS c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
extversionz/^([0-9]+([\-]*[0-9]+)?\.)*[0-9]+([\-]*[0-9]+)?$N.-   )majorminorrawnspnamedescription)r{   r   r   extname)rW   researchsplit
IndexErrorrJ   rX   int)r   ru   rZ   ext_dictrP   ext_ver_rawext_vers          r   get_ext_infozPgClusterInfo.get_ext_info  sm    oo C D 1vhS ooe$ 	AL/KyyKQ|_]e,L///499T71:.:%aj..s3G-99T71:6B)0)9)9#)>q)AGAJ &*-4QZ#gaj/T-4QZ#gaj/T#
 )m,&HQy\"!	4  & -t,-s   3DD;:D;c                     d}| j                  |      }i }|D ]1  }t        |d   |d   |d   r|d   nd|d   r|d   ng       ||d   <   3 || j                  d	<   y
)zBGet information about roles (in PgSQL groups and users are roles).a	  SELECT r.rolname, r.rolsuper, r.rolcanlogin, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members AS m JOIN pg_catalog.pg_roles AS b ON (m.roleid = b.oid) WHERE m.member = r.oid) AS memberof FROM pg_catalog.pg_roles AS r WHERE r.rolname !~ '^pg_'rolsuperrolcanloginrolvaliduntilrp   memberof)	superusercanloginvalid_until	member_ofrn   r9   Nrs   )r   rZ   ru   rol_dictrP   s        r   rI   zPgClusterInfo.get_role_info  s    - ooe$ 	A%)J-=)23O2DAo."+,Z=!J-b	&HQy\"	 !)Wr   c                     | j                  d      }|d   d   syd}| j                  |      }|syi }|D ]#  }t        |d   |d   |d   |d	   
      ||d   <   % || j                  d<   y)z1Get information about replication slots if exist.zaSELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'pg_replication_slots')r   rz   TzOSELECT slot_name, plugin, slot_type, database, active FROM pg_replication_slotsplugin	slot_typer&   active)r   r   r&   r   	slot_namer7   Nrs   )r   ru   rZ   
rslot_dictrP   s        r   rG   zPgClusterInfo.get_rslot_info  s     oo K L 1vh4ooe$ 
 	A)-{K.:{	*Jq~&	 &0\"r   c                 v   | j                  d      }|sd}nd}| j                  |      }i }|D ]  }d}|d   }|d   r|d   }nd}|dk(  rt        |      d	z  }n-|d
k(  rt        |      d	z  dz  }n|dk(  rt        |      d	z  d	z  }||dk  rd}|d   }	| j                  |	      }
d}|r|d   }t        |||d   |d   |d   r|d   nd|d   r|d   nd|d   r|d   nd|d   r|d   nd|
	      ||	<   ||||	   d<   ||||	   d<   |s| j                  d   j                  |	        || j                  d<   y)zGet server settings.zmSELECT 1 FROM information_schema.columns WHERE table_name = 'pg_settings' AND column_name = 'pending_restart'zeSELECT name, setting, unit, context, vartype, boot_val, min_val, max_val, sourcefile FROM pg_settingszvSELECT name, setting, unit, context, vartype, boot_val, min_val, max_val, sourcefile, pending_restart FROM pg_settingsNsettingunitrp   kBi   8kB   MBr   namepending_restartcontextvartypeboot_valmin_valmax_val
sourcefile)	r   r   r   r   r   r   r   r   
pretty_valval_in_bytesr:   r8   )rW   r   _PgClusterInfo__get_pretty_valrX   r>   rJ   )r   pend_rest_col_existsrZ   ru   set_dictrP   r   r   r   setting_namer   r   s               r   rH   zPgClusterInfo.get_settings  s     $ 0U  V $(E(E ooe$ ,	RAL	lGyyt|"7|d2"7|d2Q6"7|d2T9'L1,< V9L..|<J"O#"#$5"6%)))*+J-:R()))"()))"./o1\?2%
&H\" '9E&~6*<K&'89"LL!;<CCLQY,	R\ $,Z r   c           
         | j                  d      }|d   d   syd}| j                  |      }|syi }|D ]B  }t        |d   |d   r|d   nd|d	   |d
   r|d
   nd|d   |d         |t        |d         <   D || j                  d<   y)z=Get information about replication if the server is a primary.z`SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'pg_stat_replication')r   rz   TzSELECT r.pid, pg_catalog.pg_get_userbyid(r.usesysid) AS rolname, r.application_name, r.client_addr::text, r.client_hostname, r.backend_start::text, r.state FROM pg_stat_replication AS r rn   application_namerp   client_addrclient_hostnamebackend_startstate)usenameapp_namer   r   r   r   pidr6   N)rW   rX   strr>   )r   ru   rZ   	repl_dictrP   s        r   rF   zPgClusterInfo.get_repl_info/  s     oo J K 1vh2 ooe$ 	 	A'+)234F2G-.Rm,89:K8L"3 4RT0j(Ic!E(m$	 (1^$r   c                 ~    d}| j                  |      }i }|D ]"  }t        |d   |d   r|d   nd      ||d   <   $ |S )z2Get information about current supported languages.zjSELECT l.lanname, pg_catalog.pg_get_userbyid(l.lanowner) AS rolname, l.lanacl::text FROM pg_language AS l rn   lanaclrp   )lanownerr   lannamerW   rX   )r   rZ   ru   	lang_dictrP   s        r   get_lang_infozPgClusterInfo.get_lang_infoN  s\    *ooe$	 	A&*9&'kq{r'Ia	l#	 r   c                 ~    d}| j                  |      }i }|D ]"  }t        |d   |d   r|d   nd      ||d   <   $ |S )z!Get information about namespaces.zvSELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) AS rolname, n.nspacl::text FROM pg_catalog.pg_namespace AS n rn   nspaclrp   )nspownerr   r   r   )r   rZ   ru   nsp_dictrP   s        r   get_namespaceszPgClusterInfo.get_namespaces\  s[    6ooe$ 	A%)9&'kq{r&HQy\"	 r   c                    d}| j                  |      d   d   }d}| j                  |      d   d   }t        |dd       }t        |dd       }t        |dd       }|dk(  r&d	j                  t        |      t        |      g      }n/d	j                  t        |      t        |      t        |      g      }t	        |||||
      | j
                  d<   |dk(  r%|| j
                  d   d<   d| j
                  d   d<   yy)z.Get major and minor PostgreSQL server version.z,SELECT current_setting('server_version_num')r   current_settingzSELECT version()r2            r|   )r   r   patchfullr   r   Nr   )rW   r   re   r   rX   r>   )r   rZ   srv_verr   r   r   r   r   s           r   rB   zPgClusterInfo.get_pg_versionk  s   >//%(+,=>"ooe$Q'	2GAaL!GAaL!GAaL!A:88SZU45D 88SZUSZ@AD"&#
Y A:/4DLL#G,/3DLL#G, r   c                 L    | j                  d      d   d   | j                  d<   y)z'Get if the service is in recovery mode.zSELECT pg_is_in_recovery()r   pg_is_in_recoveryr3   N)rW   r>   r   s    r   rC   z PgClusterInfo.get_recovery_state  s&    &*oo6R&STU&VWj&k]#r   c           
      @   d}| j                  |      }i }|D ]2  }t        |d   |d   |d   |d   |d   r|d   nd|d   	      ||d
   <   4 t        | j                  j                        dk\  r| j                         }|D ]  }| j                  j                  |      | _        | j                  !i ||   d<   i ||   d<   i ||   d<   d||   d<   P| j                         ||   d<   | j                         ||   d<   | j                         ||   d<   t        | j                  j                        dk\  s| j                         ||   d<   j                  |i       ||   d<    || j                  d<   | j                  j                  | j                        | _        y)z+Get information about the current database.a  SELECT d.datname, pg_catalog.pg_get_userbyid(d.datdba) AS username, pg_catalog.pg_encoding_to_char(d.encoding) AS encoding, d.datcollate, d.datctype, pg_catalog.array_to_string(d.datacl, E'
') aclstring, CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname)::text ELSE 'No Access' END as dbsize, t.spcname FROM pg_catalog.pg_database AS d JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid WHERE d.datname != 'template0'usernameencoding
datcollatedatctype	aclstringrp   dbsize)ownerr   collatectypeaccess_privsizedatnamei N
namespaces
extensions	languagesz"Could not connect to the database.errorr]   subscriptionsr5   )rW   rX   r   r   
connectionrk   r;   r*   r   r   r   ra   rY   r>   r=   )r   rZ   ru   db_dictrP   rj   r   s          r   rE   zPgClusterInfo.get_db_info  s   2 ooe$ 	A$(
m:,
m./nAkN"x[%GAiL!	 dkk445?..0K 	QG++//8DK{{"13 .13 .02 -,P )-1-@-@-BGG\*-1->->-@GG\*,0,>,>,@GG[)!$++"8"89VC373D3D3F 04?OOGR4P 1	Q  %,[!kk++DOO<r   c                 6    | j                  d|z        d   |   S )z0Get setting's value represented by SHOW command.z	SHOW "%s"r   rW   )r   r   s     r   __get_pretty_valzPgClusterInfo.__get_pretty_val  s     {W45a8AAr   c           	      0   	 | j                   j                  |       | j                   j                         }|r|S 	 y# t        $ rO}| j                  j                  d|dt        |             | j                   j                          Y d}~yd}~ww xY w)z"Execute SQL and return the result.zCannot execute SQL 'z': )msgNF)r   executefetchall	Exceptionr   	fail_jsonr   r(   )r   rZ   ru   rS   s       r   
__exec_sqlzPgClusterInfo.__exec_sql  s    	 KK&++&&(C
 
   	 KK!!uiXYl&[!\KK	 s   8= 	BABBc                 0    | j                  d      d   d   S )zGet current DBzSELECT current_database() AS dbr   r%   r   r   s    r   __get_current_dbzPgClusterInfo.__get_current_db  s    
 @A!DTJJr   N)F)r+   r,   r-   r.   r   rT   ra   rk   rD   r   rI   rG   rH   rF   r   r   rB   rC   rE   r   rW   r<   r   r   r   r0   r0      sj    
"6p4$L.<,\).08@,D1>4<l4=lB
Kr   r0   c            
         t               } | j                  t        ddgddddg      t        dd      t        d	      t        d
d             t        | d      }|j                  d   }|j                  d   st        ||j                  d          t        |      }t        ||      } |j                  di |j                  |       y )Nr   r%   z4.0.0zcommunity.postgresql)r   r2   collection_name)typealiasesdeprecated_aliaseslist)r   elements)r   boolT)r   default)r'   filtersession_roletrust_input)argument_specsupports_check_moder   r   r   r   )
r   updaterX   r   r    r
   r   r0   	exit_jsonrT   )r  r   filter_r?   r>   s        r   mainr    s    13M54&"#9F 
 %0u%fd3   # F
 mmH%G=='FFMM.9:6"K FK0GF0ww/0r   __main__)
__future__r   r   r   r   __metaclass__DOCUMENTATIONEXAMPLESRETURNr   r   ansible.module_utils._textr   ansible.module_utils.basicr   ansible.module_utils.sixr	   Fansible_collections.community.postgresql.plugins.module_utils.databaser
   Fansible_collections.community.postgresql.plugins.module_utils.postgresr   r   r   r   r   r   objectr   r0   r  r+   r   r   r   <module>r     s    A @5n*X,
\ 
  0 4 . (0v (0V{KF {KD1B zF r   