I have created a comprehensive set of replacement system tables (RST for short) for Amazon Redshift, which are intended to supersede and replace the native Redshisft system tables.
These are for and only for provisioned clusters, not serverless. This is because serverless has removed large majority of Redshift system tables (which RST get information from) and so installation is impossible.
(Note serverless is not serverless, but ordinary, normal Redshift clusters presented as serverless. I wholly advise against their use, for many reasons. For more information, see Serverless investigation.)
| Version | Installer | Docs | Funcs | Procs | Tables | Views |
|---|---|---|---|---|---|---|
| 250518 | rst_250518.sql.bz2 rst_250518.sql.zip |
rst_250518.pdf rst_250518.html |
7 | 10 | 3 | 769 |
An installer will create three schemas;
Notes;
After installation, the following steps must be performed.
To grant privileges to use RST, there are two sets of four procedures provided in each install, which grant/revoke necessary privs for that release to user, group, role, or public.
These procedures grant and revoke the following privileges;
The user calling these functions must have the privileges to issue these grants/revokes, which means RST owner (the user who installed) or superuser.
base_YYMMDD.sp_grant_rst_privs_to_group( group_name ); base_YYMMDD.sp_grant_rst_privs_to_public(); base_YYMMDD.sp_privs_rst_privs_to_role( role_name ); base_YYMMDD.sp_privs_rst_privs_to_user( user_name ); base_YYMMDD.sp_revoke_rst_privs_from_group( group_name ); base_YYMMDD.sp_revoke_rst_privs_from_public(); base_YYMMDD.sp_revoke_rst_privs_from_role( role_name ); base_YYMMDD.sp_revoke_rst_privs_from_user( user_name );
I advise creating group, giving it privileges, and adding/removing users to/from the group.
Finally, as RST use native Redshift system tables, users must have usage on pg_catalog and select on all tables and views in pg_catalog. Redshift out of box has these privileges granted to public. These privileges will be missing only if you or your admin has taken (very unusual and very drastic) steps to remove them, and if so then these privileges must be granted to RST user.
There is a per-user setting, syslog, which is set to restricted (the default) or unrestricted.
When set to restricted, a user can see system table rows for and only for objects (tables, queries, etc) they own. When set to unrestricted, users - in theory, but not quite in practise (see below) - see rows for all objects. This is set like so;
alter user [user_name] syslog access unrestricted;
For some years now, system tables for new Redshift functionality have been implemented as thin veneer views over row-producing functions. These views do not honour syslog unrestricted because the underlying function does not honour syslog unrestricted. The only way with these views to see rows for all objects is to be superuser. Nothing I can do about this; Redshift system table design flaw.
The short-form views are intended for direct human use at the command line. Typing out the schema name is however laborious and as such antagonistic to direct human use.
The solution to this is add the short-form schema to the schema search path.
This can be done on a per-user basis, both on a per-session or permanent basis, and the default search path for the cluster, which is given to new users, can also be changed.
The problem with setting schema search path is that, as far as I know, it is not possible to write a command which says "existing path, plus this extra schema" - rather, you have to fully specify the search path, which means you have to show it, so you can see what it is, to then be able to set it, plus the extra schema.
To show;
show search_path;
To set on a per-session basis (here I've used the default search path, you need to set it to whatever came from show search_path;);
set search_path to '$user', public, sf_YYMMDD;
To set permanently for a user;
alter user [user_name] set search_path '$user', public, sf_YYMMDD;
The cluster's default search path, which is given to new users, is changed in the parameter group settings in the Redshift console. It cannot be changed via SQL.
Cascade drop the three schemas which hold the RST version to uninstall.
There is a single convenience view, which shows installed RST versions. If any of the three schemas of a version are present, the version is listed.
select * from base_YYMMDD.installed_versions;
Home 3D Друк Blog Bring-Up Times Cross-Region Benchmarks Email Forums Mailing Lists Redshift Price Tracker Redshift Version Tracker Replacement System Tables Reserved Instances Marketplace Slack System Table Tracker The Known Universe White Papers