support Redshift

[HarlanH]

Redshift is based on Postgres 8.0.2, so a few things are different. Maybe this issue should be a list of known issues?

pg_authid does not exist

Failed to execute query "SELECT * FROM pg_authid WHERE rolname != 'pg_signal_backend';": relation "pg_authid" does not exist

It looks like it was introduced in 8.1:

This change also replaces pg_shadow and pg_group by new role-capable catalogs pg_authid and pg_auth_members.

[zcmarine]

Hey Harlan, that's a great request! My understanding is that the best way to verify whether something works with redshift is to test against Postgres 8.0. Do you have any sense of whether getting pgbedrock working with Postgres 8.0 is roughly equivalent to getting it working with Redshift, or are there other distinctions between those two systems?

In addition to swapping out pg_authid, we'd also need to skip the default privilege grants / removals as that wasn't introduced until Postgres 9.0.

In any case, I think the solution is relatively simple: upon connecting to the database, immediately ask what Postgres version we're working with. For default privileges we then just skip that block of code if we're in an older version. With regard to pg_authid, I'm curious whether we can just use pg_shadow / pg_group permanently or if we will need to similarly branch. Until we tinker with Postgres 8.0 it likely won't be clear.

As far as specific testing process, I think this should be pretty straightforward (at least to describe in words): add a Postgres 8.0 docker container and run our test suite against it. We'll need to mark tests to skip if we're testing the older Postgres version (e.g. default privileges), and we may have to even split out some existing tests to account for the expected differing behavior. As an example, we currently test a grant to myschema.* and make sure that a grant happens for all tables as well as a default privilege grant occurs. We'd need to skip that test for Postgres 8.0 and have a new test that asserts all the same things except for the default privilege grant.

Assuming that Postgres 8.0 is functionally equivalent to Redshift, if we can get all tests to pass then we should be in a good place, but until someone actually tests against a Redshift I guess it's still a bit of a guess.

We don't actually use Redshift at Squarespace so I'm not sure how quickly this would get prioritized on my end, but I'm also happy to sync up and offer guidance on how to try implementing this if it's higher priority on your end.

[zcmarine]

After poking around a bit more, it looks like Redshift does support default privileges as per AWS's documentation here, yet Postgres 8.0.2 definitely does not.

First, the Postgres release notes first mention default privileges in 9.0. Additionally, when I try running a Postgres 8.0.2 docker instance I can't get an ALTER DEFAULT PRIVILEGES statement to run. Moreover, within that container there doesn't appear to be a pg_catalog.pg_default_acl table, which is how default privileges are maintained (at least in Postgres 9+).

For documentation purposes, I looked into this by getting a local Postgres 8.0.2 running via:

docker run -p 5439:5439 --name local_redshift guildeducation/docker-amazon-redshift

I then tested default privileges with:

CREATE USER test_user;
CREATE SCHEMA test_schema;
ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema GRANT SELECT ON TABLES TO test_user;

I then searched for the default ACL table via:

SELECT * 
FROM information_schema.tables 
WHERE table_schema = 'pg_catalog' 
ORDER BY table_name;

The takeaway from this is that I'm not really sure how to properly test redshift then: my thought was that we could spin up a Postgres 8.0.2 container and operate against that, but if that doesn't match Redshift (which based on the above it does not), then I don't know how to reliably test redshift without having a live redshift and tests that rely on network connectivity, which feels very awkward. Maybe you have some ideas though Harlan.

[HarlanH]

Hi Zach, OK, great. I think that managing permissions is an even bigger problem on analytical databases like Redshift than on operational databases. That's certainly true for us.

Yes, Redshift has backported a bunch of stuff from post 8.0.2, including the default privs stuff. I don't think that you'll be able to test Redshift without an actual Redshift instance. I wonder how other open source projects handle testing against non-free databases?

[zcmarine]

Hey Harlan, I looked around a bit and it seems like people just create another redshift instance and test on that, so that seems like the best path forward. I'm hoping because there isn't much data being added / removed that the cost element would be pretty small since I don't want to discourage people from testing.

I'm hoping that since Redshift appears to support default privileges that there isn't much machinery here that will have to change, i.e. perhaps it's just that the system catalogs are still organized the way Postgres used to be and it's just a few tables that are missing. In that vein, could you try running the below query and tell me what comes back? It's just listing out which of the pg_catalog tables that pgbedrock relies on are missing within Redshift:

SELECT
    desired_tables.table_name AS missing_tables
FROM (
    VALUES
        ('pg_auth_members'),
        ('pg_authid'),
        ('pg_class'),
        ('pg_default_acl'),
        ('pg_depend'),
        ('pg_namespace'),
        ('pg_roles')
    ) AS desired_tables (table_name)
    LEFT JOIN information_schema.tables info_tables
        ON desired_tables.table_name = info_tables.table_name
        AND info_tables.table_schema = 'pg_catalog'
