#!/usr/bin/python
# -*- coding: utf-8 -*-

# Copyright: Ansible Project
# Copyright: (c) 2019, Tobias Birkefeld (@tcraxs) <t@craxs.de>
# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)

from __future__ import absolute_import, division, print_function
__metaclass__ = type

DOCUMENTATION = r'''
---
module: postgresql_privs
short_description: Grant or revoke privileges on PostgreSQL database objects
description:
- Grant or revoke privileges on PostgreSQL database objects.
- This module is basically a wrapper around most of the functionality of
  PostgreSQL's GRANT and REVOKE statements with detection of changes
  (GRANT/REVOKE I(privs) ON I(type) I(objs) TO/FROM I(roles)).
- B(WARNING) The C(usage_on_types) option has been B(deprecated) and will be removed in
  community.postgresql 3.0.0, please use the C(type) option with value C(type) to
  GRANT/REVOKE permissions on types explicitly.
options:
  database:
    description:
    - Name of database to connect to.
    required: true
    type: str
    aliases:
    - db
    - login_db
  state:
    description:
    - If C(present), the specified privileges are granted, if C(absent) they are revoked.
    type: str
    default: present
    choices: [ absent, present ]
  privs:
    description:
    - Comma separated list of privileges to grant/revoke.
    type: str
    aliases:
    - priv
  type:
    description:
    - Type of database object to set privileges on.
    - The C(default_privs) choice is available starting at version 2.7.
    - The C(foreign_data_wrapper) and C(foreign_server) object types are available since Ansible version 2.8.
    - The C(type) choice is available since Ansible version 2.10.
    - The C(procedure) is supported since collection version 1.3.0 and PostgreSQL 11.
    type: str
    default: table
    choices: [ database, default_privs, foreign_data_wrapper, foreign_server, function,
               group, language, table, tablespace, schema, sequence, type , procedure]
  objs:
    description:
    - Comma separated list of database objects to set privileges on.
    - If I(type) is C(table), C(partition table), C(sequence), C(function) or C(procedure),
      the special value C(ALL_IN_SCHEMA) can be provided instead to specify all
      database objects of I(type) in the schema specified via I(schema).
      (This also works with PostgreSQL < 9.0.) (C(ALL_IN_SCHEMA) is available
       for C(function) and C(partition table) since Ansible 2.8).
    - C(procedure) is supported since PostgreSQL 11 and community.postgresql collection 1.3.0.
    - If I(type) is C(database), this parameter can be omitted, in which case
      privileges are set for the database specified via I(database).
    - If I(type) is C(function) or C(procedure), colons (":") in object names will be
      replaced with commas (needed to specify signatures, see examples).
    type: str
    aliases:
    - obj
  schema:
    description:
    - Schema that contains the database objects specified via I(objs).
    - May only be provided if I(type) is C(table), C(sequence), C(function), C(procedure), C(type),
      or C(default_privs). Defaults to C(public) in these cases.
    - Pay attention, for embedded types when I(type=type)
      I(schema) can be C(pg_catalog) or C(information_schema) respectively.
    - If not specified, uses C(public). Not to pass any schema, use C(not-specified).
    type: str
  roles:
    description:
    - Comma separated list of role (user/group) names to set permissions for.
    - The special value C(PUBLIC) can be provided instead to set permissions
      for the implicitly defined PUBLIC group.
    type: str
    required: true
    aliases:
    - role
  fail_on_role:
    description:
    - If C(true), fail when target role (for whom privs need to be granted) does not exist.
      Otherwise just warn and continue.
    default: true
    type: bool
  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
  target_roles:
    description:
    - A list of existing role (user/group) names to set as the
      default permissions for database objects subsequently created by them.
    - Parameter I(target_roles) is only available with C(type=default_privs).
    type: str
  grant_option:
    description:
    - Whether C(role) may grant/revoke the specified privileges/group memberships to others.
    - Set to C(false) to revoke GRANT OPTION, leave unspecified to make no changes.
    - I(grant_option) only has an effect if I(state) is C(present).
    type: bool
    aliases:
    - admin_option
  password:
    description:
    - The password to authenticate with.
    - This option has been B(deprecated) and will be removed in community.postgresql 4.0.0,
      use the I(login_password) option instead.
    - Mutually exclusive with I(login_password).
    type: str
    default: ''
  trust_input:
    description:
    - If C(false), check whether values of parameters I(roles), I(target_roles), I(session_role),
      I(schema) 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'
  usage_on_types:
    description:
    - This option has been B(deprecated) and will be removed in community.postgresql 3.0.0,
      please use the I(type) option with value C(type) to GRANT/REVOKE permissions on types
      explicitly.
    - When adding default privileges, the module always implicitly adds ``USAGE ON TYPES``.
    - To avoid this behavior, set I(usage_on_types) to C(false).
    - Added to save backwards compatibility.
    - Used only when adding default privileges, ignored otherwise.
    type: bool
    default: true
    version_added: '1.2.0'

notes:
- Parameters that accept comma separated lists (I(privs), I(objs), I(roles))
  have singular alias names (I(priv), I(obj), I(role)).
- To revoke only C(GRANT OPTION) for a specific object, set I(state) to
  C(present) and I(grant_option) to C(false) (see examples).
- Note that when revoking privileges from a role R, this role  may still have
  access via privileges granted to any role R is a member of including C(PUBLIC).
- Note that when you use C(PUBLIC) role, the module always reports that the state has been changed.
- Note that when revoking privileges from a role R, you do so as the user
  specified via I(login_user). If R has been granted the same privileges by
  another user also, R can still access database objects via these privileges.
- When revoking privileges, C(RESTRICT) is assumed (see PostgreSQL docs).

seealso:
- module: community.postgresql.postgresql_user
- module: community.postgresql.postgresql_owner
- module: community.postgresql.postgresql_membership
- name: PostgreSQL privileges
  description: General information about PostgreSQL privileges.
  link: https://www.postgresql.org/docs/current/ddl-priv.html
- name: PostgreSQL GRANT command reference
  description: Complete reference of the PostgreSQL GRANT command documentation.
  link: https://www.postgresql.org/docs/current/sql-grant.html
- name: PostgreSQL REVOKE command reference
  description: Complete reference of the PostgreSQL REVOKE command documentation.
  link: https://www.postgresql.org/docs/current/sql-revoke.html

attributes:
  check_mode:
    support: full

extends_documentation_fragment:
- community.postgresql.postgres

author:
- Bernhard Weitzhofer (@b6d)
- Tobias Birkefeld (@tcraxs)
'''

