Can a Non-Superuser create DBs for other users in Postgres 10?-CodePudding

I need an non-superuser to create dbs and users, and assign this users to its respective DB, but this "start" user should not be able to delete other DBs or basically nothing more than create users and DBs.

I have this code, it can create DBs and users, but cant assign the DB to the created users. It returns: must be member of role "...":

CREATE ROLE starter NOSUPERUSER LOGIN CREATEDB CREATEROLE NOREPLICATION NOBYPASSRLS NOINHERIT WITH ENCRYPTED PASSWORD 'mypassword';
REVOKE SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER ON ALL TABLES IN SCHEMA public FROM starter;

*Also tried with create user

Is there a way to allow "starter" user to add owner to a DB?

I'm using this query for that:

CREATE DATABASE newdatabase OWNER test;

Output:

ERROR:  must be member of role "test"

CodePudding user response：

The short answer is not really, certainly not without a fair bit of custom extension. Basically. since CREATE DATABASE isn't transactionally safe, it cannot be called from a function, or even a stored procedure (I tried).

The result is that a non-superuser can only assign a database to a role he or she is a member of.

There are a couple of things that could be done instead: you could create a "parked" role that databases could go into until being claimed, and then you could have everyone be a member of that parked role, and then able to claim the databases.

It ought to be possible to solve this with a C-language extension too but this would not work on managed services.

As an additional note based on the comment, you are likely to need to run REASSIGN OWNED when claiming the database (https://www.postgresql.org/docs/current/sql-reassign-owned.html)

CodePudding user response：

I found a solution or fix to achieve this, inspired by Chris' idea and this article (and own work). First the setup, as super user:

CREATE ROLE Switch NOSUPERUSER LOGIN PASSWORD 'mypassword'; 
CREATE USER UserCreateAccount NOSUPERUSER LOGIN CREATEDB CREATEROLE NOREPLICATION NOBYPASSRLS NOINHERIT PASSWORD 'mypassword' IN ROLE Switch; 
REVOKE SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER ON ALL TABLES IN SCHEMA public FROM UserCreateAccount;

That creates the role "switch" and the "userCreateAccount", both nonsuperuser and with restricted privileges. Now using "userCreateAccount" in psql:

CREATE USER NewUser IN ROLE Switch PASSWORD 'mypassword';
CREATE DATABASE NewDB OWNER Switch;
GRANT ALL PRIVILEGES ON DATABASE NewDB TO NewUser;

Now, being "UserCreateAccount" and "NewUser" on "Switch" role, and "NewDB" owned by switch, i just simple reassign ownership. As "NewUser" in psql:

REASSIGN OWNED BY Switch TO NewUser

Finally, but don't know if necessary as UserCreateAccount in psql:

REVOKE Switch FROM NewUser;

Done that, i can use the whole NewDB and owned by NewUser