The following bug has been logged online:
Bug reference: 2137
Logged by: Chander Ganesan
Email address: [EMAIL PROTECTED]
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)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
