I'm trying to set up a role in Postgres which can a) Create a new Role b) Create a new Database c) Make that new Role the owner of the database d) Have no other rights (as far as is possible!)
I have tried this:
sc_1=# CREATE ROLE tenant_admin CREATEDB CREATEROLE; CREATE ROLE sc_1=# CREATE ROLE user1 IN ROLE tenant_admin LOGIN NOINHERIT ENCRYPTED PASSWORD 'xyz'; CREATE ROLE sc_1=#
Followed by (in another session)
tahaan@Komputer:~/projects/acme-project$ psql -U user1 -h localhost -d postgres Password for user user1: psql (9.3.6) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. postgres=> SET ROLE tenant_admin; SET postgres=> CREATE DATABASE "Tenant1" TEMPLATE "tenant_template"; CREATE DATABASE postgres=> CREATE ROLE "owner1"; CREATE ROLE postgres=> ALTER DATABASE "Tenant1" OWNER TO "owner1"; ERROR: must be member of role "owner1" postgres=>
Background: The requirement is to have an automated function that can setup separate databases in a multi-tenant system. My hope is that this function can be performed by a role that does not have too much rights.