WHERE
    info_tables.table_name IS NULL
;

[HarlanH]

Yeah -- a single-node dc2.large instance is 25 cents an hour, so if there was a spin-up/test/spin-down script for Redshift, it'd be no big deal...

That query doesn't run because Redshift doesn't have VALUES. This query:

SELECT table_name, 
    table_type,
    table_name in ('pg_auth_members', 'pg_authid', 'pg_class', 'pg_default_acl', 'pg_depend', 'pg_namespace', 'pg_roles') as present
from information_schema.tables info_tables
WHERE info_tables.table_schema = 'pg_catalog'
order by 3 desc

yields:

pg_default_acl  BASE TABLE  t
pg_class    BASE TABLE  t
pg_namespace    BASE TABLE  t
pg_depend   BASE TABLE  t
pg_conf BASE TABLE  f
pg_library  BASE TABLE  f
pg_shdepend BASE TABLE  f
pg_statistic    BASE TABLE  f
pg_type BASE TABLE  f
pg_attribute    BASE TABLE  f
pg_tablespace   BASE TABLE  f
pg_inherits BASE TABLE  f
pg_index    BASE TABLE  f
pg_operator BASE TABLE  f
pg_opclass  BASE TABLE  f
pg_am   BASE TABLE  f
pg_amop BASE TABLE  f
pg_amproc   BASE TABLE  f
pg_language BASE TABLE  f
pg_largeobject  BASE TABLE  f
pg_aggregate    BASE TABLE  f
pg_statistic_multicol   BASE TABLE  f
pg_trigger  BASE TABLE  f
pg_listener BASE TABLE  f
pg_cast BASE TABLE  f
pg_conversion   BASE TABLE  f
pg_udf  BASE TABLE  f
pg_bar_repos    BASE TABLE  f
pg_bar_state    BASE TABLE  f
pg_highwatermark    BASE TABLE  f
pg_resize   BASE TABLE  f
pg_attrdef  BASE TABLE  f
pg_constraint   BASE TABLE  f
pg_database BASE TABLE  f
pg_description  BASE TABLE  f
pg_group    BASE TABLE  f
pg_proc BASE TABLE  f
pg_rewrite  BASE TABLE  f
pg_bar_ddllog   BASE TABLE  f
pg_test BASE TABLE  f
pg_statistic_indicator  BASE TABLE  f
pg_user VIEW    f
pg_rules    VIEW    f
pg_views    VIEW    f
pg_tables   VIEW    f
pg_table_def    VIEW    f
pg_indexes  VIEW    f
pg_stats    VIEW    f
pg_stat_all_tables  VIEW    f
pg_stat_sys_tables  VIEW    f
pg_stat_user_tables VIEW    f
pg_statio_all_tables    VIEW    f
pg_statio_sys_tables    VIEW    f
pg_statio_user_tables   VIEW    f
pg_stat_all_indexes VIEW    f
pg_stat_sys_indexes VIEW    f
pg_stat_user_indexes    VIEW    f
pg_statio_all_indexes   VIEW    f
pg_statio_sys_indexes   VIEW    f
pg_statio_user_indexes  VIEW    f
pg_statio_all_sequences VIEW    f
pg_statio_sys_sequences VIEW    f
pg_statio_user_sequences    VIEW    f
pg_stat_activity    VIEW    f
pg_stat_database    VIEW    f
pg_locks    VIEW    f
pg_user_info    VIEW    f
pg_database_info    VIEW    f
pg_settings VIEW    f
pg_external_schema  BASE TABLE  f
stv_wlm_qmr_config  BASE TABLE  f
stl_rlf_scan    BASE TABLE  f
stl_internal_query_details  BASE TABLE  f
svl_awsclient_error VIEW    f
svl_s3requests  VIEW    f
svv_comm_pkt_latency_hist   VIEW    f
svv_comm_pkt_latency    VIEW    f
svv_io_latency_read VIEW    f
svv_io_latency_sync VIEW    f
svv_io_latency_write    VIEW    f
svv_sem_usage   VIEW    f
svv_sem_usage_leader    VIEW    f
svl_user_info   VIEW    f
padb_config_harvest BASE TABLE  f
stl_aggr    BASE TABLE  f
stl_alert_event_log BASE TABLE  f
stl_analyze BASE TABLE  f
stl_bcast   BASE TABLE  f
stl_column_stats    BASE TABLE  f
stl_commit_stats    BASE TABLE  f
stl_connection_log  BASE TABLE  f
stl_ddltext BASE TABLE  f
stl_delete  BASE TABLE  f
stl_disk_full_diag  BASE TABLE  f
stl_dist    BASE TABLE  f
stl_error   BASE TABLE  f
stl_explain BASE TABLE  f
stl_file_scan   BASE TABLE  f
stl_hash    BASE TABLE  f
stl_hashjoin    BASE TABLE  f
stl_insert  BASE TABLE  f
stl_leader_snapshot BASE TABLE  f
stl_limit   BASE TABLE  f
stl_load_commits    BASE TABLE  f
stl_load_errors BASE TABLE  f
stl_load_trace  BASE TABLE  f
stl_loaderror_detail    BASE TABLE  f
stl_merge   BASE TABLE  f
stl_mergejoin   BASE TABLE  f
stl_metadata_step   BASE TABLE  f
stl_nestloop    BASE TABLE  f
stl_parse   BASE TABLE  f
stl_plan_info   BASE TABLE  f
stl_project BASE TABLE  f
stl_query   BASE TABLE  f
stl_query_metrics   BASE TABLE  f
stl_querytext   BASE TABLE  f
stl_replacements    BASE TABLE  f
stl_restarted_sessions  BASE TABLE  f
stl_return  BASE TABLE  f
stl_rpc BASE TABLE  f
stl_s3client    BASE TABLE  f
stl_s3client_error  BASE TABLE  f
stl_save    BASE TABLE  f
stl_scan    BASE TABLE  f
stl_sessions    BASE TABLE  f
stl_sort    BASE TABLE  f
stl_sshclient_error BASE TABLE  f
stl_stream_segs BASE TABLE  f
stl_tr_conflict BASE TABLE  f
stl_udf_compile_error   BASE TABLE  f
stl_undone  BASE TABLE  f
stl_unique  BASE TABLE  f
stl_unload_log  BASE TABLE  f
stl_userlog BASE TABLE  f
stl_utilitytext BASE TABLE  f
stl_vacuum  BASE TABLE  f
stl_vacuum_detail   BASE TABLE  f
stl_warning BASE TABLE  f
stl_window  BASE TABLE  f
stl_wlm_error   BASE TABLE  f
stl_wlm_query   BASE TABLE  f
stl_wlm_rule_action BASE TABLE  f
stv_active_cursors  BASE TABLE  f
stv_block_reps  BASE TABLE  f
stv_blocklist   BASE TABLE  f
stv_cursor_configuration    BASE TABLE  f
stv_disk_extents    BASE TABLE  f
stv_exec_state  BASE TABLE  f
stv_inflight    BASE TABLE  f
stv_interleaved_counts  BASE TABLE  f
stv_load_state  BASE TABLE  f
stv_locks   BASE TABLE  f
stv_partitions  BASE TABLE  f
stv_pg_wal_length   BASE TABLE  f
stv_proc_stat   BASE TABLE  f
stv_query_metrics   BASE TABLE  f
stv_query_stats BASE TABLE  f
stv_recents BASE TABLE  f
stv_sessions    BASE TABLE  f
stv_slices  BASE TABLE  f
stv_startup_recovery_state  BASE TABLE  f
stv_tbl_perm    BASE TABLE  f
stv_tbl_trans   BASE TABLE  f
stv_underrepped_blocks  BASE TABLE  f
stv_wlm_classification_config   BASE TABLE  f
stv_wlm_query_queue_state   BASE TABLE  f
stv_wlm_query_state BASE TABLE  f
stv_wlm_query_task_state    BASE TABLE  f
stv_wlm_service_class_config    BASE TABLE  f
stv_wlm_service_class_state BASE TABLE  f
svl_compile VIEW    f
svl_qlog    VIEW    f
svl_query_metrics   VIEW    f
svl_query_metrics_summary   VIEW    f
svl_query_queue_info    VIEW    f
svl_query_report    VIEW    f
svl_query_summary   VIEW    f
svl_s3catalog   VIEW    f
svl_s3list  VIEW    f
svl_s3log   VIEW    f
svl_s3partition VIEW    f
svl_s3partition_summary VIEW    f
svl_s3query VIEW    f
svl_s3query_summary VIEW    f
svl_s3retries   VIEW    f
svl_statementtext   VIEW    f
svl_udf_log VIEW    f
svl_vacuum_percentage   VIEW    f
svv_columns VIEW    f
svv_diskusage   VIEW    f
svv_external_columns    VIEW    f
svv_external_databases  VIEW    f
svv_external_partitions VIEW    f
svv_external_schemas    VIEW    f
svv_external_tables VIEW    f
svv_interleaved_columns VIEW    f
svv_query_inflight  VIEW    f
svv_query_state VIEW    f
svv_restore_table_state VIEW    f
svv_table_info  VIEW    f
svv_tables  VIEW    f
svv_transactions    VIEW    f
svv_vacuum_progress VIEW    f
svv_vacuum_summary  VIEW    f
systable_globaldict BASE TABLE  f
systable_schema BASE TABLE  f
systable_topology   BASE TABLE  f

