Automatically Grant Privileges on New YugabyteDB Databases

When managing a multi-tenant or automation-heavy PostgreSQL-compatible database like YugabyteDB, a common administrative challenge is ensuring that newly created databases consistently apply the right privileges to certain users.

Wouldn’t it be nice if every new database automatically granted SELECT on all tables in the public schema to a specific user?

Well, with a clever use of the template1 database, this is actually possible, but it comes with some important caveats and security implications.

The Problem: Privileges Aren’t Inherited by Default

Let’s say you create a role named some_user:

				
					$ ysqlsh -h 127.0.0.1 -c "CREATE ROLE some_user WITH LOGIN;"

Now, you create a new database, new_db, and a table in it:

				
					$ ysqlsh -h 127.0.0.1 -c "CREATE DATABASE new_db;"
$ ysqlsh -h 127.0.0.1 -d new_db -c "CREATE TABLE t AS SELECT generate_series(1, 3) c;"

By default, if some_user tries to select from t, they’ll get a permission denied error:

				
					$ ysqlsh -h 127.0.0.1 -d new_db -U some_user -c "SELECT * FROM t;"
ERROR:  permission denied for table t

So how do we fix that?

The Trick: Grant Privileges in `template1`

In PostgreSQL and YugabyteDB, every new database is created by cloning a template. By default, that template is the template1 database. Any objects or privileges present in template1 will be inherited by new databases. This means you can “preload” default privileges into it:

				
					$ ysqlsh -h 127.0.0.1 -d template1 -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO some_user;"
$ ysqlsh -h 127.0.0.1 -d template1 -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO some_user;"

Now, when you create a new database:

				
					$ ysqlsh -h 127.0.0.1 -c "CREATE DATABASE new_db;"
$ ysqlsh -h 127.0.0.1 -d new_db -c "CREATE TABLE t AS SELECT generate_series(1, 3) c;"

The some_user role automatically has SELECT access:

				
					$ ysqlsh -h 127.0.0.1 -d new_db -U some_user -c "SELECT * FROM t;"
 c
---
 3
 1
 2
(3 rows)

Magic! 🎩✨

Is This Secure?

Now let’s talk about the elephant in the room: security.

The ability to inherit privileges from template1 is incredibly powerful, but also potentially dangerous:

• Unintentional privilege propagation: You might forget that template1 contains grants to certain roles, meaning every future database will carry those grants—even in environments where you didn’t intend to.
• Hard to audit: There’s no visual cue or flag on a new database indicating that it inherited grants from template1. This makes it easy to overlook in security reviews.
• Potential privilege escalation: If you accidentally grant INSERT or ALL privileges in template1, new databases may be wide open to misuse by non-privileged users.

For these reasons, use this technique very cautiously. It’s best suited for controlled environments, like dev/test setups, or where a specific automation use case justifies the tradeoff.

Best Practices

If you choose to use template1 for privilege propagation, here are a few tips:

• Document everything: Keep a changelog or script that shows exactly what you’ve granted in template1.
• Audit periodically: Run \dp and \ddp in template1 occasionally to review existing privileges and default privileges.
• Consider custom templates: If different teams or services need different privileges, you can create a custom template (e.g., template_myapp) and use it when creating new databases via CREATE DATABASE mydb TEMPLATE template_myapp;.

Summary

Using template1 to predefine privileges in new YugabyteDB databases is a clever hack that can simplify repetitive GRANT operations. It leverages the templated nature of new databases to enforce consistent access controls, but it’s not without risk.

Be deliberate, document your changes, and always weigh convenience against security.

Have Fun!