Обсуждение: BUG #2137: CREATE DATABASE permission is not inherited.

The following bug has been logged online:

Bug reference:      2137
Logged by:          Chander Ganesan
Email address:      g_chander@yahoo.com
PostgreSQL version: 8.1.1
Operating system:   SLES 9 - linux 2.6.5-7.97-default #1 Fri Jul 2 14:21:59
UTC 2004 i686 i686 i386 GNU/Linux
Description:        CREATE DATABASE permission is not inherited.
Details:

Apparently one needs to do a 'set role' in order to gain access to a 'create
database' privilege, even though inherit is set to "true" for the user.

This is contrary to the documentation - which implies that ineritance is
automatic.

Access privileges (granted with GRANT) seem to flow down correctly.

This could be a documentation issue...


payroll=> select session_user, current_user;
 session_user | current_user
--------------+--------------
 joe          | joe
(1 row)

payroll=> \x
Expanded display is on.
payroll=> select * from pg_roles where rolname in ('joe', 'dba');
-[ RECORD 1 ]-+---------------------
rolname       | dba
rolsuper      | f
rolinherit    | t
rolcreaterole | f
rolcreatedb   | t
rolcatupdate  | f
rolcanlogin   | f
rolconnlimit  | -1
rolpassword   | ********
rolvaliduntil |
rolconfig     |
oid           | 16515
-[ RECORD 2 ]-+---------------------
rolname       | joe
rolsuper      | f
rolinherit    | t
rolcreaterole | f
rolcreatedb   | f
rolcatupdate  | f
rolcanlogin   | t
rolconnlimit  | -1
rolpassword   | ********
rolvaliduntil |
rolconfig     | {search_path=public}
oid           | 16516

payroll=> \du
                                 List of roles
   Role name   | Superuser | Create role | Create DB | Connections | Member
of
---------------+-----------+-------------+-----------+-------------+--------
---
 accounting    | no        | no          | no        | no limit    |
 dba           | no        | no          | yes       | no limit    |
 joe           | no        | no          | no        | no limit    | {dba}
 manufacturing | no        | no          | no        | no limit    |
 payroll       | no        | no          | no        | no limit    |
 postgres      | yes       | yes         | yes       | no limit    |
 root          | yes       | no          | no        | no limit    |
 student       | no        | no          | no        | no limit    |
 student1      | no        | yes         | no        | no limit    |
(9 rows)

payroll=> create database test;
ERROR:  permission denied to create database
payroll=> set role dba;
SET
payroll=> create database test;
ERROR:  database "test" already exists
payroll=> drop database test;
DROP DATABASE
payroll=> reset role;
RESET
payroll=> create database test;
ERROR:  permission denied to create database
payroll=> set role dba;
SET
payroll=> create database test;
CREATE DATABASE
payroll=> select version();
-[ RECORD 1
]---------------------------------------------------------------------------
-----
version | PostgreSQL 8.1.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.3 (SuSE Linux)

"Chander Ganesan" <g_chander@yahoo.com> writes:
> Apparently one needs to do a 'set role' in order to gain access to a 'create
> database' privilege, even though inherit is set to "true" for the user.
> This is contrary to the documentation - which implies that ineritance is
> automatic.

The documentation says no such thing, and in fact says the opposite:

: The INHERIT attribute governs inheritance of grantable privileges
: (that is, access privileges for database objects and role
: memberships). It does not apply to the special role attributes set by
: CREATE ROLE and ALTER ROLE. For example, being a member of a role with
: CREATEDB privilege does not immediately grant the ability to create
: databases, even if INHERIT is set; it would be necessary to become
: that role via SET ROLE before creating a database.

            regards, tom lane