[zcmarine]

Interesting. So it looks like it's missing three tables that pgbedrock uses:

• pg_roles - this doesn't really need to be used; we should just swap out the one reference to this for pg_authid instead
• pg_auth_members - it looks like this could be replaced by pg_group (Postgres docs)
• pg_authid - This looks like the most challenging relation to swap out, for a few reasons:
  1. The attributes that are available via pg_user (Postgres docs) differ, so attributes.py would need to behave differently.
  2. Group roles in much older versions of Postgres behaved differently: they didn't have attributes themselves and I think (?) were just there to manage privileges exclusively. There'd be a bit of a ramp-up making sure we understand how to use group roles properly in this other context.
  3. We'd need to handle login and non-login roles separately. As an example, changing the login attribute for a role would likely mean we'd have to DROP USER foo; and then CREATE GROUP foo;. Similarly, if someone wanted to modify some attributes of a group role (e.g. give it a connection limit) I think that wouldn't work, so in the Redshift implementation we'd have to put additional schema validation in against specs to explain what is and isn't possible.
  4. The most trivial issue: Currently we look up all roles in pg_authid, but now we'd need to decide how to work across two tables: looking up non-login roles in pg_group and looking up login roles in pg_user.

In addition, we'd need to figure out how to test against Redshift, i.e. potentially add custom markers listing tests that should only run on Redshift and others that should only run on Postgres, then add the ability to swap out the test URL so it can hit a real Redshift.

