CREATEDB and CREATEROLE privileges cannot vacuum pg_authid and others - Mailing list 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=20
database , but fails to run the vacuum command( vacuum, analyze, and=20
full all fail with the same error) in some tables with error as:

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

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

second this is totally apart, this user "dba" can grant privileges in=20
schema public, but cannot drop that schema (I create my own schemas)=20
because the owner of schema public is set to postgres, so dba can create=20
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=3D> \l
         List of databases
     Name    |  Owner   | Encoding
------------+----------+----------
  postgres   | postgres | UTF8
  template0  | postgres | UTF8
  template1  | postgres | UTF8
  test01     | dba      | UTF8
(5 rows)

test01=3D> CREATE TABLE mytable (
test01(> id serial PRIMARY KEY,
test01(> mydata varchar(10)
test01(> );
NOTICE:  CREATE TABLE will create implicit sequence "mytable_id_seq" for=20
serial column "mytable.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index=20
"mytable_pkey" for table "mytable"
CREATE TABLE
test01=3D> vacuum full analyze;
WARNING:  skipping "pg_authid" --- only table or database owner can=20
vacuum it
WARNING:  skipping "pg_database" --- only table or database owner can=20
vacuum it
WARNING:  skipping "pg_shdepend" --- only table or database owner can=20
vacuum it
WARNING:  skipping "pg_shdescription" --- only table or database owner=20
can vacuum it
WARNING:  skipping "pg_auth_members" --- only table or database owner=20
can vacuum it
WARNING:  skipping "pg_tablespace" --- only table or database owner can=20
vacuum it
WARNING:  skipping "pg_pltemplate" --- only table or database owner can=20
vacuum it
VACUUM
test01=3D> drop schema public;
ERROR:  must be owner of schema public
test01=3D> \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=3D>


thanks in advance,


Gabriel

--=20
e-mail: gabrieloacapulco@gmail.com