hey folks curious q: I am just getting started with Motherduck and got a bit confused with the best pratices with role mangement - wonder what would be the best pratices there? and if there is any existing documentation?
so I come from a more Snowflake/BigQuery background, and my understanding of Role Management of the objects (e.g. tables) are still limited) from what I understood so far, it seems that Motherduck is literally the host of the DuckDBs. and all DuckDBs have to be owned by an account (either human account or a Service Account). so I assume the pattern here should be: Production Service Account owns:
Production DB; THEN,
Share of the Production DB and let the whole Org discover it
Staging Service Account owns
Staging DB; THEN,
Share of the Staing DB and let the data team discover it
this is the encouraged pattern I see so far, I am just wondering if there are further instruction of best pratices and the pattern of separating Production/Staging env here
Your understanding is pretty solid! MotherDuck databases are indeed owned by accounts (human or service), and the pattern of using service accounts for production workloads + shares for read access is the encouraged direction. One thing worth noting coming from Snowflake/BigQuery is that MotherDuck doesn't have a roles layer like SYSADMIN/ACCOUNTADMIN or IAM bindings on datasets. Access control is at the database level: a user either has access to a database or doesn't. Within an org, users are either Admins (can manage users, billing, invites) or Members. The building blocks for access separation are service accounts, shares, and token types. For production workloads, especially for production runs, we'd recommend using separate user accounts or service accounts with dedicated compute for updating and maintaining core tables. So the pattern would be: 1. Create a production service account This account owns the production databases and runs your dbt/pipeline jobs with its read/write token. 2. Create a staging service account This gives you compute isolation, since every duckling (compute instance) is isolated from each other. 3. Share production data with the org for read access The production service account creates shares from its databases. They have three access levels to choose from: • ACCESS ORGANIZATION (default) where only members of your organisation can access the share • ACCESS RESTRICTED where only the share owner and users specified with GRANT commands can access the share • ACCESS UNRESTRICTED where any user signed into any MotherDuck organisation in the same cloud region can access the share 3. For your use case (let the whole org discover it), ACCESS ORGANIZATION with VISIBILITY DISCOVERABLE is the right default. If you want to limit staging access to just the data team, ACCESS RESTRICTED + GRANT READ ON SHARE to specific usernames can give you that control. 4. BI tools and read-heavy consumers use read-scaling tokens You generate a read_scaling token for your BI tool that only permits read operations, and a standard read_write token for your ETL script. We have a nice service account walkthrough here, a DWH overview here (with broader patterns on how to structure a warehouse on MotherDuck), and an org management guide. Would this help? 🙂
.png){kind=logo}
