The Right Way to Give a Third-Party DBA Access to Your PostgreSQL Database

Giving an external team access to your PostgreSQL database is one of those decisions that deserves a little thought. The easiest option is to hand over a superuser account, but it’s rarely the right one. A better approach is to create a dedicated role with only the privileges they actually need, and it takes just a few minutes to set up.

Over the years, I’ve been on both sides of this conversation. I’ve been the external DBA being onboarded onto a client’s database, and I’ve been the internal engineer deciding what access to grant. The pattern I’m going to walk you through is the one I’d reach for in either situation: a purpose-built, non-superuser role that gives an outside team exactly what they need to do real work, and nothing they shouldn’t have.

Why “Just Use Superuser” Is the Wrong Answer

A PostgreSQL superuser bypasses every permission check in the database, but the risk doesn’t stop there. Superusers can also execute operations that interact with the operating system, meaning the impact extends beyond the database itself.

In many customer engagements, external DBAs are granted elevated privileges because they need to monitor the database, investigate performance issues, or assist during incidents. In one case, the customer required a least-privilege approach because of their security and compliance requirements. Rather than providing unrestricted superuser access, they requested a dedicated monitoring role with only the permissions needed to perform the agreed scope of work.

The principle at play here is called Least Privilege: give someone exactly what they need and nothing more. It sounds obvious until you’re in the middle of an incident and “just give them superuser for now” feels like the fastest path forward. That’s exactly when the shortcut is most tempting, and exactly when it’s most likely to bite you.

Step 1: Create the Role Itself

The foundation is a dedicated login role with no elevated flags. Think of this like issuing a contractor badge that gets them into the building but not into the server room or the executive floor.

CREATE ROLE <your_user>
  LOGIN
  PASSWORD '<strong_password>'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT 5;

Note: CONNECTION LIMIT 5 is a practical addition. External teams tend to run tooling that opens a pool of connections. Capping this prevents an accidental connection leak from an external script from exhausting your max_connections and taking down your application. More on connection segmentation at the end.

Step 2: Grant Monitoring Without Data Access

This is where PostgreSQL’s predefined roles do most of the heavy lifting for you. The pg_monitor role is a bundle that grants visibility into server internals, active queries, lock waits, buffer stats, and replication lag, without touching a single application table.

GRANT pg_monitor TO <your_user>;

That one line gives an external DBA everything they need for performance analysis and incident diagnosis. They can see who’s blocking whom, which queries are burning CPU, how the autovacuum daemon is performing, and what the replication lag looks like. They cannot see what’s inside your orders table or your users table. That separation is the whole point.

Step 3: Allow Session Management

Monitoring is useful. Being able to act on what you see is what makes a DBA actually helpful during an incident.

pg_signal_backend lets the role cancel runaway queries or terminate stuck sessions, the database equivalent of killing a hung process. Without it, an external team can see a query that’s been running for six hours and chewing through I/O, but they can’t do anything about it.

GRANT pg_signal_backend TO <your_user>;

One caveat worth understanding: this role can signal backends belonging to other non-superusers, but it cannot terminate a superuser’s own session. That’s a deliberate PostgreSQL design choice, and it’s the right one. The customer’s internal DBA always retains that final kill switch.

Step 4: Checkpoint Control (PostgreSQL 15 and Later)

Before PostgreSQL 15, triggering a manual CHECKPOINT, which flushes dirty pages from shared buffers to disk, required superuser privileges. This matters during certain maintenance windows and before manually taking a base backup.

PostgreSQL 15 introduced pg_checkpoint specifically for this delegation use case.

GRANT pg_checkpoint TO <your_user>;

This is optional, and you should only grant it if the engagement scope actually calls for maintenance-level work. If the external team is doing monitoring and query tuning only, skip this one.

Step 5: Maintenance on Tables You Don’t Own (PostgreSQL 17+)

