ALTER DEFAULT PRIVs / not working for me
От | Michael Rau |
---|---|
Тема | ALTER DEFAULT PRIVs / not working for me |
Дата | |
Msg-id | 201011161832.00922.michael.rau@iqundu.com обсуждение исходный текст |
Ответы |
Re: ALTER DEFAULT PRIVs / not working for me
|
Список | 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.
В списке pgsql-novice по дате отправления: