Обсуждение: ALTER DEFAULT PRIVs / not working for me

Hi,

I am very excited about the new features of 9.0 to ALTER DEFAULT PRIVILEGES.
But I cannot get it working. What am I doing wrong? My understanding is, that
the following should give R/W access to user2 of a table which has been
created by user1.

The complete SQL flow is:

| ----------------------------------------------------------------------

$ sudo -u postgres psql
psql (9.0.1)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=# create role iquser;
CREATE ROLE
postgres=# create user hdi with in group iquser password 'hdi';
CREATE ROLE
postgres=# create user mra with in group iquser password 'mra';
CREATE ROLE

| ----------------------------------------------------------------------
| NOTE: not sure, if the following is necessary.
| ----------------------------------------------------------------------

postgres=# grant iquser to postgres;
GRANT ROLE
postgres=# \c test;
You are now connected to database "test".
test=# create schema tmp;
CREATE SCHEMA
test=# grant create, usage on schema tmp to iquser;
GRANT
test=# alter default privileges for role iquser GRANT ALL ON TABLES to iquser;
ALTER DEFAULT PRIVILEGES
test=# \q

| ----------------------------------------------------------------------
| now login as user1 to create a table
| ----------------------------------------------------------------------

$ export PGUSER=mra
$ export PGPASSWORD=mra
$ psql -h localhost test
psql (9.0.1)
Type "help" for help.

test=> create table tmp.mra1 (id integer);
CREATE TABLE
test=> \q

| ----------------------------------------------------------------------
| now login as user2 to select the table
| ----------------------------------------------------------------------

$ export PGUSER=hdi
$ export PGPASSWORD=hdi
$ psql -h localhost test
psql (9.0.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

test=> select * from tmp.mra1;
ERROR:  permission denied for relation mra1

| ----------------------------------------------------------------------

Any help explaining 1) the ERROR: permission denied and 2) what needs to be
done to define a TMP schema, where all users have access to all tables created
in the future is highly appreciated.

Thanks for your work.

Michael.

Michael Rau <michael.rau@iqundu.com> writes:
> I am very excited about the new features of 9.0 to ALTER DEFAULT PRIVILEGES.
> But I cannot get it working. What am I doing wrong?

I think you misunderstand this:

> test=# alter default privileges for role iquser GRANT ALL ON TABLES to iquser;

That says to change the default privileges for tables that are
subsequently created by role iquser.  It does not apply to tables
created by any other role.  In particular it does not recursively
apply to roles that happen to be a member of the group iquser.

            regards, tom lane

Tom, this clarified things for me. Thanks very much for your support.

Michael.

Am Dienstag, 16. November 2010, um 19:06:51 schrieb Tom Lane:
> Michael Rau <michael.rau@iqundu.com> writes:
> > I am very excited about the new features of 9.0 to ALTER DEFAULT
> > PRIVILEGES. But I cannot get it working. What am I doing wrong?
>
> I think you misunderstand this:
> > test=# alter default privileges for role iquser GRANT ALL ON TABLES to
> > iquser;
>
> That says to change the default privileges for tables that are
> subsequently created by role iquser.  It does not apply to tables
> created by any other role.  In particular it does not recursively
> apply to roles that happen to be a member of the group iquser.
>
>             regards, tom lane