Creating a database under a different owner

Question

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.

Answer 1

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 ROLE

• user1071847: Why are you using double quotes?
• Johan: Things in double-quotes are inspected and used to reference to other things. Single quotes are for human-readable string values that are not de-referenced by Postgres.
• user1071847: But why use quotes at all? E.g. why "owner3" not owner3?
• Johan: At the time I wrote this I was busy automating the process. Many of the names were derived from things entered by users of the apps. So just generally a good idea to quote all of them.

Answer 2

Had this problem with RDS when trying to create new DB.

To solve it:

Login as superuser

psql --host=xxxxxxx --port=5432 --username=SUPERUSER_NAME --password --dbname=postgres

Create the User

CREATE USER newuser WITH CREATEDB PASSWORD 'password';

Logout

\q

Login as newuser

psql --host=xxxxxxx --port=5432 --username=newuser --password --dbname=postgres

Create your DB

CREATE DATABASE ....

Answer 3

To Creating a user in postgres, Creating a database under a different user in postgres, Creating a schema under a different database with new user in postgres,

Login as Postgres-user

psql --host=xxxxxxx --port=5432 --username=postgres --password

create a Database:

postgres=>CREATE DATABASE newdb;

Create New-User:

postgres=>CREATE USER user1 WITH PASSWORD 'uSeRi04o8';

Grant permission:

postgres=>GRANT ALL PRIVILEGES ON DATABASE "newdb" to user1;

Login to New-Database with new user:

postgres=> \connect newdb user1
...
You are now connected to database "newdb" as user "user1".
newdb=> 

To create schema with new user "user1" in newdb:

newdb=> CREATE SCHEMA s1;

To list all the schema in postgres from specific db :

SELECT * from information_schema.schemata;