Create read-only backup user for Qlik Sense repository and logging database

create_sense_backup_user.sql

/*

  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;