BUG #2137: CREATE DATABASE permission is not inherited. - Mailing list pgsql-bugs

From Chander Ganesan
Subject BUG #2137: CREATE DATABASE permission is not inherited.
Date
Msg-id 20051230215715.DEFEDF0AC6@svr2.postgresql.org
Whole thread Raw
Responses Re: BUG #2137: CREATE DATABASE permission is not inherited.
List pgsql-bugs
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)

pgsql-bugs by date:

Previous
From: BFRACI@aol.com
Date:
Subject: Re: BUG #2102: Backend reports wrong number of affected rows for a
Next
From: Michael Fuhr
Date:
Subject: Re: BUG #2136: plperl doesn't work, plperlu - yes