PostgreSQL Bugs - BUG #2137: CREATE DATABASE permission is not inherited.

Author

BUG #2137: CREATE DATABASE permission is not inherited.

Chander Ganesan

2005-12-31, 3:23 am

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 | & #123;search_path=pub
lic}
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

Tom Lane

2005-12-31, 8:23 pm

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links