It generates a SQL script based on a Jinja2 template, allowing you to automate the creation of a database, roles, and permissions.
Project description
generate_sql_script.py
This script generates a SQL script based on a Jinja2 template, allowing you to automate the creation of a database, roles, and permissions. It is particularly useful for setting up a PostgreSQL database with predefined roles and permissions.
Installation:
# You can create a virtual env to install the cli
# Creating the venv
$ python -m venv tutorial_env
# Activating
$ source tutorial_env/bin/activate
# Installing the cli
pip install generate_sql_script
Usage
$ psql_script_generator -d <database_name> -w <readwrite_role> -u <user_role> -p <password> -t <template_file> -o <output_file>
-d, --database: Name of the database.
-w, --readwrite_role: Name of the readwrite role.
-u, --user_role: Name of the users role.
-p, --password: Password for the user roles.
-t, --template: Path to the Jinja2 template file.
-o, --output_file: Output file for the generated SQL script.
Let's validate our generated sql script!!!
Start a PostgreSQL container:
$ docker run --name psql-validating -e POSTGRES_PASSWORD=mysecretpassword -p 5555:5432 -d postgres:13
Run generate_sql_script cli:
$ psql_script_generator -d test -w test_readwrite -u test_user -p 'qweasdzxc' -t readwrite-user-template.sql.j2 -o test_sql_script.sql
Run the generated SQL script:
$ export PGPASSWORD='mysecretpassword'; psql -h localhost -U postgres -d postgres -p 5555 -w -f test_sql_script.sql
# The output should be something like that:
CREATE DATABASE
CREATE ROLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
CREATE ROLE
GRANT ROLE
ALTER DATABASE
Now, let's test the grants that provided to test_user
# Run the sql script with write, read and delete permissions
export PGPASSWORD='qweasdzxc'; psql -h localhost -U test_user -d postgres -p 5555 -w -f validating.sql
# The output should be something like:
CREATE TABLE
INSERT 0 1
id | title
----+------------------------
1 | Learn basic SQL syntax
(1 row)
ALTER TABLE
UPDATE 1
id | title | completed
----+------------------------+-----------
1 | Learn basic SQL syntax | t
(1 row)
INSERT 0 1
DELETE 1
id | title | completed
----+------------------------+-----------
1 | Learn basic SQL syntax | t
(1 row)
DROP TABLE
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Close
Hashes for psql_script_generator-0.0.2.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 0fa2136be2a5ea436756484db9b300b4cb3ea727a3797803ce7ffdc1fb504f01 |
|
MD5 | 6a58db08fd021052f85a64190c74cab3 |
|
BLAKE2b-256 | f181bf6fd2f88fadeaf1d6cd7e080e5da0340a5da2838ca39a11ef15b551d9a8 |
Close
Hashes for psql_script_generator-0.0.2-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | ebb51898ddb599154560d89b82435f59bc373ccd601271bc7eb48ef11d609ee4 |
|
MD5 | ae5999fd44460fa58c3f3f902be4cf8f |
|
BLAKE2b-256 | 364a91c5c89d3fc552442939bec2e0a3dc5e81f469a2c56768641ed8ec7c058f |