sparknetworks / pgpm Goto Github PK

It's enough to use session_user instead of current_user as default for this column.
I could prove that by hacking "ALTER TABLE ... ALTER COLUMN ... SET DEFAULT session_user" - and it worked.

Consider the following:
flehmann=# create function pgpm_test() returns text as $$select current_user::text$$ security definer language sql;
CREATE FUNCTION
flehmann=# select pgpm_test();
--> flehmann
flehmann=# alter function pgpm_test() owner to postgres;
ALTER FUNCTION
flehmann=# select pgpm_test();
--> postgres

flehmann=# create function pgpm_test1() returns text as $$select session_user::text$$ security definer language sql;
CREATE FUNCTION
flehmann=# select pgpm_test1();
--> flehmann
flehmann=# alter function pgpm_test1() owner to postgres;
ALTER FUNCTION
flehmann=# select pgpm_test1();
--> flehmann

Add set <connection_set_name> argument to the call of pgpm that will get a set of connection strings to deploy to with command
db_set_name to be defined in config file that can be attached with --global-config <global_config_file_path> argument

make pgpm also send emails if it is set in general config file.
Configuration for SMTP:

"email": {
        "type": "SMTP",
        "credentials": {
            "username": "user",
            "password": "pass"
        },
        "host": "smtp.example.com",
        "port": 465,
        "from": "[email protected]",
        "to": "[email protected]",
        "subject": "Subject",
        "body": "Body"
    }

add support for two parameters:

1. --force-table-cascade will (when dropping schema objects) drop dependent table objects (like columns).
   Example: table a has column c with type t. When dropping type t with cascade column c will be dropped as well.
   We don't want that by default (as table evolutions are managed differently from functions/types changes).
2. --force-inter-schema-cascade will (when dropping schema objects) drop dependent objects in other schema (not table related like columns, for those you will need to use --force-table-cascade as well.

Currently the whole schema is dropped if run in unsafe mode. Change it to dropping only specific objects of schema.

For example, functions, types, views, but not tables

Add functions to pgpm that will start/stop auditing changes to specified tables

Currently code is crumpled in a single file. Ok for prototyping but we need to refactor it to reflect demands for scale and future development.

• Make proper OO architecture
• Provide public API

when deploying a set of functions, one of them failed, because its return_type didn't exist - a common case. It would be nice to see in the error message (stdout and/or log) a hint, which (or which first) of the "CREATE FUNCTION" scripts had the error.

The option "-u" seems to be broken, because without it i can install. The error message is:
...
2015-08-17 12:38:15,579 - pgpm.deploy - INFO - scripts/functions/_find_schema.sql
Traceback (most recent call last):
File "/usr/local/bin/pgpm", line 11, in
sys.exit(main())
File "/usr/local/lib/python2.7/dist-packages/pgpm/deploy.py", line 783, in main
install_manager(arguments)
File "/usr/local/lib/python2.7/dist-packages/pgpm/deploy.py", line 364, in install_manager
cur.execute(GRANT_DEFAULT_USAGE_PRIVILEGES.format(_variables.PGPM_SCHEMA_NAME, ', '.join(user_roles)))
psycopg2.ProgrammingError: schema "_pgpm" does not exist

There should be pgpm install command that must be run before pgpm deploy can be used.
Installation procedure will:

• create _pgpm schema in the db unless it was created already.
• if table did not exist before, create table tracked_schemas that will include info from config.json (e.g. name, current version, type, dependencies)

add --issue-ref command to pgpm deploy call.

This will store issue reference in deployment_events table

This should be supported

pgpm deploy <connection_string> --add-config file_name

it will add aditional config file on top of config.json and will override config.json's settings

format of the set of db connection strings will understand one more attribute: unique_name (could be the same as dbname):

[
    {
        "host": "HOST_NAME",
        "dbname": "DBNAME",
        "port": PORT,
        "environment": "ENVIRONMENT_NAME",
        "unique_name": "UNIQUE NAME ALIAS"
    }
]

E.G.

functions_path : ["path_1", "path_2"]

Sometimes fo rone feature i have to do ddl-changes in 2 (or more) schemas/repos. So my ddl-scripts naturally are named the same (in my case edarling_cid_tracking/ddl/acs_162_flehmann.sql and edarling_dwh_data/ddl/acs_162_flehmann.sql)
When deploying the second, I get "ddl/acs_162_flehmann.sql is not executed for schema edarling_dwh_data as it has already been executed before."
You would probably just add a column "package" to _pgpm.table_evolutions_log and add the package as parameter to _pgpm._is_table_ddl_executed(p_file_name text)

if --log-file flag is not given logs still should be written somewhere

When deploying by file in existing schema, allow adding new types

Dependency entry in config.json file will be parsed. Concept is somewhat similar to https://docs.npmjs.com/files/package.json#dependencies

• version numbers must follow a modified semver. The difference is that instead of dots version digits should be separated with _ sign (. is not allowed character for schema names)
• version notation can support:
  • exact version number either in a format 1_2_3 or 01_02_03 (latter format is for formatting purposes)
  • x notation like 01_02_XX or 01_02_xx or 1_2_X or 1_2_x
  • comparison operators like >01_02_03 or <2
  • x for any latest version of package
• package name must comply with naming conventions of postgres, exist as schema and be trackable by pgpm in order to satisfy dependancy

Example:

{ "dependencies" :
  { "foo" : "1_2_3"
  , "bar" : ">=01_02_03"
  , "baz" : ">01_02_XX"
  , "boo" : "1_2_x"
  , "qux" : "<2"
  , "thr" : "x"
  }
}

Track deployment events with installation events table

add --vcs-ref command to pgpm deploy call.

This will store vcs reference in deployment_events table

add to config.json the following attributes:

functions: {path: [PATH], strategy: [LIST OF FILES TO BE DEPLOYED RELATIVE TO PATH]}
types: {path: [PATH], strategy: [LIST OF FILES TO BE DEPLOYED RELATIVE TO PATH]}

old functions_path and types_path attributes will be ignored

pgpm is not accepting version that contain characters.
Example:
"version" : "11_07_00", -> Works correctly
"version" : "11_v_00", -> Throws an error

allow mentioning users and owners of schema in config file. Using it through -o and -u commands will override config file with a warning

rename --user flag to --usage flag and use --user flag as a user that connects to DB when used set option.

At the same time if user is not specified, os user must be used for connection attempt and warning message output

After we deployed one new schema with "-o " option, but connecting as one personal (super-) user, only the schema is owned by the , but all the objects, in this case types and functions, are owned by the one personal (super-) user.
This is not what we expect and want. Please make the owner-role owner of all objects.
The connecting (super-) user should still appear in the "_pgpm" deployment logs, but not in the real structures.
Note, that the person, who executes a pgpm deployment will probably have to be superuser, but may not have the credentials of the owner-role. As far as i know, there is no "switch user" in postgres and if there was, we wouldn't want to use it here, because we want to identify the individual user in the deployment logs.

For some reason by default all newly created functions have by default execute permission for public role. So we need to revoke it.

• add the following parameter to global-config:

{
    "issue-tracker": {
        "type": "JIRA",
        "url": "URL_TO_API",
        "username": "USERNAME",
        "password": "PASSWORD",
    }
}

• on each deployment that contains issue key, comment must be added that states what has been deployed and where

pgpm deploy <connection_string> -f file.sql - works wherever "file.sql" is in repo
pgpm deploy <connection_string> -f actual_relative_path_to_file/file.sql - doesn't work

Track when was the last deployment, what files were updated