Does anyone know off-hand which privileges are nee...
# troubleshootingd
dean_morin
05/14/2021, 6:29 PMDoes anyone know off-hand which privileges are needed for catalog discovery in postgres?
I’ve created a database user for meltano to use:
Copy code
CREATE ROLE meltano_robot WITH LOGIN PASSWORD 'TODO';
CREATE ROLE meltano_app;
GRANT rds_replication TO meltano_app;
GRANT CONNECT ON DATABASE mydb TO meltano_app;
GRANT USAGE ON SCHEMA public TO meltano_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO meltano_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO meltano_app;RuntimeError: 0 tables were discovered across the entire clustera
aaronsteers
05/14/2021, 7:22 PMI think you’ve covered the USAGE and SELECT permissions, which are the main ones. To confirm, did you run/rerun the
GRANT SELECT ON ALL TABLES…d
dean_morin
05/14/2021, 8:07 PMThe user can query all of the tables no problem, it just seems that it can’t run this query that the tap uses to get table details: https://github.com/transferwise/pipelinewise-tap-postgres/blob/master/tap_postgres/discovery_utils.py#L68-L108
dean_morin
05/14/2021, 8:07 PMIf I run that query as the rds admin use, a bunch of rows are returned, but as my created user I get zero rows back.
dean_morin
05/14/2021, 8:28 PMWait nm, the user can’t query all the tables no problem
dean_morin
05/14/2021, 8:31 PMI must have connected to the wrong db or something when I was testing. The problem was I didn’t actually assign the user to the role with all of the privs, lol:
Copy code
GRANT meltano_app TO meltano_robot;a
aaronsteers
05/14/2021, 8:33 PMThat’s great news, I think. 🙂 Does that mean it seems to be working now?
d
dean_morin
05/14/2021, 8:33 PMIt is thanks!
dean_morin
05/14/2021, 8:33 PMI thought there would be some obscure privs involved, but nope, just the bog standard
a
aaronsteers
05/14/2021, 8:34 PMAwesome - I’m glad it’s working now!
c
chris_kings-lynne
05/15/2021, 5:42 AMHas anyone ever tried ELTing the actual
information_schemaa
aaronsteers
05/15/2021, 5:58 AMI did something similar in a past role. Specifically, we created a simplified yaml version of the catalog.json file and then committed that simplified file to source control. Periodically, or when something looked off, we'd check the version history on that catalog derivative to see what changed when. We could have gone further and done automated daily check-ins, but the visibility that gave us was I guess "good enough" that we didn't end up prioritizing the daily commit and automated comparison.
It did come in really handy in a least a few occasions - to be able to quantify how many new columns were being added/dropped in Salesforce, for instance, and as the first thing to check if something downstream broke.
c
chris_kings-lynne
05/15/2021, 6:05 AMmm interesting
chris_kings-lynne
05/15/2021, 6:06 AMit’d be a sweet trick in meltano to have the option of flattening and storing into the loader the metadata
c
casey
06/14/2021, 10:35 PM@dean_morin you just saved me some more time figuring out why this was failing. Thanks for posting your issue.