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.