Postgres unable to create db after granting privs to role

Question

I'm sure I'm missing something simple, but I've created the following:

postgres=# \du
                          List of roles
 Role name |               Attributes                | Member of
-----------+-----------------------------------------+-----------
 admin     | No inheritance, Create DB, Cannot login | {}
 postgres  | Superuser, Create role, Create DB       | {}
 wade      |                                         | {admin}

(Note that Cannot login and No inheritance don't affect what's happening to wade, here. See the PostgreSQL documentation for role membership to understand why. —bignose)

However, when I try to create a db, I get:

bin wwilliam$ createdb -U wade test
Password:
createdb: database creation failed: ERROR:  permission denied to create database

What am I missing?

Perhaps "Cannot login" is the cause.

hsmiths
– hsmiths

2011-05-15 02:30:00 +00:00
Commented May 15, 2011 at 2:30 — hsmiths
– hsmiths, Commented May 15, 2011 at 2:30
Nope - I can login as user wade.

wadesworld
– wadesworld

2011-05-15 02:35:33 +00:00
Commented May 15, 2011 at 2:35 — wadesworld
– wadesworld, Commented May 15, 2011 at 2:35
try logging in and running CREATE DATABASE.

Kevin
– Kevin

2011-05-15 02:38:07 +00:00
Commented May 15, 2011 at 2:38 — Kevin
– Kevin, Commented May 15, 2011 at 2:38
@Kevin - same thing. permission denied.

wadesworld
– wadesworld

2011-05-15 06:20:55 +00:00
Commented May 15, 2011 at 6:20 — wadesworld
– wadesworld, Commented May 15, 2011 at 6:20

Milen A. Radev · Accepted Answer · 2011-05-15 08:13:39Z

10

An excerpt from the manual:

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.

(Emphasis mine).

answered May 15, 2011 at 8:13

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hung Nguyen · Accepted Answer · 2018-12-29 02:01:20Z

0

In documentation:

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute

So you must activate admin role using SET ROLE admin; before creating DB.

edited Dec 29, 2018 at 2:01

answered Dec 29, 2018 at 1:48

Hung Nguyen

1,3912 gold badges14 silver badges20 bronze badges

Collectives™ on Stack Overflow

Postgres unable to create db after granting privs to role

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related