Skip site navigation (1) Skip section navigation (2)

CREATEDB and CREATEROLE privileges cannot vacuum pg_authid and others

From: Gabriel Ramirez <gabrieloacapulco(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: CREATEDB and CREATEROLE privileges cannot vacuum pg_authid and others
Date: 2008-04-07 05:55:53
Message-ID: 47F9B769.5000002@gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello,


By documentation advice in:
http://www.postgresql.org/docs/8.3/interactive/role-attributes.html

  Tip:  It is good practice to create a role that has the CREATEDB
and CREATEROLE privileges, but is not a superuser, and then use this
role for all routine management of databases and roles. This approach
avoids the dangers of operating as a superuser for tasks that do not
really require it.

I created a user "dba" with above privileges,  with it create one 
database , but fails to run the vacuum command( vacuum, analyze, and 
full all fail with the same error) in some tables with error as:

WARNING:  skipping "pg_authid" --- only table or database owner can 
vacuum it

so its a bug(by the message "database owner can vacuum it" because is 
the owner but fails to vacuum it), or vacuum isn't considered a routine 
management of databases.

second this is totally apart, this user "dba" can grant privileges in 
schema public, but cannot drop that schema (I create my own schemas) 
because the owner of schema public is set to postgres, so dba can create 
a database but don't own it fully.

postgres 8.3.1, Mac OS X 10.4.11, gcc 3.0.1, Xcode 2.4, macports 1.600,

sequence of commands and output follows

~$ createdb test01 -e -E UTF8 -U dba -W
Password:
CREATE DATABASE test01 ENCODING 'UTF8';
~$ psql -U dba test01
Password for user dba:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

test01=> \l
         List of databases
     Name    |  Owner   | Encoding
------------+----------+----------
  postgres   | postgres | UTF8
  template0  | postgres | UTF8
  template1  | postgres | UTF8
  test01     | dba      | UTF8
(5 rows)

test01=> CREATE TABLE mytable (
test01(> id serial PRIMARY KEY,
test01(> mydata varchar(10)
test01(> );
NOTICE:  CREATE TABLE will create implicit sequence "mytable_id_seq" for 
serial column "mytable.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"mytable_pkey" for table "mytable"
CREATE TABLE
test01=> vacuum full analyze;
WARNING:  skipping "pg_authid" --- only table or database owner can 
vacuum it
WARNING:  skipping "pg_database" --- only table or database owner can 
vacuum it
WARNING:  skipping "pg_shdepend" --- only table or database owner can 
vacuum it
WARNING:  skipping "pg_shdescription" --- only table or database owner 
can vacuum it
WARNING:  skipping "pg_auth_members" --- only table or database owner 
can vacuum it
WARNING:  skipping "pg_tablespace" --- only table or database owner can 
vacuum it
WARNING:  skipping "pg_pltemplate" --- only table or database owner can 
vacuum it
VACUUM
test01=> drop schema public;
ERROR:  must be owner of schema public
test01=> \dn
         List of schemas
         Name        |  Owner
--------------------+----------
  information_schema | postgres
  pg_catalog         | postgres
  pg_toast           | postgres
  pg_toast_temp_1    | postgres
  public             | postgres
(5 rows)

test01=>


thanks in advance,


Gabriel

-- 
e-mail: gabrieloacapulco(at)gmail(dot)com

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-04-07 06:02:31
Subject: Re: CREATEDB and CREATEROLE privileges cannot vacuum pg_authid and others
Previous:From: Joe ConwayDate: 2008-04-06 20:36:02
Subject: Re: BUG #3983: pgxs files still missing in win32 install (8.3.1)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group