Exporting Roles and Grants in YugabyteDB with ysql_dumpall and ysql_dump

When working with role management in YugabyteDB (or PostgreSQL), it’s common to need a dump of all roles and their associated privileges for backup, migration, or auditing. At first glance, you might expect ysql_dumpall --roles-only to capture everything role-related (including grants on schemas and tables).

But that’s not how it actually works. Let’s break it down.

What `--roles-only` Really Does

The following command:

				
					ysql_dumpall --roles-only --no-role-passwords --database OUR_DATABASE -W

…produces only the global role definitions:

				
					--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres ...;
CREATE ROLE sh_admin;
...

This covers:

● Role creation (CREATE ROLE)
● Role attributes (ALTER ROLE)
● Role memberships

But it does not include object-level privileges (e.g., schema or table grants).

Where Do the Schema Grants Go?

Grants like these:

				
					GRANT ALL ON SCHEMA schema_name TO sh_admin;
GRANT USAGE ON SCHEMA schema_name TO role1;

…are tied to the objects themselves, not the global role catalog.

That means they only appear in per-database dumps, not in the global --roles-only output.

You’ll see them if you run:

				
					ysql_dump --schema-only --dbname OUR_DATABASE -W

How to Export Both Roles and Grants

If you want everything role-related (roles plus object grants) but nothing else, you’ll need a two-step process:

1) Dump roles and memberships (cluster-wide):

				
					ysql_dumpall --roles-only --no-role-passwords > roles.sql

2) Dump grants from a database (schema/table-level):

				
					ysql_dump --schema-only --dbname OUR_DATABASE \
  | grep -E '^(GRANT|REVOKE) ' > grants.sql

For schema-only grants:

				
					ysql_dump --schema-only --dbname OUR_DATABASE \
  | grep -E '^(GRANT|REVOKE) .* ON SCHEMA ' > schema_grants.sql

Repeat step 2 for each non-template database if you want a full picture across the cluster.

Example:

Step 1. Create some roles and objects

				
					-- Connect as superuser (e.g. yugabyte)

-- Roles
CREATE ROLE analyst;
CREATE ROLE dba;
CREATE ROLE app_user;

-- Schema + tables
CREATE SCHEMA sales AUTHORIZATION dba;
CREATE TABLE sales.orders (id int, amount numeric);

CREATE SCHEMA hr AUTHORIZATION dba;
CREATE TABLE hr.employees (id int, name text);

-- Grants
GRANT USAGE ON SCHEMA sales TO analyst;
GRANT SELECT ON sales.orders TO analyst;

GRANT USAGE ON SCHEMA hr TO app_user;
GRANT SELECT, INSERT ON hr.employees TO app_user;

Now we have:

● Three roles (analyst, dba, app_user)
● Two schemas (sales, hr) with one table each
● A handful of schema/table grants

Step 2. Dump only roles

				
					[root@localhost ~]# ysql_dumpall --roles-only --no-role-passwords > roles.sql

[root@localhost ~]# cat roles.sql | grep 'CREATE\|ALTER'
CREATE ROLE analyst;
ALTER ROLE analyst WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE app_user;
ALTER ROLE app_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE datahub;
ALTER ROLE datahub WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE dba;
ALTER ROLE dba WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE yb_db_admin;
ALTER ROLE yb_db_admin WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE yb_extension;
ALTER ROLE yb_extension WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE yb_fdw;
ALTER ROLE yb_fdw WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE yugabyte;
ALTER ROLE yugabyte WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

Notice: only the roles are included. No schema/table grants appear here.

Step 3. Dump all grants from one database

				
					[root@localhost ~]# ysql_dump --schema-only --dbname yugabyte | grep -E '^(GRANT|REVOKE) ' > grants.sql

[root@localhost ~]# cat grants.sql
GRANT USAGE ON SCHEMA hr TO app_user;
GRANT USAGE ON SCHEMA sales TO analyst;
GRANT SELECT,INSERT ON TABLE hr.employees TO app_user;
GRANT SELECT ON TABLE sales.orders TO analyst;

This captures all object-level GRANTs/REVOKEs (schemas, tables, etc.) but not the role definitions themselves.

Step 4. Dump only schema-level grants

				
					[root@localhost ~]# ysql_dump --schema-only --dbname yugabyte | grep -E '^(GRANT|REVOKE) .* ON SCHEMA ' > schema_grants.sql

[root@localhost ~]# cat schema_grants.sql
GRANT USAGE ON SCHEMA hr TO app_user;
GRANT USAGE ON SCHEMA sales TO analyst;

This filters down to just schema privileges, leaving out table-level ones.

Summary

● ysql_dumpall --roles-only = roles and memberships only
● ysql_dump --schema-only = schema objects plus grants
● Combine the two if you need a complete role + grants snapshot

This separation is by design (same as PostgreSQL). With the two-step method above, you’ll always have a clear record of both who your roles are and what they can access.

Have Fun!