salt.modules.postgres¶
Module to provide Postgres compatibility to salt.
- configuration:
In order to connect to Postgres, certain configuration is required in /etc/salt/minion on the relevant minions. Some sample configs might look like:
postgres.host: 'localhost' postgres.port: '5432' postgres.user: 'postgres' -> db user postgres.pass: '' postgres.maintenance_db: 'postgres'
The default for the maintenance_db is 'postgres' and in most cases it can be left at the default setting. This data can also be passed into pillar. Options passed into opts will overwrite options passed into pillar
To prevent Postgres commands from running arbitrarily long, a timeout (in seconds) can be set
postgres.timeout: 60New in version 3006.0.
- note:
This module uses MD5 hashing which may not be compliant with certain security audits.
- note:
When installing postgres from the official postgres repos, on certain linux distributions, either the psql or the initdb binary is not automatically placed on the path. Add a configuration to the location of the postgres bin's path to the relevant minion for this module:
postgres.bins_dir: '/usr/pgsql-9.5/bin/'
- salt.modules.postgres.available_extensions(user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
List available postgresql extensions
CLI Example:
salt '*' postgres.available_extensions
- salt.modules.postgres.create_extension(name, if_not_exists=None, schema=None, ext_version=None, from_version=None, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Install a postgresql extension
CLI Example:
salt '*' postgres.create_extension 'adminpack'
- salt.modules.postgres.create_metadata(name, ext_version=None, schema=None, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Get lifecycle information about an extension
CLI Example:
salt '*' postgres.create_metadata adminpack
- salt.modules.postgres.datadir_exists(name)¶
New in version 2016.3.0.
Checks if postgres data directory has been initialized
CLI Example:
salt '*' postgres.datadir_exists '/var/lib/pgsql/data'
- name
Name of the directory to check
- salt.modules.postgres.datadir_init(name, auth='password', user=None, password=None, encoding='UTF8', locale=None, waldir=None, checksums=False, runas=None)¶
New in version 2016.3.0.
Initializes a postgres data directory
CLI Example:
salt '*' postgres.datadir_init '/var/lib/pgsql/data'
- name
The name of the directory to initialize
- auth
The default authentication method for local connections
- password
The password to set for the postgres user
- user
The database superuser name
- encoding
The default encoding for new databases
- locale
The default locale for new databases
- waldir
The transaction log (WAL) directory (default is to keep WAL inside the data directory)
New in version 2019.2.0.
- checksums
If True, the cluster will be created with data page checksums.
Note
Data page checksums are supported since PostgreSQL 9.3.
New in version 2019.2.0.
- runas
The system user the operation should be performed on behalf of
- salt.modules.postgres.db_alter(name, user=None, host=None, port=None, maintenance_db=None, password=None, tablespace=None, owner=None, owner_recurse=False, runas=None)¶
Change tablespace or/and owner of database.
CLI Example:
salt '*' postgres.db_alter dbname owner=otheruser
- salt.modules.postgres.db_create(name, user=None, host=None, port=None, maintenance_db=None, password=None, tablespace=None, encoding=None, lc_collate=None, lc_ctype=None, owner=None, template=None, runas=None)¶
Adds a databases to the Postgres server.
CLI Example:
salt '*' postgres.db_create 'dbname' salt '*' postgres.db_create 'dbname' template=template_postgis
- salt.modules.postgres.db_exists(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Checks if a database exists on the Postgres server.
CLI Example:
salt '*' postgres.db_exists 'dbname'
- salt.modules.postgres.db_list(user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Return dictionary with information about databases of a Postgres server.
CLI Example:
salt '*' postgres.db_list
- salt.modules.postgres.db_remove(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Removes a databases from the Postgres server.
CLI Example:
salt '*' postgres.db_remove 'dbname'
- salt.modules.postgres.drop_extension(name, if_exists=None, restrict=None, cascade=None, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Drop an installed postgresql extension
CLI Example:
salt '*' postgres.drop_extension 'adminpack'
- salt.modules.postgres.get_available_extension(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Get info about an available postgresql extension
CLI Example:
salt '*' postgres.get_available_extension plpgsql
- salt.modules.postgres.get_installed_extension(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Get info about an installed postgresql extension
CLI Example:
salt '*' postgres.get_installed_extension plpgsql
- salt.modules.postgres.group_create(groupname, user=None, host=None, port=None, maintenance_db=None, password=None, createdb=None, createroles=None, encrypted=None, login=None, inherit=None, superuser=None, replication=None, rolepassword=None, groups=None, runas=None)¶
Creates a Postgres group. A group is postgres is similar to a user, but cannot login.
CLI Example:
salt '*' postgres.group_create 'groupname' user='user' \ host='hostname' port='port' password='password' \ rolepassword='rolepassword'
- salt.modules.postgres.group_remove(groupname, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Removes a group from the Postgres server.
CLI Example:
salt '*' postgres.group_remove 'groupname'
- salt.modules.postgres.group_update(groupname, user=None, host=None, port=None, maintenance_db=None, password=None, createdb=None, createroles=None, encrypted=None, inherit=None, login=None, superuser=None, replication=None, rolepassword=None, groups=None, runas=None)¶
Updates a postgres group
CLI Examples:
salt '*' postgres.group_update 'username' user='user' \ host='hostname' port='port' password='password' \ rolepassword='rolepassword'
- salt.modules.postgres.has_privileges(name, object_name, object_type, privileges=None, grant_option=None, prepend='public', maintenance_db=None, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Check if a role has the specified privileges on an object
CLI Example:
salt '*' postgres.has_privileges user_name table_name table \ SELECT,INSERT maintenance_db=db_name
- name
Name of the role whose privileges should be checked on object_type
- object_name
Name of the object on which the check is to be performed
- object_type
The object type, which can be one of the following:
table
sequence
schema
tablespace
language
database
group
function
- privileges
Comma separated list of privileges to check, from the list below:
INSERT
CREATE
TRUNCATE
CONNECT
TRIGGER
SELECT
USAGE
TEMPORARY
UPDATE
EXECUTE
REFERENCES
DELETE
ALL
- grant_option
If grant_option is set to True, the grant option check is performed
- prepend
Table and Sequence object types live under a schema so this should be provided if the object is not under the default public schema
- maintenance_db
The database to connect to
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.installed_extensions(user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
List installed postgresql extensions
CLI Example:
salt '*' postgres.installed_extensions
- salt.modules.postgres.is_available_extension(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Test if a specific extension is available
CLI Example:
salt '*' postgres.is_available_extension
- salt.modules.postgres.is_installed_extension(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Test if a specific extension is installed
CLI Example:
salt '*' postgres.is_installed_extension
- salt.modules.postgres.language_create(name, maintenance_db, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Installs a language into a database
CLI Example:
salt '*' postgres.language_create plpgsql dbname
- name
Language to install
- maintenance_db
The database to install the language in
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.language_exists(name, maintenance_db, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Checks if language exists in a database.
CLI Example:
salt '*' postgres.language_exists plpgsql dbname
- name
Language to check for
- maintenance_db
The database to check in
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.language_list(maintenance_db, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Return a list of languages in a database.
CLI Example:
salt '*' postgres.language_list dbname
- maintenance_db
The database to check
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.language_remove(name, maintenance_db, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Removes a language from a database
CLI Example:
salt '*' postgres.language_remove plpgsql dbname
- name
Language to remove
- maintenance_db
The database to install the language in
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.owner_to(dbname, ownername, user=None, host=None, port=None, password=None, runas=None)¶
Set the owner of all schemas, functions, tables, views and sequences to the given username.
CLI Example:
salt '*' postgres.owner_to 'dbname' 'username'
- salt.modules.postgres.privileges_grant(name, object_name, object_type, privileges=None, grant_option=None, prepend='public', maintenance_db=None, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Grant privileges on a postgres object
CLI Example:
salt '*' postgres.privileges_grant user_name table_name table \ SELECT,UPDATE maintenance_db=db_name
- name
Name of the role to which privileges should be granted
- object_name
Name of the object on which the grant is to be performed
- object_type
The object type, which can be one of the following:
table
sequence
schema
tablespace
language
database
group
function
- privileges
Comma separated list of privileges to grant, from the list below:
INSERT
CREATE
TRUNCATE
CONNECT
TRIGGER
SELECT
USAGE
TEMPORARY
UPDATE
EXECUTE
REFERENCES
DELETE
ALL
- grant_option
If grant_option is set to True, the recipient of the privilege can in turn grant it to others
- prepend
Table and Sequence object types live under a schema so this should be provided if the object is not under the default public schema
- maintenance_db
The database to connect to
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.privileges_list(name, object_type, prepend='public', maintenance_db=None, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Return a list of privileges for the specified object.
CLI Example:
salt '*' postgres.privileges_list table_name table maintenance_db=db_name
- name
Name of the object for which the permissions should be returned
- object_type
The object type, which can be one of the following:
table
sequence
schema
tablespace
language
database
group
function
- prepend
Table and Sequence object types live under a schema so this should be provided if the object is not under the default public schema
- maintenance_db
The database to connect to
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.privileges_revoke(name, object_name, object_type, privileges=None, prepend='public', maintenance_db=None, user=None, host=None, port=None, password=None, runas=None)¶
New in version 2016.3.0.
Revoke privileges on a postgres object
CLI Example:
salt '*' postgres.privileges_revoke user_name table_name table \ SELECT,UPDATE maintenance_db=db_name
- name
Name of the role whose privileges should be revoked
- object_name
Name of the object on which the revoke is to be performed
- object_type
The object type, which can be one of the following:
table
sequence
schema
tablespace
language
database
group
function
- privileges
Comma separated list of privileges to revoke, from the list below:
INSERT
CREATE
TRUNCATE
CONNECT
TRIGGER
SELECT
USAGE
TEMPORARY
UPDATE
EXECUTE
REFERENCES
DELETE
ALL
- maintenance_db
The database to connect to
- user
database username if different from config or default
- password
user password if any password for a specified user
- host
Database host if different from config or default
- port
Database port if different from config or default
- runas
System user all operations should be performed on behalf of
- salt.modules.postgres.psql_query(query, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None, write=False)¶
Run an SQL-Query and return the results as a list. This command only supports SELECT statements. This limitation can be worked around with a query like this:
WITH updated AS (UPDATE pg_authid SET rolconnlimit = 2000 WHERE rolname = 'rolename' RETURNING rolconnlimit) SELECT * FROM updated;
- query
The query string.
- user
Database username, if different from config or default.
- host
Database host, if different from config or default.
- port
Database port, if different from the config or default.
- maintenance_db
The database to run the query against.
- password
User password, if different from the config or default.
- runas
User to run the command as.
- write
Mark query as READ WRITE transaction.
CLI Example:
salt '*' postgres.psql_query 'select * from pg_stat_activity'
- salt.modules.postgres.role_get(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None, return_password=False)¶
Return a dict with information about users of a Postgres server.
Set return_password to True to get password hash in the result.
CLI Example:
salt '*' postgres.role_get postgres
- salt.modules.postgres.schema_create(dbname, name, owner=None, user=None, db_user=None, db_password=None, db_host=None, db_port=None)¶
Creates a Postgres schema.
CLI Example:
salt '*' postgres.schema_create dbname name owner='owner' \ user='user' \ db_user='user' db_password='password' db_host='hostname' db_port='port'
- salt.modules.postgres.schema_exists(dbname, name, user=None, db_user=None, db_password=None, db_host=None, db_port=None)¶
Checks if a schema exists on the Postgres server.
CLI Example:
salt '*' postgres.schema_exists dbname schemaname
- dbname
Database name we query on
- name
Schema name we look for
- user
The system user the operation should be performed on behalf of
- db_user
database username if different from config or default
- db_password
user password if any password for a specified user
- db_host
Database host if different from config or default
- db_port
Database port if different from config or default
- salt.modules.postgres.schema_get(dbname, name, user=None, db_user=None, db_password=None, db_host=None, db_port=None)¶
Return a dict with information about schemas in a database.
CLI Example:
salt '*' postgres.schema_get dbname name
- dbname
Database name we query on
- name
Schema name we look for
- user
The system user the operation should be performed on behalf of
- db_user
database username if different from config or default
- db_password
user password if any password for a specified user
- db_host
Database host if different from config or default
- db_port
Database port if different from config or default
- salt.modules.postgres.schema_list(dbname, user=None, db_user=None, db_password=None, db_host=None, db_port=None)¶
Return a dict with information about schemas in a Postgres database.
CLI Example:
salt '*' postgres.schema_list dbname
- dbname
Database name we query on
- user
The system user the operation should be performed on behalf of
- db_user
database username if different from config or default
- db_password
user password if any password for a specified user
- db_host
Database host if different from config or default
- db_port
Database port if different from config or default
- salt.modules.postgres.schema_remove(dbname, name, user=None, db_user=None, db_password=None, db_host=None, db_port=None)¶
Removes a schema from the Postgres server.
CLI Example:
salt '*' postgres.schema_remove dbname schemaname
- dbname
Database name we work on
- schemaname
The schema's name we'll remove
- user
System user all operations should be performed on behalf of
- db_user
database username if different from config or default
- db_password
user password if any password for a specified user
- db_host
Database host if different from config or default
- db_port
Database port if different from config or default
- salt.modules.postgres.tablespace_alter(name, user=None, host=None, port=None, maintenance_db=None, password=None, new_name=None, new_owner=None, set_option=None, reset_option=None, runas=None)¶
Change tablespace name, owner, or options.
CLI Example:
salt '*' postgres.tablespace_alter tsname new_owner=otheruser salt '*' postgres.tablespace_alter index_space new_name=fast_raid salt '*' postgres.tablespace_alter test set_option="{'seq_page_cost': '1.1'}" salt '*' postgres.tablespace_alter tsname reset_option=seq_page_cost
New in version 2015.8.0.
- salt.modules.postgres.tablespace_create(name, location, options=None, owner=None, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Adds a tablespace to the Postgres server.
CLI Example:
salt '*' postgres.tablespace_create tablespacename '/path/datadir'
New in version 2015.8.0.
- salt.modules.postgres.tablespace_exists(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Checks if a tablespace exists on the Postgres server.
CLI Example:
salt '*' postgres.tablespace_exists 'dbname'
New in version 2015.8.0.
- salt.modules.postgres.tablespace_list(user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Return dictionary with information about tablespaces of a Postgres server.
CLI Example:
salt '*' postgres.tablespace_list
New in version 2015.8.0.
- salt.modules.postgres.tablespace_remove(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Removes a tablespace from the Postgres server.
CLI Example:
salt '*' postgres.tablespace_remove tsname
New in version 2015.8.0.
- salt.modules.postgres.user_create(username, user=None, host=None, port=None, maintenance_db=None, password=None, createdb=None, createroles=None, inherit=None, login=None, connlimit=None, encrypted=None, superuser=None, replication=None, rolepassword=None, valid_until=None, groups=None, runas=None)¶
Creates a Postgres user.
CLI Examples:
salt '*' postgres.user_create 'username' user='user' \ host='hostname' port='port' password='password' \ rolepassword='rolepassword' valid_until='valid_until'
- salt.modules.postgres.user_exists(name, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Checks if a user exists on the Postgres server.
CLI Example:
salt '*' postgres.user_exists 'username'
- salt.modules.postgres.user_list(user=None, host=None, port=None, maintenance_db=None, password=None, runas=None, return_password=False)¶
Return a dict with information about users of a Postgres server.
Set return_password to True to get password hash in the result.
CLI Example:
salt '*' postgres.user_list
- salt.modules.postgres.user_remove(username, user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Removes a user from the Postgres server.
CLI Example:
salt '*' postgres.user_remove 'username'
- salt.modules.postgres.user_update(username, user=None, host=None, port=None, maintenance_db=None, password=None, createdb=None, createroles=None, encrypted=None, superuser=None, inherit=None, login=None, connlimit=None, replication=None, rolepassword=None, valid_until=None, groups=None, runas=None)¶
Updates a Postgres user.
CLI Examples:
salt '*' postgres.user_update 'username' user='user' \ host='hostname' port='port' password='password' \ rolepassword='rolepassword' valid_until='valid_until'
- salt.modules.postgres.version(user=None, host=None, port=None, maintenance_db=None, password=None, runas=None)¶
Return the version of a Postgres server.
CLI Example:
salt '*' postgres.version
