6

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.

8

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
  • Why are you using double quotes? – user1071847 Apr 17 '18 at 15:37
  • 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. – Johan Apr 18 '18 at 16:14
  • But why use quotes at all? E.g. why "owner3" not owner3? – user1071847 Apr 18 '18 at 16:44
  • 1
    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. – Johan Apr 19 '18 at 17:40
4

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 ....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.