The following bug has been logged on the website:
Bug reference: 7791
Logged by: Brice Breeden
Email address: zardozwildman@hotmail.com
PostgreSQL version: 9.1.7
Operating system: windows x64
Description: =
This database creation script was executed via the postgres superuser.
Executes create database and the "with owner" clause.
Create database assigns ownership to a role (myta_admin_role). This is not a
login role.
After database creation, script creates a schema. I later discovered that
the role never gets usage permission on the schema. Even though it is the
database owner.
My workaround was to remove the "with owner" clause. And explicitly set
schema permissions on the role.
I am providing a portion of the script for reference.
do
$$
begin
if (not exists (select * from information_schema.enabled_roles where
lower(role_name)=3Dlower('myta_admin_role'))) then
create role myta_admin_role superuser createdb createrole replication;
end if;
end;
$$;
do
$$
begin
if (not exists (select * from information_schema.enabled_roles where
lower(role_name)=3Dlower('myta_svc_role'))) then
create role myta_svc_role;
end if;
end;
$$;
-- Database creation can't be made conditional in postgres.
-- NOTE: Declaring one of our roles as owner doesn't seem to work.
-- Postgres doesn't give myta_admin_role usage rights on the myta schema.
-- Even though it's declared as the owner.
-- So we'll let ownership default to the superuser. And grant
-- the rights ourselves.
create database mytadb
with owner=3Dmyta_admin_role
encoding=3D'UTF8'
tablespace=3Dpg_default
LC_COLLATE=3D'English_United States.1252'
LC_CTYPE=3D'English_United States.1252'
CONNECTION LIMIT =3D -1;
\connect mytadb
do
$$
begin
if (not exists (select * from pg_namespace where nspname=3D'myta')) then
create schema myta;
end if;
end;
$$;