EXAMPLES = r'''
# On database "library":
# GRANT SELECT, INSERT, UPDATE ON TABLE public.books, public.authors
# TO librarian, reader WITH GRANT OPTION
- name: Grant privs to librarian and reader on database library
  community.postgresql.postgresql_privs:
    database: library
    state: present
    privs: SELECT,INSERT,UPDATE
    type: table
    objs: books,authors
    schema: public
    roles: librarian,reader
    grant_option: true

- name: Same as above leveraging default values
  community.postgresql.postgresql_privs:
    db: library
    privs: SELECT,INSERT,UPDATE
    objs: books,authors
    roles: librarian,reader
    grant_option: true

# REVOKE GRANT OPTION FOR INSERT ON TABLE books FROM reader
# Note that role "reader" will be *granted* INSERT privilege itself if this
# isn't already the case (since state: present).
- name: Revoke privs from reader
  community.postgresql.postgresql_privs:
    db: library
    state: present
    priv: INSERT
    obj: books
    role: reader
    grant_option: false

# "public" is the default schema. This also works for PostgreSQL 8.x.
- name: REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM reader
  community.postgresql.postgresql_privs:
    db: library
    state: absent
    privs: INSERT,UPDATE
    objs: ALL_IN_SCHEMA
    role: reader

- name: GRANT ALL PRIVILEGES ON SCHEMA public, math TO librarian
  community.postgresql.postgresql_privs:
    db: library
    privs: ALL
    type: schema
    objs: public,math
    role: librarian

# Note the separation of arguments with colons.
- name: GRANT ALL PRIVILEGES ON FUNCTION math.add(int, int) TO librarian, reader
  community.postgresql.postgresql_privs:
    db: library
    privs: ALL
    type: function
    obj: add(int:int)
    schema: math
    roles: librarian,reader

# Note that group role memberships apply cluster-wide and therefore are not
# restricted to database "library" here.
- name: GRANT librarian, reader TO alice, bob WITH ADMIN OPTION
  community.postgresql.postgresql_privs:
    db: library
    type: group
    objs: librarian,reader
    roles: alice,bob
    admin_option: true

# Note that here "db: postgres" specifies the database to connect to, not the
# database to grant privileges on (which is specified via the "objs" param)
- name: GRANT ALL PRIVILEGES ON DATABASE library TO librarian
  community.postgresql.postgresql_privs:
    db: postgres
    privs: ALL
    type: database
    obj: library
    role: librarian

# If objs is omitted for type "database", it defaults to the database
# to which the connection is established
- name: GRANT ALL PRIVILEGES ON DATABASE library TO librarian
  community.postgresql.postgresql_privs:
    db: library
    privs: ALL
    type: database
    role: librarian

# Available since version 2.7
# Objs must be set, ALL_DEFAULT to TABLES/SEQUENCES/TYPES/FUNCTIONS
# ALL_DEFAULT works only with privs=ALL
# For specific
- name: ALTER DEFAULT PRIVILEGES ON DATABASE library TO librarian
  community.postgresql.postgresql_privs:
    db: library
    objs: ALL_DEFAULT
    privs: ALL
    type: default_privs
    role: librarian
    grant_option: true

# Available since version 2.7
# Objs must be set, ALL_DEFAULT to TABLES/SEQUENCES/TYPES/FUNCTIONS
# ALL_DEFAULT works only with privs=ALL
# For specific
- name: ALTER DEFAULT PRIVILEGES ON DATABASE library TO reader, step 1
  community.postgresql.postgresql_privs:
    db: library
    objs: TABLES,SEQUENCES
    privs: SELECT
    type: default_privs
    role: reader

- name: ALTER DEFAULT PRIVILEGES ON DATABASE library TO reader, step 2
  community.postgresql.postgresql_privs:
    db: library
    objs: TYPES
    privs: USAGE
    type: default_privs
    role: reader

# Available since version 2.8
- name: GRANT ALL PRIVILEGES ON FOREIGN DATA WRAPPER fdw TO reader
  community.postgresql.postgresql_privs:
    db: test
    objs: fdw
    privs: ALL
    type: foreign_data_wrapper
    role: reader

# Available since community.postgresql 0.2.0
- name: GRANT ALL PRIVILEGES ON TYPE customtype TO reader
  community.postgresql.postgresql_privs:
    db: test
    objs: customtype
    privs: ALL
    type: type
    role: reader

# Available since version 2.8
- name: GRANT ALL PRIVILEGES ON FOREIGN SERVER fdw_server TO reader
  community.postgresql.postgresql_privs:
    db: test
    objs: fdw_server
    privs: ALL
    type: foreign_server
    role: reader

# Available since version 2.8
# Grant 'execute' permissions on all functions in schema 'common' to role 'caller'
- name: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA common TO caller
  community.postgresql.postgresql_privs:
    type: function
    state: present
    privs: EXECUTE
    roles: caller
    objs: ALL_IN_SCHEMA
    schema: common

# Available since collection version 1.3.0
# Grant 'execute' permissions on all procedures in schema 'common' to role 'caller'
# Needs PostreSQL 11 or higher and community.postgresql 1.3.0 or higher
- name: GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA common TO caller
  community.postgresql.postgresql_privs:
    type: procedure
    state: present
    privs: EXECUTE
    roles: caller
    objs: ALL_IN_SCHEMA
    schema: common

# Available since version 2.8
# ALTER DEFAULT PRIVILEGES FOR ROLE librarian IN SCHEMA library GRANT SELECT ON TABLES TO reader
# GRANT SELECT privileges for new TABLES objects created by librarian as
# default to the role reader.
# For specific
- name: ALTER privs
  community.postgresql.postgresql_privs:
    db: library
    schema: library
    objs: TABLES
    privs: SELECT
    type: default_privs
    role: reader
    target_roles: librarian

# Available since version 2.8
# ALTER DEFAULT PRIVILEGES FOR ROLE librarian IN SCHEMA library REVOKE SELECT ON TABLES FROM reader
# REVOKE SELECT privileges for new TABLES objects created by librarian as
# default from the role reader.
# For specific
- name: ALTER privs
  community.postgresql.postgresql_privs:
    db: library
    state: absent
    schema: library
    objs: TABLES
    privs: SELECT
    type: default_privs
    role: reader
    target_roles: librarian

# Available since community.postgresql 0.2.0
- name: Grant type privileges for pg_catalog.numeric type to alice
  community.postgresql.postgresql_privs:
    type: type
    roles: alice
    privs: ALL
    objs: numeric
    schema: pg_catalog
    db: acme

- name: Alter default privileges grant usage on schemas to datascience
  community.postgresql.postgresql_privs:
    database: test
    type: default_privs
    privs: usage
    objs: schemas
    role: datascience
'''

