Cosmos Db for PostgreSQL
You set up cosmos db for postgresql by creating cluster nodes and database roles. Then you grant table and column permissions to users and apps. Most teams start with one shared admin role to get apps running fast. Broad roles let any user change schemas or read sensitive customer data. A single bad query or leaked password then puts the whole cluster at risk. How role setup works in cosmos db for postgresql This service runs native PostgreSQL with an extension that splits tables across nodes
PostgreSQL Access Control: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
You set up cosmos db for postgresql by creating cluster nodes and database roles. Then you grant table and column permissions to users and apps. Most teams start with one shared admin role to get apps running fast. Broad roles let any user change schemas or read sensitive customer data. A single bad query or leaked password then puts the whole cluster at risk.
How role setup works in cosmos db for postgresql
This service runs native PostgreSQL with an extension that splits tables across nodes. A coordinator node takes client connections and routes queries to worker nodes. You manage database roles on the coordinator node using standard database privileges. Group roles help teams manage access when many people need the same permissions. Admins create a group role first and grant it specific table privileges. Then they add individual user roles into that group role.
Every database also includes a special role called PUBLIC that covers all users. By default, PUBLIC lets any user connect to the database and make temporary tables. It also lets users run functions and procedures unless the owner revokes those rights. In PostgreSQL 15 and newer, users cannot create objects in the public schema by default. On older upgraded databases, admins must revoke that create right from PUBLIC by hand.
For read access, admins grant the SELECT privilege on tables, views, or materialized views. You can grant SELECT on a whole table or limit it to specific columns. Keep in mind that the SELECT privilege also lets users export data out. When teams add new tables later, default privilege rules apply only to the creating role. If a migration runs under a different role, new tables might miss those rules.
What native roles solve and what they leave open
Native database roles do a good job of sorting users into broad buckets. You can separate read-only analysts from apps that need to write data. Column privileges also help you restrict access to specific fields on known tables. This setup controls who can log in and which objects they can touch.
PostgreSQL Access Control: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, static database roles only check permissions at the object and column level. They do not inspect what individual queries or commands do while in flight. A user with read rights on a customer table can still pull every row by default. If a column holds emails, phone numbers, card numbers, or national IDs, SELECT returns them raw. Maintaining column grants across dozens of distributed tables also takes constant manual work. Every schema change requires a review of group roles and default privilege rules.
Shared credentials create another big gap for engineering and operations teams. When five developers use the same database role, database logs only show that role name. You cannot tell which person ran a slow query or dropped a table. Rotating passwords for those roles also means updating config files across many apps. Teams need a way to tie every query to a real person and filter results.
Govern statements and responses across databases and APIs
Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev sits inline in the protocol beside each supported service as an open-source proxy, a sidecar for your database or API. For every query or command an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.
First, hoop.dev guardrails block banned queries before they run or hold risky ones for review, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
How do I give an engineer read-only access in cosmos db for postgresql?
Create a group role and grant it the SELECT privilege on the target tables. Then create a user role for the engineer and add it to that group.
Why can a new user still run functions without explicit grants?
PostgreSQL grants the special PUBLIC role permission to execute functions and procedures by default. The object owner must revoke execute rights from PUBLIC to block new users.
Does granting SELECT on specific columns stop bulk data exports?
No, the SELECT privilege also allows bulk export commands on the permitted columns. Users can still copy out all rows from the columns they are allowed to read by default.