Difference between ALTER ROLE WITH CREATEDB and GRANT CREATE ON TABLESPACE

Coming from MySQL and not knowing about ROLEs I absentmindedly tried this

GRANT CREATE ON TABLESPACE pg_default TO username;

It didn't have the desired effect. The command that I was looking for was:开发者_开发知识库

ALTER ROLE username WITH CREATEDB;

But what's the difference precisely? Does giving someone the CREATEDB role implicitly give them CREATE ON TABLESPACE ...? Is there a table where I can see all this?

From the docs, GRANT CREATE ON TABLESPACE means (my emphasis):

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

They are completely different privileges. CREATEDB means the role can create database. If your role doesn't have that, it can't create databases, period.

Granting CREATE for a tablespace to a role means that the role will be able to use that tablespace as default tablespace for the database. So a role that has CREATEDB will be able to create a database; just not on that tablespace. Note that there's always a pg_default tablespace that will be used as default tablespace for databases that don't otherwise have a default tablespace.

Tablespaces are not the same as databases.

It looks like the first statement gives you the ability to create tablespaces. This are physical files (or spaces on your storage device) that hold the data/indexes/... of the database.

So effectively you granted two different rights with the commands.