Again, I'm not sure how highly prioritized this will get at Squarespace as we don't really use Redshift, but it seems like this is doable if someone who uses Redshift would want to take the lead on trying to flesh this out with my help.

[HarlanH]

Huge thanks for doing this research, Zach! I think this gives a big head-start to whoever can devote some time to this. (Hoping my colleague @hanleyhansen might be interested...? :) )

[clrcrl]

So I'm hacking away at this with the goal to make my forked repo Redshift-compatible (but in the process, I'm losing Postgres compatibility).

I'm pretty new to python, so I'm using this as an exercise in teaching myself, since the subject matter (Redshift user permissions) is very familiar.

As such, I don't think any of the code that I produce will be pull-request-standard. If I get my forked repo working for Redshift, I'm happy to share it around, but I don't feel I'll be the right person to introduce multi-database compatibility. Nonetheless, what I'm learning along the way will be very valuable to the person that does take this issue on.

I haven't got it working yet, and I don't imagine I'll get another chance to look at it for at least a week, so just wanted to share my current progress in case someone is looking at this soon.

So far the noteworthy challenges are:

Group memberships

• Group memberships are completely different in Redshift, and quite a challenge
  • There's no equivalent table to pg_auth_members, instead pg_groups stores group memberships as an array, returning tuples of (group, members), e.g.:

groname	grosysid	grolist
webpowerusers	101
webdevusers	102
webappusers	100	102, 103

• grolist is an array (according to information_schema.columns), which is weird, because arrays aren't supported in Redshift
• It's very difficult to unnest arrays (which are normally stored as strings) in Redshift, without using some sort of pre-made sequence of numbers (see this tutorial)
• To work around this, I considered doing a Regex match between pg_user.usesysid and pg_group.gro_list to join them together (yuk!), but I couldn't do Regex on the array type. And then because arrays are not a supported data type, it won't let me cast it to a string ¯_(ツ)_/¯
• So it seems to me that for Redshift compatibility, the group memberships will have to be returned to pgbedrock as arrays, and then I'll have to use python to unnest the array, to get the tuples of (member, group).
• Also note that rather than granting membership of a group to a role (user), in Redshift, you have to alter group "group_name" add user "user_name" - a bit strange but nothing too bad.

Users & Groups versus Roles

• As you've already realised, Redshift treats users and groups as separate objects, compared to postgres which uses roles.
• In working through this, I decided it would be easier to replace can_login: in the config with a required field, role_type: (user/group).
• I then implemented some extra Cerberus validation so that if role_type == group, certain configurations cannot exist (e.g. for owns:, role_type must equal user, since groups cannot own tables/schemas in Redshift).

Passwords

• Passwords are required for Redshift, and have constraints on length/character-types
• This is compared to Postgres where they are optional

Superusers

• The concept of a superuser in Redshift is slightly different to postgres - basically a superuser is just someone with the createuser privilege, so createuser should not be able to be defined in attributes

[mikekaminsky]

@clrcrl have you made any progress on this? I might be interested in collaborating.

[JoeNaso]

I'm pretty late to the party here, but have there been any developments with Redshift compatibility?

[ghost]

+1