patternsqlModerate
Creating a database under a different owner
Viewed 0 times
ownercreatingdatabasedifferentunder
Problem
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:
Followed by (in another session)
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.
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.
Solution
I found a solution involving a few extra steps. The "tenant_admin" role is still created the same way, but it is now used as follow:
postgres=> SET ROLE tenant_admin;
SET
postgres=> CREATE ROLE "owner3";
CREATE ROLE
postgres=> GRANT "owner3" TO "tenant_admin";
GRANT ROLE
postgres=> CREATE DATABASE "Tenant3" OWNER "owner3";
CREATE DATABASE
postgres=> REVOKE "owner3" from "tenant_admin";
REVOKE ROLECode Snippets
postgres=> SET ROLE tenant_admin;
SET
postgres=> CREATE ROLE "owner3";
CREATE ROLE
postgres=> GRANT "owner3" TO "tenant_admin";
GRANT ROLE
postgres=> CREATE DATABASE "Tenant3" OWNER "owner3";
CREATE DATABASE
postgres=> REVOKE "owner3" from "tenant_admin";
REVOKE ROLEContext
StackExchange Database Administrators Q#96368, answer score: 12
Revisions (0)
No revisions yet.