Here’s the one that genuinely excites me when I talk about PostgreSQL 17. Until this version, performing VACUUM, ANALYZE, or REINDEX on a table you don’t own required either ownership of that table or superuser. There was no middle ground. For external DBAs who need to run maintenance without seeing data, the only options were awkward: have the customer’s team run the command, or temporarily transfer table ownership, which is messy and easy to forget to revert.

PostgreSQL 17 introduced pg_maintain. It’s a clean, targeted solution.

GRANT pg_maintain TO <your_user>;

With this grant, the role can run VACUUM, ANALYZE, REINDEX, CLUSTER, and even REFRESH MATERIALIZED VIEW on tables it doesn’t own and has no SELECT privilege on. That last part is the key. You can physically reorganize a table without ever reading its contents. This is exactly the kind of thoughtful design that makes PostgreSQL a joy to work with at scale.

Step 6: Verify Before Handing Over Credentials

Never hand over credentials without running verification queries first. The first query below confirms the role flags. rolsuper, rolbypassrls, and rolreplication should all return f (false).

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolname = '<your_user>';

The second query confirms the exact set of memberships. You should see only the roles you explicitly granted: pg_monitor, pg_signal_backend, and optionally pg_checkpoint or pg_maintain. Nothing else.

SELECT roleid::regrole AS granted_role, admin_option
FROM pg_auth_members
WHERE member = '<your_user>'::regrole;

This third query is the one most teams skip, and skipping it has burned people badly. It checks whether PUBLIC has been granted SELECT on any tables in your database.

Here’s why that matters. In PostgreSQL, PUBLIC is not a role you create; it’s a built-in group that every role automatically belongs to. Always. No exceptions. So if someone ran GRANT SELECT ON customer_orders TO PUBLIC at any point in the past, during a migration or a debugging session, then every role on the system can read that table, including your carefully scoped external DBA account. The role looks restricted. It isn’t. To find tables that have this grant, the following SQL query can be helpful.

SELECT table_schema, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'PUBLIC' AND privilege_type = 'SELECT';

Finally, run this negative test. Substitute any real application table name. This query must fail with permission denied. If it doesn’t, something in your grant chain is wrong, and you need to track down why before proceeding.

SET ROLE <your_user>;
SELECT * FROM <any_application_table> LIMIT 1;
RESET ROLE;

What This Role Still Can’t Do

Being honest about limitations is part of getting the access model right. The following operations require superuser on every PostgreSQL version, and no predefined role exists to delegate them:

Modifying postgresql.conf or running ALTER SYSTEM to change server parameters
Editing pg_hba.conf or pg_ident.conf for authentication configuration
Restarting the PostgreSQL server process
Creating tablespaces
Installing extensions that require superuser, including untrusted procedural languages like plperlu or plpythonu
Certain low-level replication configuration tasks

These should stay with your internal DBA team. For engagements where an external team genuinely needs to perform one of these operations, the right approach is a controlled, audited, time-boxed privilege elevation, not a standing superuser grant.

The Mindset Shift That Makes This Easy

Creating a dedicated role might seem like extra work the first time you do it, but once you’ve built the process, it quickly becomes part of a standard onboarding checklist. The result is a clearly defined security boundary that’s easy to review, audit, and remove when the engagement ends.

What surprised me was how much the external team appreciated it, too. Not because it limited them, but because it gave them something clean to point to. When they’re working inside a client’s system, being able to say “here’s exactly what we can see and here’s exactly what we can’t” builds trust faster than any contract clause.

Your security team gets a clear, auditable boundary. You get a role you can revoke with a single DROP ROLE at the end of the engagement and know with certainty what you’re removing. Nobody has to guess what that superuser account touched or who still has the password written down somewhere.

The predefined roles: pg_monitor, pg_signal_backend, pg_checkpoint, and pg_maintain exist because the PostgreSQL team recognized that “superuser or nothing” isn’t a real security model for teams that work with people outside their organization. Those roles are the answer. Fifteen minutes of setup now is the thing that keeps a future incident from becoming something worse than it needed to be.