Can the admin user have CREATEROLE and BYPASSRLS? (no superuser)

Questions / Help
1

We’re planning to move to Managed Postgres from an unmanaged postgres-flex cluster (18.x). Our migrations run as an “owner” role that is not a superuser, and we’ve narrowed what it needs down to two attributes:

  • CREATEROLE: the migrations CREATE ROLE … LOGIN for the application roles and run ALTER ROLE … SET search_path.
  • BYPASSRLS: several tables use FORCE ROW LEVEL SECURITY, and the owner (and the SECURITY DEFINER functions it owns) has to bypass it. Without it, one of our first data migrations fails with “new row violates row-level security policy”.

We also use the pgcrypto and pgvector extensions.

Questions:

  1. Can the Schema Admin user (fly-user), or a user created with fly mpg users create, have the CREATEROLE and BYPASSRLS attributes? If not, is there a supported way to get them?
  2. Can that user manage the roles it creates afterwards (GRANT … WITH ADMIN OPTION, ALTER ROLE on those roles)? Is the createrole_self_grant setting available?
  3. pgvector is documented as supported, but it is not a “trusted” extension. Who runs CREATE EXTENSION vector, and can the migration user run CREATE EXTENSION IF NOT EXISTS vector when it is already installed?
  4. Which major versions are available?

The docs describe Schema Admin as the closest role to a superuser but don’t list its attributes. Thanks!

2

Hi there,

We can add BYPASSRLS to an existing role manually, but we cannot grant your users CREATEROLE as then they might muck up the system roles we need to properly manage your MPG cluster.

pgvector should totally be supported/used; we have many users running it. DId you enable it via the MPG dashboard’s extension tabs?

Which major versions of what, are you asking about?

Cheers!

4

On question 3, the migration side is fine once the extension is enabled from the MPG dashboard. CREATE EXTENSION IF NOT EXISTS checks for the existing extension before it checks whether you’re allowed to create it, so a non-superuser gets a notice instead of an error.

Quick check on plain Postgres 17, with vector already installed by a superuser and a migration role that has no superuser attributes:

mig_owner=> create extension if not exists vector;
NOTICE:  extension "vector" already exists, skipping

mig_owner=> create extension if not exists pg_buffercache;
ERROR:  permission denied to create extension "pg_buffercache"
HINT:  Must be superuser to create this extension.

(vector and pg_buffercache are both untrusted, trusted = f in pg_available_extension_versions.) So keep the IF NOT EXISTS in your migrations and enable the extension through the dashboard first. A plain CREATE EXTENSION vector without IF NOT EXISTS will still fail, it just fails with “already exists” instead.