Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Description
- -----------
- This script creates a read-only Postgres user with access
- to the Qlik Sense QSR and QLogs databases. You can use this
- user to perform backups of your Qlik Sense Repository and
- Centralized Logging databases.
- Configuration
- -------------
- On the lines below containing the \set statements, change
- the username and password to your desired values. Default
- below is set to username 'qliksensebackup' and password
- 'yourpassword'
- Executing the script
- --------------------
- To run this script, you need the password for the
- Qlik Sense Repository Superuser.
- On your central node, open a command line or PowerShell window
- in the Postgres folder supplied with Qlik Sense. As of February 2019,
- this is the default location:
- 'C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin'
- In this folder, execute the following command:
- 'psql -p 4432 -U postgres -f path\to\create_sense_backup_user.sql'
- */
- \set username qliksensebackup
- \set password yourpassword
- -- Note: if the backup user already exists then comment the line below with -- (double hyphen)
- CREATE USER :username WITH ENCRYPTED PASSWORD :'password';
- \echo Setting permissions for :username
- \echo ********************************************
- GRANT CONNECT ON DATABASE postgres TO :username ;
- GRANT CONNECT ON DATABASE "QSR" TO :username ;
- GRANT CONNECT ON DATABASE "QLogs" TO :username ;
- \ echo Setting permissions on 'postgres'
- \c postgres
- GRANT USAGE ON SCHEMA public TO :username ;
- GRANT SELECT ON ALL TABLES IN SCHEMA public TO :username ;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public
- GRANT SELECT ON TABLES TO :username ;
- GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO :username;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public
- GRANT SELECT ON SEQUENCES TO qliksensebackup;
- \ echo Setting permissions on 'QSR'
- \c "QSR"
- GRANT USAGE ON SCHEMA public TO :username ;
- GRANT SELECT ON ALL TABLES IN SCHEMA public TO :username ;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public
- GRANT SELECT ON TABLES TO :username ;
- GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO :username;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public
- GRANT SELECT ON SEQUENCES TO qliksensebackup;
- \ echo Setting permissions on 'QLogs'
- \c "QLogs"
- GRANT USAGE ON SCHEMA public TO :username ;
- GRANT SELECT ON ALL TABLES IN SCHEMA public TO :username ;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public
- GRANT SELECT ON TABLES TO :username ;
- GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO :username;
- ALTER DEFAULT PRIVILEGES IN SCHEMA public
- GRANT SELECT ON SEQUENCES TO qliksensebackup;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement