Postgresql – How to Amazon RDS grant CREATE EXTENSION to their RDS_SUPERUSER role

amazon-rdspostgresql

I've been trying for some time to allow ordinary, non-superuser users to create extensions.

While reading across RDS documentation, I find that their internal "master user", rds_superuser, is capable of creating extensions, even though it is not an actual superuser, as is well documented.

Further searching leads me to this table:

|Database Engine|System privilege|Database role|
|---------------|----------------|-------------|
|PostgreSQL     |`CREATE ROLE, CREATE DB, PASSWORD VALID UNTIL INFINITY, CREATE EXTENSION, ALTER EXTENSION, DROP EXTENSION, CREATE TABLESPACE, ALTER < OBJECT> OWNER, CHECKPOINT, PG_CANCEL_BACKEND(), PG_TERMINATE_BACKEND(), SELECT PG_STAT_REPLICATION, EXECUTE PG_STAT_STATEMENTS_RESET(), OWN POSTGRES_FDW_HANDLER(), OWN POSTGRES_FDW_VALIDATOR(), OWN POSTGRES_FDW, EXECUTE PG_BUFFERCACHE_PAGES(), SELECT PG_BUFFERCACHE`|RDS_SUPERUSER

When I try executing grant create extension to any user, I get a syntax error.

How is this grant possible?

Best Answer

CREATE EXTENSION is not a grantable privilege. It is hard-coded who can create extensions.

In PostgreSQL, this is restricted to superusers, and from v13 on to users with the CREATE privileges on the database (if the extension is a "trusted" one).

Note, however, that RDS is different from PostgreSQL, and godJeff only knows how they hacked up the code.

Related Question