In the YugabyteDB Tip “Part 1: Let Non-Superusers Query yb_local_tablets in YugabyteDB” we showed how to wrap the yb_local_tablets system function in a SECURITY DEFINER function so that non-superusers could safely inspect tablet metadata without elevated privileges.
Now let’s take that one step further… creating user-friendly views and JSON outputs, while keeping everything secure and auditable.
Recap: Why We Needed a Wrapper
The built-in yb_local_tablets is restricted:
ERROR: only superusers and yb_db_admin can query yb_local_tablets
By defining a SECURITY DEFINER function (owned by an admin) that executes yb_local_tablets internally, we can safely expose the data:
CREATE OR REPLACE FUNCTION util.yb_local_tablets_ro()
RETURNS TABLE (...)
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
SELECT * FROM yb_local_tablets();
$$;
Non-admins get read access through GRANT EXECUTE.
Option 1: A Simple View on Top of the Secure Function
If you’d rather not make users remember the function name, create a view on top of it:
-- Run this as a superuser
CREATE OR REPLACE VIEW util.yb_local_tablets AS
SELECT * FROM util.yb_local_tablets_ro();
Then grant minimal access:
-- Run these steps as a superuser
GRANT USAGE ON SCHEMA util TO cam;
GRANT EXECUTE ON FUNCTION util.yb_local_tablets_ro() TO cam;
GRANT SELECT ON util.yb_local_tablets TO cam;
Result:
Users can query with a simple SELECT * FROM util.v_yb_local_tablets; while the security definer function does the privileged work.
Example:
yugabyte=> SELECT current_user;
current_user
--------------
cam
(1 row)
yugabyte=> SELECT * FROM util.yb_local_tablets LIMIT 2;
tablet_id | table_id | table_type | namespace_name | ysql_schema_name | table_name | partition_key_start | partition_key_end | state
----------------------------------+----------------------------------+------------+----------------+------------------+---------------------+---------------------+-------------------+-------------------
3d1a92d2454a43dab38a2a167fb3ad8d | 000034cb000030008000000000004013 | YSQL | yugabyte | util | mv_yb_local_tablets | | | TABLET_DATA_READY
5cb909600c114fc68af52c693bdd2b81 | 05e069ad93a442d9b50246412bd4fd07 | System | system | | transactions | \xc000 | \xe000 | TABLET_DATA_READY
(2 rows)
Note:
A plain view alone won’t bypass the built-in privilege check, because the underlying function still runs as the invoker. That’s why the secure function layer is essential.
Option 2: JSON Row Output
In some use cases (dashboards, logs, API exports), you might want a JSON representation instead of typed columns.
Here’s a per-row JSON version:
-- Run these steps as a superuser
CREATE OR REPLACE FUNCTION util.yb_local_tablets_json_rows()
RETURNS SETOF jsonb
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
SELECT jsonb_build_object(
'tablet_id', tablet_id,
'table_id', table_id,
'table_type', table_type,
'namespace_name', namespace_name,
'ysql_schema_name', ysql_schema_name,
'table_name', table_name,
'partition_key_start', encode(partition_key_start, 'hex'),
'partition_key_end', encode(partition_key_end, 'hex'),
'state', state
)
FROM yb_local_tablets();
$$;
REVOKE ALL ON FUNCTION util.yb_local_tablets_json_rows() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION util.yb_local_tablets_json_rows() TO cam;
Usage:
SELECT jsonb_pretty(j)
FROM util.yb_local_tablets_json_rows() AS j
LIMIT 2;
Example:
yugabyte=> SELECT current_user;
current_user
--------------
cam
(1 row)
yugabyte=> \a
Output format is unaligned.
yugabyte=> SELECT jsonb_pretty(j)
yugabyte-> FROM util.yb_local_tablets_json_rows() AS j
yugabyte-> LIMIT 2;
jsonb_pretty
{
"state": "TABLET_DATA_READY",
"table_id": "05e069ad93a442d9b50246412bd4fd07",
"tablet_id": "5cb909600c114fc68af52c693bdd2b81",
"table_name": "transactions",
"table_type": "System",
"namespace_name": "system",
"ysql_schema_name": "",
"partition_key_end": "e000",
"partition_key_start": "c000"
}
{
"state": "TABLET_DATA_READY",
"table_id": "05e069ad93a442d9b50246412bd4fd07",
"tablet_id": "091c0ecdd3664d59960b1bc3d545575e",
"table_name": "transactions",
"table_type": "System",
"namespace_name": "system",
"ysql_schema_name": "",
"partition_key_end": "a000",
"partition_key_start": "8000"
}
(2 rows)
Each tablet appears as a separate JSON row, easy to feed into observability tooling.
Option 3: A Materialized View (Cached Snapshot)
For monitoring dashboards that don’t need real-time tablet info, a materialized view gives the best of both worlds… safe access and fast reads.
-- Run these steps as a superuser
CREATE MATERIALIZED VIEW util.mv_yb_local_tablets AS
SELECT * FROM util.yb_local_tablets_ro();
GRANT SELECT ON util.mv_yb_local_tablets TO cam;
Example:
yugabyte=> SELECT current_user;
current_user
--------------
cam
(1 row)
yugabyte=> SELECT * FROM util.mv_yb_local_tablets LIMIT 2;
tablet_id | table_id | table_type | namespace_name | ysql_schema_name | table_name | partition_key_start | partition_key_end | state
----------------------------------+----------------------------------+------------+----------------+------------------+--------------+---------------------+-------------------+-------------------
2895827a20c54d3eb8f03828f29b5bce | 05e069ad93a442d9b50246412bd4fd07 | System | system | | transactions | \x6000 | \x8000 | TABLET_DATA_READY
ea9b7bf1b69e460f86ada1229f0c59cb | 05e069ad93a442d9b50246412bd4fd07 | System | system | | transactions | \x2000 | \x4000 | TABLET_DATA_READY
(2 rows)
Make sure to refresh it periodically as a superuser (via pg_cron, cron, or a script):
-- Run this as a superuser
REFRESH MATERIALIZED VIEW util.mv_yb_local_tablets;
Summary
By layering a secure definer function under your views or JSON functions, you can:
● Safely expose tablet metadata to non-superusers
● Build friendly SQL and JSON interfaces
● Support dashboards and automation
● Maintain strict least-privilege principles
This pattern applies broadly, not just to yb_local_tablets, but to any restricted YSQL function you want to expose safely in YugabyteDB.
Have Fun!
