Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group