Postgres CREATEROLE limit to specific db

Question

In Postgres, how can I create a user that has CREATEROLE-privileges, but only for a specific DB/set of DBs?

I tried doing:

CREATE ROLE user WITH LOGIN PASSWORD 'password' NOCREATEDB CREATEROLE;

Is this correct? + How can I grant CREATEROLE to more than one db?

Answer 1

Roles are created by database cluster, as per the doc, not by database. If you give this powerful right to a user, he will be able to create role usable in every Database of the cluster.

Since you need to grant priviledges to the roles, you can make sure that content from a single DB get assigned to the role.

Answer 2

It seems that it is no a simple way to do it. But you can to create function with security definer option to achieve the desired behavior:

create or replace function fn_create_role(p_name text, p_password text, p_databases text[]) returns void
  language plpgsql
  security definer
as $$
begin
  execute format('create role %I with login password %L;', p_name, p_password);
  execute format('grant connect on database %s to %I', array_to_string(p_databases, ','), p_name);
  return;
end $$;

Create this function as superuser.

Then you could to create the role with NOCREATEROLE option but grant to it the EXECUTE privilege to this function and use it to create another roles.

Note: you need to revoke connect option from public role for particular DBs to disallow roles connect to them by default, for example:

revoke connect on database db1, db2 from public;

Test it:

---

As superuser (nd in my case who also owns the schema with same name)

postgres=# create database db1; create database db2;
CREATE DATABASE
CREATE DATABASE
postgres=# revoke connect on database db1, db2 from public;
REVOKE
postgres=# create role foo with login password 'bar' nocreatedb nocreaterole;
CREATE ROLE
postgres=# set role foo;
SET
postgres=> create role win with login password 'amp' nocreatedb nocreaterole;
ERROR:  permission denied to create role
postgres=> set role nd;
SET
postgres=# grant usage on schema nd to foo;
GRANT
postgres=# grant execute on function nd.fn_create_role(text, text, text[]) to foo;
GRANT
postgres=# set role foo;
SET
postgres=> select nd.fn_create_role('win', 'amp', '{db1}');
┌────────────────┐
│ fn_create_role │
╞════════════════╡
│                │
└────────────────┘
(1 row)

And in terminal:

$ psql -h localhost -d db1 -U win
Password for user win: 
psql (9.6.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

win@db1=> \q
$ psql -h localhost -d db2 -U win
Password for user win: 
psql: FATAL:  permission denied for database "db2"
DETAIL:  User does not have CONNECT privilege.