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