ALTER DEFAULT PRIVs / not working for me

From: Michael Rau <michael(dot)rau(at)iqundu(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: ALTER DEFAULT PRIVs / not working for me
Date: 2010-11-16 17:32:00
Message-ID: 201011161832.00922.michael.rau@iqundu.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-11-16 18:06:51 Re: ALTER DEFAULT PRIVs / not working for me
Previous Message Mladen Gogala 2010-11-16 14:23:37 Re: Postgresql certifications