Schema and Table Permissioning and Database Users
👤 This documentation is intended for Database Administrators and/or Site Administrators.
This documentation includes information on how the Warehouse grants permissions to newly created schemas and tables and information on database users for Warehouse customers. **Note: Only Periscope Data Warehouse customers have access to grant schema and table permissions and database users. Site administrators can contact their Customer Success Manager for additional information.**
To view a list of users, query the PG_USER catalog table:
- select * from pg_user
- This is the default superuser that is created for customers with Periscope Cache.
- This is the user that runs queries both on the Warehouse and on Periscope Cache.
- This is not a superuser.
- USAGE and SELECT access are automatically granted to new schemas and tables for this user. These permissions can also be manually set at any time. This functionality is covered in more detail later in this documentation page.
- This is the superuser that is created when customers save a password via the Cluster Info Page
- Customers will utilize this user to access and connect to their Warehouse
- This user will always be their site name. For example, the user on "Periscope-Redesign-Demo" is "periscoperedesigndemo". The host URL on the Cluster Info page is "periscope-redesign-demo.periscopewarehouse.com".
- Redshift makes a few users by default as well, e.g. "rdsdb".
Modifying the default database and the default user is not recommended.
Schema and Table Permissioning
The Periscope Warehouse provides master access to an AWS Redshift cluster which integrates directly into the Periscope visualization and analytics platform. The Periscope Warehouse stores everything in one database on the cluster.
Automatic Schema and Table Grants:
Once an hour, Periscope will automatically attempt to grant the site_12345 user access to any new schemas+tables in the database. This is the same user that runs queries in Periscope on the cache/Warehouse.
Manual Schema and Table Grants:
If customers want to see and access the data in Periscope immediately, they can manually grant schema and table permissions by following the steps below:
1. Customer is logged into the Redshift instance with superuser access (can be obtained via Cluster Info)
2. Run: GRANT USAGE ON SCHEMA schema_name TO site_12345
3. Run one of the following if it's a specific table or if it's all tables in the schema:
GRANT SELECT ON schema_name.table_name TO site_12345
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO site_12345
4. After running the previous two steps, the user should now be able to query the table in Periscope
5. To make the schema and table visible in the Warehouse dropdown, the user must now refresh it via the "Cluster Info" page
6. Confirm whether permissions were properly granted by attempting to select from it in the database dropdown within the SQL editor
7. Confirm the schema and table have been defined in the site_12345 database.
Views with no schema binding are not shown in the schema tab.
Refreshing the Cluster Info schema DOES NOT automatically grant USAGE/SELECT. It will only make schemas and tables appear in the "Warehouse" dropdown if they have already been granted the necessary access.