[BUGS] CREATEDB and CREATEROLE privileges cannot vacuum pg_authid and others

[Gabriel Ramirez]

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.









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(> );

CREATE TABLE
test01=> vacuum full analyze;

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: [EMAIL PROTECTED]

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. 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" 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

signature.asc
Description: OpenPGP digital signature