0

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?

1
  • 2
    RDS is not a vanilla postgresql. This is a closed fork with several code modifications. They could replace the "!superuser()" check with their RDS_SUPERUSER role membership without introducing separate permissions.
    – Melkij
    May 20 at 10:35
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.