RETURN = r'''
queries:
  description: List of executed queries.
  returned: always
  type: list
  sample: ['REVOKE GRANT OPTION FOR INSERT ON TABLE "books" FROM "reader";']
'''

import traceback

PSYCOPG2_IMP_ERR = None
try:
    import psycopg2
    import psycopg2.extensions
except ImportError:
    PSYCOPG2_IMP_ERR = traceback.format_exc()
    psycopg2 = None

# import module snippets
from ansible.module_utils.basic import AnsibleModule, missing_required_lib
from ansible_collections.community.postgresql.plugins.module_utils.database import (
    pg_quote_identifier,
    check_input,
)
from ansible_collections.community.postgresql.plugins.module_utils.postgres import postgres_common_argument_spec, get_conn_params
from ansible.module_utils._text import to_native

VALID_PRIVS = frozenset(('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE',
                         'REFERENCES', 'TRIGGER', 'CREATE', 'CONNECT',
                         'TEMPORARY', 'TEMP', 'EXECUTE', 'USAGE', 'ALL'))
VALID_DEFAULT_OBJS = {'TABLES': ('ALL', 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'),
                      'SEQUENCES': ('ALL', 'SELECT', 'UPDATE', 'USAGE'),
                      'FUNCTIONS': ('ALL', 'EXECUTE'),
                      'TYPES': ('ALL', 'USAGE'),
                      'SCHEMAS': ('CREATE', 'USAGE'), }

executed_queries = []


class Error(Exception):
    pass


def role_exists(module, cursor, rolname):
    """Check user exists or not"""
    query = "SELECT 1 FROM pg_roles WHERE rolname = '%s'" % rolname
    try:
        cursor.execute(query)
        return cursor.rowcount > 0

    except Exception as e:
        module.fail_json(msg="Cannot execute SQL '%s': %s" % (query, to_native(e)))

    return False


# We don't have functools.partial in Python < 2.5
def partial(f, *args, **kwargs):
    """Partial function application"""

    def g(*g_args, **g_kwargs):
        new_kwargs = kwargs.copy()
        new_kwargs.update(g_kwargs)
        return f(*(args + g_args), **g_kwargs)

    g.f = f
    g.args = args
    g.kwargs = kwargs
    return g


class Connection(object):
    """Wrapper around a psycopg2 connection with some convenience methods"""

    def __init__(self, params, module):
        self.database = params.database
        self.module = module

        conn_params = get_conn_params(module, params.__dict__, warn_db_default=False)

        sslrootcert = params.ca_cert
        if psycopg2.__version__ < '2.4.3' and sslrootcert is not None:
            raise ValueError('psycopg2 must be at least 2.4.3 in order to user the ca_cert parameter')

        self.connection = psycopg2.connect(**conn_params)
        self.cursor = self.connection.cursor()
        self.pg_version = self.connection.server_version

    def commit(self):
        self.connection.commit()

    def rollback(self):
        self.connection.rollback()

    @property
    def encoding(self):
        """Connection encoding in Python-compatible form"""
        return psycopg2.extensions.encodings[self.connection.encoding]

    # Methods for querying database objects

    # PostgreSQL < 9.0 doesn't support "ALL TABLES IN SCHEMA schema"-like
    # phrases in GRANT or REVOKE statements, therefore alternative methods are
    # provided here.

    def schema_exists(self, schema):
        query = """SELECT count(*)
                   FROM pg_catalog.pg_namespace WHERE nspname = %s"""
        self.cursor.execute(query, (schema,))
        return self.cursor.fetchone()[0] > 0

    def get_all_tables_in_schema(self, schema):
        if schema:
            if not self.schema_exists(schema):
                raise Error('Schema "%s" does not exist.' % schema)

            query = """SELECT relname
                       FROM pg_catalog.pg_class c
                       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                       WHERE nspname = %s AND relkind in ('r', 'v', 'm', 'p')"""
            self.cursor.execute(query, (schema,))
        else:
            query = ("SELECT relname FROM pg_catalog.pg_class "
                     "WHERE relkind in ('r', 'v', 'm', 'p')")
            self.cursor.execute(query)
        return [t[0] for t in self.cursor.fetchall()]

    def get_all_sequences_in_schema(self, schema):
        if schema:
            if not self.schema_exists(schema):
                raise Error('Schema "%s" does not exist.' % schema)
            query = """SELECT relname
                       FROM pg_catalog.pg_class c
                       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                       WHERE nspname = %s AND relkind = 'S'"""
            self.cursor.execute(query, (schema,))
        else:
            self.cursor.execute("SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'S'")
        return [t[0] for t in self.cursor.fetchall()]

    def get_all_functions_in_schema(self, schema):
        if schema:
            if not self.schema_exists(schema):
                raise Error('Schema "%s" does not exist.' % schema)

            query = ("SELECT p.proname, oidvectortypes(p.proargtypes) "
                     "FROM pg_catalog.pg_proc p "
                     "JOIN pg_namespace n ON n.oid = p.pronamespace "
                     "WHERE nspname = %s")

            if self.pg_version >= 110000:
                query += " and p.prokind = 'f'"

            self.cursor.execute(query, (schema,))
        else:
            self.cursor.execute("SELECT p.proname, oidvectortypes(p.proargtypes) FROM pg_catalog.pg_proc p")
        return ["%s(%s)" % (t[0], t[1]) for t in self.cursor.fetchall()]

    def get_all_procedures_in_schema(self, schema):
        if self.pg_version < 110000:
            raise Error("PostgreSQL verion must be >= 11 for type=procedure. Exit")

        if schema:
            if not self.schema_exists(schema):
                raise Error('Schema "%s" does not exist.' % schema)

            query = ("SELECT p.proname, oidvectortypes(p.proargtypes) "
                     "FROM pg_catalog.pg_proc p "
                     "JOIN pg_namespace n ON n.oid = p.pronamespace "
                     "WHERE nspname = %s and p.prokind = 'p'")

            self.cursor.execute(query, (schema,))
        else:
            query = ("SELECT p.proname, oidvectortypes(p.proargtypes) "
                     "FROM pg_catalog.pg_proc p WHERE p.prokind = 'p'")
            self.cursor.execute(query)
        return ["%s(%s)" % (t[0], t[1]) for t in self.cursor.fetchall()]

    # Methods for getting access control lists and group membership info

    # To determine whether anything has changed after granting/revoking
    # privileges, we compare the access control lists of the specified database
    # objects before and afterwards. Python's list/string comparison should
    # suffice for change detection, we should not actually have to parse ACLs.
    # The same should apply to group membership information.

    def get_table_acls(self, schema, tables):
        if schema:
            query = """SELECT relacl
                       FROM pg_catalog.pg_class c
                       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                       WHERE nspname = %s AND relkind in ('r','p','v','m') AND relname = ANY (%s)
                       ORDER BY relname"""
            self.cursor.execute(query, (schema, tables))
        else:
            query = ("SELECT relacl FROM pg_catalog.pg_class "
                     "WHERE relkind in ('r','p','v','m') AND relname = ANY (%s) "
                     "ORDER BY relname")
            self.cursor.execute(query)
        return [t[0] for t in self.cursor.fetchall()]

    def get_sequence_acls(self, schema, sequences):
        if schema:
            query = """SELECT relacl
                       FROM pg_catalog.pg_class c
                       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                       WHERE nspname = %s AND relkind = 'S' AND relname = ANY (%s)
                       ORDER BY relname"""
            self.cursor.execute(query, (schema, sequences))
        else:
            query = ("SELECT relacl FROM pg_catalog.pg_class "
                     "WHERE  relkind = 'S' AND relname = ANY (%s) ORDER BY relname")
            self.cursor.execute(query)
        return [t[0] for t in self.cursor.fetchall()]

    def get_function_acls(self, schema, function_signatures):
        funcnames = [f.split('(', 1)[0] for f in function_signatures]
        if schema:
            query = """SELECT proacl
                       FROM pg_catalog.pg_proc p
                       JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                       WHERE nspname = %s AND proname = ANY (%s)
                       ORDER BY proname, proargtypes"""
            self.cursor.execute(query, (schema, funcnames))
        else:
            query = ("SELECT proacl FROM pg_catalog.pg_proc WHERE proname = ANY (%s) "
                     "ORDER BY proname, proargtypes")
            self.cursor.execute(query)
        return [t[0] for t in self.cursor.fetchall()]

    def get_schema_acls(self, schemas):
        query = """SELECT nspacl FROM pg_catalog.pg_namespace
                   WHERE nspname = ANY (%s) ORDER BY nspname"""
        self.cursor.execute(query, (schemas,))
        return [t[0] for t in self.cursor.fetchall()]

    def get_language_acls(self, languages):
        query = """SELECT lanacl FROM pg_catalog.pg_language
                   WHERE lanname = ANY (%s) ORDER BY lanname"""
        self.cursor.execute(query, (languages,))
        return [t[0] for t in self.cursor.fetchall()]

    def get_tablespace_acls(self, tablespaces):
        query = """SELECT spcacl FROM pg_catalog.pg_tablespace
                   WHERE spcname = ANY (%s) ORDER BY spcname"""
        self.cursor.execute(query, (tablespaces,))
        return [t[0] for t in self.cursor.fetchall()]

    def get_database_acls(self, databases):
        query = """SELECT datacl FROM pg_catalog.pg_database
                   WHERE datname = ANY (%s) ORDER BY datname"""
        self.cursor.execute(query, (databases,))
        return [t[0] for t in self.cursor.fetchall()]

    def get_group_memberships(self, groups):
        query = """SELECT roleid, grantor, member, admin_option
                   FROM pg_catalog.pg_auth_members am
                   JOIN pg_catalog.pg_roles r ON r.oid = am.roleid
                   WHERE r.rolname = ANY(%s)
                   ORDER BY roleid, grantor, member"""
        self.cursor.execute(query, (groups,))
        return self.cursor.fetchall()

    def get_default_privs(self, schema, *args):
        if schema:
            query = """SELECT defaclacl
                       FROM pg_default_acl a
                       JOIN pg_namespace b ON a.defaclnamespace=b.oid
                       WHERE b.nspname = %s;"""
            self.cursor.execute(query, (schema,))
        else:
            self.cursor.execute("SELECT defaclacl FROM pg_default_acl;")
        return [t[0] for t in self.cursor.fetchall()]

    def get_foreign_data_wrapper_acls(self, fdws):
        query = """SELECT fdwacl FROM pg_catalog.pg_foreign_data_wrapper
                   WHERE fdwname = ANY (%s) ORDER BY fdwname"""
        self.cursor.execute(query, (fdws,))
        return [t[0] for t in self.cursor.fetchall()]

    def get_foreign_server_acls(self, fs):
        query = """SELECT srvacl FROM pg_catalog.pg_foreign_server
                   WHERE srvname = ANY (%s) ORDER BY srvname"""
        self.cursor.execute(query, (fs,))
        return [t[0] for t in self.cursor.fetchall()]

    def get_type_acls(self, schema, types):
        if schema:
            query = """SELECT t.typacl FROM pg_catalog.pg_type t
                       JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
                       WHERE n.nspname = %s AND t.typname = ANY (%s) ORDER BY typname"""
            self.cursor.execute(query, (schema, types))
        else:
            query = "SELECT typacl FROM pg_catalog.pg_type WHERE typname = ANY (%s) ORDER BY typname"
            self.cursor.execute(query)
        return [t[0] for t in self.cursor.fetchall()]

    # Manipulating privileges

    # WARNING: usage_on_types has been deprecated and will be removed in community.postgresql 3.0.0, please use an obj_type of 'type' instead.
    def manipulate_privs(self, obj_type, privs, objs, orig_objs, roles, target_roles,
                         state, grant_option, schema_qualifier=None, fail_on_role=True, usage_on_types=True):
        """Manipulate database object privileges.

        :param obj_type: Type of database object to grant/revoke
                         privileges for.
        :param privs: Either a list of privileges to grant/revoke
                      or None if type is "group".
        :param objs: List of database objects to grant/revoke
                     privileges for.
        :param orig_objs: ALL_IN_SCHEMA or None
        :param roles: Either a list of role names or "PUBLIC"
                      for the implicitly defined "PUBLIC" group
        :param target_roles: List of role names to grant/revoke
                             default privileges as.
        :param state: "present" to grant privileges, "absent" to revoke.
        :param grant_option: Only for state "present": If True, set
                             grant/admin option. If False, revoke it.
                             If None, don't change grant option.
        :param schema_qualifier: Some object types ("TABLE", "SEQUENCE",
                                 "FUNCTION") must be qualified by schema.
                                 Ignored for other Types.
        """
        # get_status: function to get current status
        if obj_type == 'table':
            get_status = partial(self.get_table_acls, schema_qualifier)
        elif obj_type == 'sequence':
            get_status = partial(self.get_sequence_acls, schema_qualifier)
        elif obj_type in ('function', 'procedure'):
            get_status = partial(self.get_function_acls, schema_qualifier)
        elif obj_type == 'schema':
            get_status = self.get_schema_acls
        elif obj_type == 'language':
            get_status = self.get_language_acls
        elif obj_type == 'tablespace':
            get_status = self.get_tablespace_acls
        elif obj_type == 'database':
            get_status = self.get_database_acls
        elif obj_type == 'group':
            get_status = self.get_group_memberships
        elif obj_type == 'default_privs':
            get_status = partial(self.get_default_privs, schema_qualifier)
        elif obj_type == 'foreign_data_wrapper':
            get_status = self.get_foreign_data_wrapper_acls
        elif obj_type == 'foreign_server':
            get_status = self.get_foreign_server_acls
        elif obj_type == 'type':
            get_status = partial(self.get_type_acls, schema_qualifier)
        else:
            raise Error('Unsupported database object type "%s".' % obj_type)

        # Return False (nothing has changed) if there are no objs to work on.
        if not objs:
            return False

        quoted_schema_qualifier = '"%s"' % schema_qualifier.replace('"', '""') if schema_qualifier else None
        # obj_ids: quoted db object identifiers (sometimes schema-qualified)
        if obj_type in ('function', 'procedure'):
            obj_ids = []
            for obj in objs:
                try:
                    f, args = obj.split('(', 1)
                except Exception:
                    raise Error('Illegal function / procedure signature: "%s".' % obj)
                obj_ids.append('%s."%s"(%s' % (quoted_schema_qualifier, f, args))
        elif obj_type in ['table', 'sequence', 'type']:
            obj_ids = ['%s."%s"' % (quoted_schema_qualifier, o) for o in objs]
        else:
            obj_ids = ['"%s"' % o for o in objs]

        # set_what: SQL-fragment specifying what to set for the target roles:
        # Either group membership or privileges on objects of a certain type
        if obj_type == 'group':
            set_what = ','.join(obj_ids)
        elif obj_type == 'default_privs':
            # We don't want privs to be quoted here
            set_what = ','.join(privs)
        else:
            # function types are already quoted above
            if obj_type not in ('function', 'procedure'):
                obj_ids = [pg_quote_identifier(i, 'table') for i in obj_ids]
            # Note: obj_type has been checked against a set of string literals
            # and privs was escaped when it was parsed
            # Note: Underscores are replaced with spaces to support multi-word obj_type
            if orig_objs is not None:
                set_what = '%s ON %s %s' % (','.join(privs), orig_objs, quoted_schema_qualifier)
            else:
                set_what = '%s ON %s %s' % (','.join(privs), obj_type.replace('_', ' '), ','.join(obj_ids))

        # for_whom: SQL-fragment specifying for whom to set the above
        if roles == 'PUBLIC':
            for_whom = 'PUBLIC'
        else:
            for_whom = []
            for r in roles:
                if not role_exists(self.module, self.cursor, r):
                    if fail_on_role:
                        self.module.fail_json(msg="Role '%s' does not exist" % r.strip())

                    else:
                        self.module.warn("Role '%s' does not exist, pass it" % r.strip())
                else:
                    for_whom.append('"%s"' % r)

            if not for_whom:
                return False

            for_whom = ','.join(for_whom)

        # as_who:
        as_who = None
        if target_roles:
            as_who = ','.join('"%s"' % r for r in target_roles)

        status_before = get_status(objs)

        query = QueryBuilder(state) \
            .for_objtype(obj_type) \
            .with_grant_option(grant_option) \
            .for_whom(for_whom) \
            .as_who(as_who) \
            .for_schema(quoted_schema_qualifier) \
            .set_what(set_what) \
            .for_objs(objs) \
            .usage_on_types(usage_on_types) \
            .build()

        executed_queries.append(query)
        self.cursor.execute(query)
        if roles == 'PUBLIC':
            return True

        status_after = get_status(objs)

        def nonesorted(e):
            # For python 3+ that can fail trying
            # to compare NoneType elements by sort method.
            if e is None:
                return ''
            return e

        status_before.sort(key=nonesorted)
        status_after.sort(key=nonesorted)
        return status_before != status_after


class QueryBuilder(object):
    def __init__(self, state):
        self._grant_option = None
        self._for_whom = None
        self._as_who = None
        self._set_what = None
        self._obj_type = None
        self._state = state
        self._schema = None
        self._objs = None
        self._usage_on_types = None
        self.query = []

    def for_objs(self, objs):
        self._objs = objs
        return self

    def for_schema(self, schema):
        self._schema = ' IN SCHEMA %s' % schema if schema is not None else ''
        return self

    def with_grant_option(self, option):
        self._grant_option = option
        return self

    def for_whom(self, who):
        self._for_whom = who
        return self

    def usage_on_types(self, usage_on_types):
        self._usage_on_types = usage_on_types
        return self

    def as_who(self, target_roles):
        self._as_who = target_roles
        return self

    def set_what(self, what):
        self._set_what = what
        return self

    def for_objtype(self, objtype):
        self._obj_type = objtype
        return self

    def build(self):
        if self._state == 'present':
            self.build_present()
        elif self._state == 'absent':
            self.build_absent()
        else:
            self.build_absent()
        return '\n'.join(self.query)

    def add_default_revoke(self):
        for obj in self._objs:
            if self._as_who:
                self.query.append(
                    'ALTER DEFAULT PRIVILEGES FOR ROLE {0}{1} REVOKE ALL ON {2} FROM {3};'.format(self._as_who,
                                                                                                  self._schema, obj,
                                                                                                  self._for_whom))
            else:
                self.query.append(
                    'ALTER DEFAULT PRIVILEGES{0} REVOKE ALL ON {1} FROM {2};'.format(self._schema, obj,
                                                                                     self._for_whom))

    def add_grant_option(self):
        if self._grant_option:
            if self._obj_type == 'group':
                self.query[-1] += ' WITH ADMIN OPTION;'
            else:
                self.query[-1] += ' WITH GRANT OPTION;'
        elif self._grant_option is False:
            self.query[-1] += ';'
            if self._obj_type == 'group':
                self.query.append('REVOKE ADMIN OPTION FOR {0} FROM {1};'.format(self._set_what, self._for_whom))
            elif not self._obj_type == 'default_privs':
                self.query.append('REVOKE GRANT OPTION FOR {0} FROM {1};'.format(self._set_what, self._for_whom))
        else:
            self.query[-1] += ';'

    def add_default_priv(self):
        for obj in self._objs:
            if self._as_who:
                self.query.append(
                    'ALTER DEFAULT PRIVILEGES FOR ROLE {0}{1} GRANT {2} ON {3} TO {4}'.format(self._as_who,
                                                                                              self._schema,
                                                                                              self._set_what,
                                                                                              obj,
                                                                                              self._for_whom))
            else:
                self.query.append(
                    'ALTER DEFAULT PRIVILEGES{0} GRANT {1} ON {2} TO {3}'.format(self._schema,
                                                                                 self._set_what,
                                                                                 obj,
                                                                                 self._for_whom))
            self.add_grant_option()

        if self._usage_on_types:

            if self._as_who:
                self.query.append(
                    'ALTER DEFAULT PRIVILEGES FOR ROLE {0}{1} GRANT USAGE ON TYPES TO {2}'.format(self._as_who,
                                                                                                  self._schema,
                                                                                                  self._for_whom))
            else:
                self.query.append(
                    'ALTER DEFAULT PRIVILEGES{0} GRANT USAGE ON TYPES TO {1}'.format(self._schema, self._for_whom))
        self.add_grant_option()

    def build_present(self):
        if self._obj_type == 'default_privs':
            self.add_default_revoke()
            self.add_default_priv()
        else:
            self.query.append('GRANT {0} TO {1}'.format(self._set_what, self._for_whom))
            self.add_grant_option()

    def build_absent(self):
        if self._obj_type == 'default_privs':
            self.query = []
            for obj in ['TABLES', 'SEQUENCES', 'TYPES']:
                if self._as_who:
                    self.query.append(
                        'ALTER DEFAULT PRIVILEGES FOR ROLE {0}{1} REVOKE ALL ON {2} FROM {3};'.format(self._as_who,
                                                                                                      self._schema, obj,
                                                                                                      self._for_whom))
                else:
                    self.query.append(
                        'ALTER DEFAULT PRIVILEGES{0} REVOKE ALL ON {1} FROM {2};'.format(self._schema, obj,
                                                                                         self._for_whom))
        else:
            self.query.append('REVOKE {0} FROM {1};'.format(self._set_what, self._for_whom))


def main():
    argument_spec = postgres_common_argument_spec()
    argument_spec.update(
        database=dict(required=True, aliases=['db', 'login_db']),
        state=dict(default='present', choices=['present', 'absent']),
        privs=dict(required=False, aliases=['priv']),
        type=dict(default='table',
                  choices=['table',
                           'sequence',
                           'function',
                           'procedure',
                           'database',
                           'schema',
                           'language',
                           'tablespace',
                           'group',
                           'default_privs',
                           'foreign_data_wrapper',
                           'foreign_server',
                           'type', ]),
        objs=dict(required=False, aliases=['obj']),
        schema=dict(required=False),
        roles=dict(required=True, aliases=['role']),
        session_role=dict(required=False),
        target_roles=dict(required=False),
        grant_option=dict(required=False, type='bool',
                          aliases=['admin_option']),
        # WARNING: password is deprecated and will  be removed in community.postgresql 4.0.0,
        # login_password should be used instead
        password=dict(default='', no_log=True,
                      removed_in_version='4.0.0',
                      removed_from_collection='community.postgreql'),
        fail_on_role=dict(type='bool', default=True),
        trust_input=dict(type='bool', default=True),
        usage_on_types=dict(type='bool', default=True,
                            removed_in_version='3.0.0',
                            removed_from_collection='community.postgresql'),
    )

    module = AnsibleModule(
        argument_spec=argument_spec,
        supports_check_mode=True,
    )

    fail_on_role = module.params['fail_on_role']
    usage_on_types = module.params['usage_on_types']

    # Create type object as namespace for module params
    p = type('Params', (), module.params)

    # WARNING: password is deprecated and will  be removed in community.postgresql 4.0.0,
    # login_password should be used instead
    # https://github.com/ansible-collections/community.postgresql/issues/406
    if p.password:
        if p.login_password:
            module.fail_json(msg='Use the "password" or "login_password" option but not both '
                                 'to pass a password to log in with.')
        p.login_password = p.password

    # param "schema": default, allowed depends on param "type"
    if p.type in ['table', 'sequence', 'function', 'procedure', 'type', 'default_privs']:
        if p.objs == 'schemas' or p.schema == 'not-specified':
            p.schema = None
        else:
            p.schema = p.schema or 'public'
    elif p.schema:
        module.fail_json(msg='Argument "schema" is not allowed '
                             'for type "%s".' % p.type)

    # param "objs": ALL_IN_SCHEMA can be used only
    # when param "type" is table, sequence, function or procedure
    if p.objs == 'ALL_IN_SCHEMA' and p.type not in ('table', 'sequence', 'function', 'procedure'):
        module.fail_json(msg='Argument "objs": ALL_IN_SCHEMA can be used only for '
                             'type: table, sequence, function or procedure, '
                             '%s was passed.' % p.type)

    # param "objs": default, required depends on param "type"
    if p.type == 'database':
        p.objs = p.objs or p.database
    elif not p.objs:
        module.fail_json(msg='Argument "objs" is required '
                             'for type "%s".' % p.type)

    # param "privs": allowed, required depends on param "type"
    if p.type == 'group':
        if p.privs:
            module.fail_json(msg='Argument "privs" is not allowed '
                                 'for type "group".')
    elif not p.privs:
        module.fail_json(msg='Argument "privs" is required '
                             'for type "%s".' % p.type)

    # Check input
    if not p.trust_input:
        # Check input for potentially dangerous elements:
        check_input(module, p.roles, p.target_roles, p.session_role, p.schema)

    # Connect to Database
    if not psycopg2:
        module.fail_json(msg=missing_required_lib('psycopg2'), exception=PSYCOPG2_IMP_ERR)
    try:
        conn = Connection(p, module)
    except psycopg2.Error as e:
        module.fail_json(msg='Could not connect to database: %s' % to_native(e), exception=traceback.format_exc())
    except TypeError as e:
        if 'sslrootcert' in e.args[0]:
            module.fail_json(msg='Postgresql server must be at least version 8.4 to support sslrootcert')
        module.fail_json(msg="unable to connect to database: %s" % to_native(e), exception=traceback.format_exc())
    except ValueError as e:
        # We raise this when the psycopg library is too old
        module.fail_json(msg=to_native(e))

    if p.session_role:
        try:
            conn.cursor.execute('SET ROLE "%s"' % p.session_role)
        except Exception as e:
            module.fail_json(msg="Could not switch to role %s: %s" % (p.session_role, to_native(e)), exception=traceback.format_exc())

    try:
        # privs
        if p.privs:
            privs = frozenset(pr.upper() for pr in p.privs.split(','))
            if not privs.issubset(VALID_PRIVS):
                module.fail_json(msg='Invalid privileges specified: %s' % privs.difference(VALID_PRIVS))
        else:
            privs = None
        # objs:
        orig_objs = None
        if p.objs == 'ALL_IN_SCHEMA':
            if p.type == 'table':
                objs = conn.get_all_tables_in_schema(p.schema)
            elif p.type == 'sequence':
                objs = conn.get_all_sequences_in_schema(p.schema)
            elif p.type == 'function':
                objs = conn.get_all_functions_in_schema(p.schema)
            elif p.type == 'procedure':
                objs = conn.get_all_procedures_in_schema(p.schema)

            if conn.pg_version >= 90000:
                if p.type == 'table':
                    orig_objs = 'ALL TABLES IN SCHEMA'
                elif p.type == 'sequence':
                    orig_objs = 'ALL SEQUENCES IN SCHEMA'
                elif p.type == 'function':
                    orig_objs = 'ALL FUNCTIONS IN SCHEMA'
                elif p.type == 'procedure':
                    orig_objs = 'ALL PROCEDURES IN SCHEMA'

        elif p.type == 'default_privs':
            if p.objs == 'ALL_DEFAULT':
                VALID_DEFAULT_OBJS.pop('SCHEMAS')
                objs = frozenset(VALID_DEFAULT_OBJS.keys())
            else:
                objs = frozenset(obj.upper() for obj in p.objs.split(','))
                if not objs.issubset(VALID_DEFAULT_OBJS):
                    module.fail_json(
                        msg='Invalid Object set specified: %s' % objs.difference(VALID_DEFAULT_OBJS.keys()))
            # Again, do we have valid privs specified for object type:
            valid_objects_for_priv = frozenset(obj for obj in objs if privs.issubset(VALID_DEFAULT_OBJS[obj]))
            if not valid_objects_for_priv == objs:
                module.fail_json(
                    msg='Invalid priv specified. Valid object for priv: {0}. Objects: {1}'.format(
                        valid_objects_for_priv, objs))
        else:
            objs = p.objs.split(',')

            # function signatures are encoded using ':' to separate args
            if p.type in ('function', 'procedure'):
                objs = [obj.replace(':', ',') for obj in objs]

        # roles
        if p.roles.upper() == 'PUBLIC':
            roles = 'PUBLIC'
        else:
            roles = p.roles.split(',')

            if len(roles) == 1 and not role_exists(module, conn.cursor, roles[0]):
                if fail_on_role:
                    module.fail_json(msg="Role '%s' does not exist" % roles[0].strip())
                else:
                    module.warn("Role '%s' does not exist, nothing to do" % roles[0].strip())
                module.exit_json(changed=False, queries=executed_queries)

        # check if target_roles is set with type: default_privs
        if p.target_roles and not p.type == 'default_privs':
            module.warn('"target_roles" will be ignored '
                        'Argument "type: default_privs" is required for usage of "target_roles".')

        # target roles
        if p.target_roles:
            target_roles = p.target_roles.split(',')
        else:
            target_roles = None

        changed = conn.manipulate_privs(
            obj_type=p.type,
            privs=privs,
            objs=objs,
            orig_objs=orig_objs,
            roles=roles,
            target_roles=target_roles,
            state=p.state,
            grant_option=p.grant_option,
            schema_qualifier=p.schema,
            fail_on_role=fail_on_role,
            usage_on_types=usage_on_types,
        )

    except Error as e:
        conn.rollback()
        module.fail_json(msg=to_native(e), exception=traceback.format_exc())

    except psycopg2.Error as e:
        conn.rollback()
        module.fail_json(msg=to_native(e))

    if module.check_mode or not changed:
        conn.rollback()
    else:
        conn.commit()

    conn.cursor.close()
    conn.connection.close()

    module.exit_json(changed=changed, queries=executed_queries)


if __name__ == '__main__':
    main()