In order to access a Postgres database QueryTree needs a database user that has read access to one or more views/tables.
1. First you'll need to create a QueryTree user. You can do this by logging into Postgres with an administrator account and running the following SQL command:
CREATE USER QueryTreeUser WITH PASSWORD '<PasswordGoesHere>';
Remember to replace <PasswordGoesHere> with a secure password.
2. Next the user needs appropriate access level. Postgres has three levels of security that need to be set up: Database, Schema, and Table. To allow QueryTree to connect to your database you will need to run the following SQL command:
GRANT CONNECT ON DATABASE <DatabaseGoesHere> TO QueryTreeUser;
Remember to replace <DatabaseGoesHere> with the name of your database.
3. Now the QueryTree user needs to be given usage rights at the schema level. Postgres groups up its tables into organisational units called schemas. If no schemas were created for your database the tables will be found on a schema called 'public'. You will need to grant access to each schema that contains tables that you want to report on. You can do this with the following SQL command for each schema:
GRANT USAGE ON SCHEMA <SchemaGoesHere> TO QueryTreeUser;
Remember to replace <SchemaGoesHere> with the name of your schema.
4. Finally the QueryTree user needs to be given read only access to each table you want to report on.
If you have Postgres version 9.0 or higher you can give read only access to all tables on a schema with the following SQL command:
GRANT SELECT ON ALL TABLES IN SCHEMA <SchemaGoesHere> TO QueryTreeUser;
In previous versions of Postgres the following SQL command as the same effect:
SELECT 'GRANT SELECT ON ' || relname || ' TO QueryTreeUser;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = '<SchemaGoesHere>' AND relkind IN ('r', 'v', 'S');
If you are not sure what version of Postgres you have you can run the following SQL Command:
If you only want to grant access to specific tables you can run the following SQL command for each table instead;
GRANT SELECT ON <TableGoesHere> TO QueryTreeUser;