Retired: Using a Different Database Connection for PDT Jobs

[kevin1]

Note: The content of this article has been updated and moved to Looker’s technical documentation here.

[Seth_Newman]

What do we need to do to successfully change our configuration from our normal looker DB user to another user made exclusively for PDT’s? On a Postgres DB, we encountered an issue where Looker tried to recreate PDT’s and got an error trying to rename the PDT to the name of a table that already existed -

Error in RENAME TABLE: org.postgresql.util.PSQLException: ERROR: relation "lr$lzh1vutdpb6b1qej835hd_some_table_name" already exists

Do we need to drop the original tables from the looker_scratch space? Or make sure that the new PDT user has the right ownership over the original scratch tables?

Hope this helps for anyone else who may have run into a similar issue.

[ted_conbeer]

I had a bear of a time determining the correct privileges to use this feature with Redshift. For the benefit of others, here is a set of GRANTS that will generate the desired permissions:

grant usage on schema analytics to looker_pdt_builder;
grant select on all tables in schema analytics to looker_pdt_builder;

alter schema looker_scratch owner to looker_pdt_builder ;
grant all on schema looker_scratch to looker_pdt_builder WITH GRANT OPTION;
grant all on all tables in schema looker_scratch to looker_pdt_builder WITH GRANT OPTION;

grant usage on schema analytics to looker;
grant select on all tables in schema analytics to looker;

grant usage on schema looker_scratch to looker;
grant select on all tables in schema looker_scratch to looker;
alter default privileges for user looker_pdt_builder in schema looker_scratch
    grant select on tables to looker;

You’ll want to run this as a superuser.

Assumes you have your normal tables in analytics, build your pdts in looker_scratch and have Looker connecting with a user named looker with the pdt override user called looker_pdt_builder. If analytics contains views that select from other schemas, you’ll need to grant usage on those schemas to both users, as well.

[philip_martinel]

Hey @Seth_Newman,

Provided the db user defined in the PDT Override section has the same grants as the original db user to the temp db scratch schema, Looker should always drop the existing pdt before renaming the new table. Whenever a rename fails, it’s possibly a consequence of a failed DROP. Please check whether your PDT Override user has the needed permissions to perform that function. If you’re still running into trouble, please visit help.looker.com.

Thanks,
Philip

[Rex1]

Hi Phillip I think there is a problem i can see in some circumstance that pdts are built with the original looker user but cannot be dropped by the new user because they are not the owner.

you might consider validating if migration steps are:

1. Create PDT User
2. Grant user neccesary read/write permissions on data schemas (not the looker scratch)
3. Change owner of looker scratch schema to pdt
4. Make sure that current looker user has explicit read access to all tables in scratch schema
5. Change owner of all tables in the scratch schema to pdt user

-- use meta sql to generate a list of sql statements to run
SELECT 'ALTER TABLE ' || table_schema || '.' || table_name || ' OWNER TO looker_pdt_builder;'
FROM information_schema.tables t
WHERE t.table_schema = 'looker_scratch';
-- copy the output to a sql client and run
-- curate the output to make sure there isn't something funky in your scratch schema that doesn't belong to the pdt user

6. Change the connection pdt user to the new looker pdt user
7. IMPORTANT Check PDT builder area in admin to confirm there are no issues.

[Michael_Patek]

Thanks for this!

Another thing to note, if the tables in the analytics schema are ever rebuilt, you’d need to re-grant SELECT permission on those tables. Assuming that the analytics tables are built by an analytics_builder user, then you can ensure that Looker has future access by running this:

alter default privileges for user analytics_builder in schema analytics grant select on tables to looker_pdt_builder;

[ted_conbeer]

Very good point! You would need the same for the normal Looker user, too:

alter default privileges for user analytics_builder in schema analytics grant select on tables to looker;