Thread: BUG #7791: create database with owner, owner does not get usage on schema

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;
$$;

zardozwildman@hotmail.com writes:
> 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.

This isn't a bug.  A database owner just owns the database (and hence
can rename or drop it).  The owner doesn't magically have additional
permissions on the objects therein.  It'd be a security hole if he did,
at least for superuser-owned objects such as the core functions and
operators ... and I gather that you created this schema as superuser.

We could argue about whether database owners should have extra
privileges on objects belonging to ordinary users, but it'd be rather
inconsistent to do that IMO.  It makes more sense for the object owner
to have to grant privileges to the database owner, if the latter is to
be able to access the object.

            regards, tom lane