1

While using the database service for postgresql om azure, it looks like it is not possible to create a custom template database.

What I want to achieve, is that a regular account, csn create new databases with a specific extension enabled. The creation can be delegated, but the enabling of the extension fails in my test for all but the initial database admin account.

maRtin
  • 6,336
  • 11
  • 43
  • 66
Wouter
  • 371
  • 2
  • 12
  • I don't know about Azure services, but with a stock Postgres you can use _any_ database as a template. It's not necessary to mark it as a template database. The only restriction is, that no connection is allowed to the source database if you clone it. –  Nov 03 '17 at 07:38
  • Thanks, will check if that works for me – Wouter Nov 03 '17 at 11:38
  • Azure does not allow a regular database to be used as a template. They changed parts of the permission system, but could not find any documentation about what should work. – Wouter Nov 05 '17 at 20:59

1 Answers1

1

I've just tried the following in Azure Database for PostgreSQL and it worked (please bear with me as I walk you through my steps in Azure).

  1. In Azure CLI (bash) followed steps in Quick Start for Azure PostgreSQL to create a new resource group > create a PG server in it > create a new DB (originaldb) on that PG server. All worked just fine.
  2. Then I enabled a earthdistance and cube (pre-req for earthdistance) extensions for orginaldb (the DB I created in step 1).
  3. Then I used CLI to create another DB (dbclone) using orginaldb as a template: CREATE DATABASE dbclone TEMPLATE originaldb; It worked just fine and cube and earthdistance extensions are enabled in dbclone DB.
  4. Now on to trying it with another PG user: I created a PG user (user1) and granted this user DB creation privilege.
  5. Then I logged on to my server as user1 and created another DB from CLI using the same command: CREATE DATABASE dbclone2 TEMPLATE originaldb; It worked too and I see that cube and earthdistance extensions are enabled in dbclone2 database.

Is that what you're trying to do? Are you hitting errors following the same steps or you're trying to do something different?

Nik Larin - Azure Data
  • 30
  • 5
  • That is roughly what I did. Steps I took are: 1. create a database with the following command: CREATE DATABASE my_template LC_COLLATE 'en-US' LC_CTYPE 'en-US' ENCODING 'UTF8' TEMPLATE template0; 2. Connected to that database via \c my_template (same session) and called CREATE EXTENSION tablefunc; 3. Now created a session with a user that is capable of creating databases and tried the command: CREATE DATABASE sub TEMPLATE my_template; 3. fails with 'ERROR permission denied to copy database "my_template" – Wouter Nov 10 '17 at 14:07
  • I finally found what I did do different. I use the main postgres admin account to create a subadmin account (with only create role & create db permissions). Using that role, I can follow your steps above. But using this role, to create a new role e.g. clientdb. Than this user cannot access the my_template database. Even when the my_template database is created by the 'subadmin', it cannot be created. The main difference is, that the clientdb role cannot create new roles while the subadmin can. Does this make any sense @NikLarinMSFT? – Wouter Nov 10 '17 at 14:44
  • Yes, I see. As far as I can tell it's not specific to PostgreSQL in Azure but is the same in any Postgres, e.g. a locally installed instance. – Nik Larin - Azure Data Nov 11 '17 at 03:23
  • I am not sure. I adapted my code that us running find since the postgresql 8 series. Only change allowing c language extension to be replaced by the tablefunc, as I need crosstab. Created a few thousand databases the described above on multiple instances of postgresql. Only other change is the username that now requires an instance. – Wouter Nov 11 '17 at 06:26
  • @Wouter if you can list minimum steps to replicate what work in, say, local PostgreSQL instance and doesn't work in PostgreSQL in Azure, we can look into that. I tried to create another PG role that can login and can create databases but doesn't have any other PG privileges and I was able to repro your error when I'm trying to create a DB using a template. Behavior is identical on my local PG 9.6 and on PG (9.6) in Azure. – Nik Larin - Azure Data Nov 17 '17 at 21:33
  • 1. Create a new database instance with admin user postgres. 2. login ad postgres user, issue CREATE DATABASE my_template 3. (optional) connect als postgres user to my_template, execute CREATE EXTENSION tablefunc; 4. Create user test with createrole permission 5. Connect to postgres database as user test, create role db with createdb permissions 6. Connect with db account to postgres database and issue CREATE DATABASE db TEMPLATE my_template. Step 6 will result in an permission problem. I tried this using a 'basic' database sku – Wouter Nov 29 '17 at 09:40