alter default privileges problem

The fix had to do with connecting as the "insert" user, then setting the default privs. My mistake was to run the "alter default privileges..." as the superuser.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Thursday, January 03, 2013 2:09 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] alter default privileges problem

v9.1 on linux

Connect to postgres DB, then...

create user "select" password 'select';
create user "insert" password 'insert';
alter default privileges for user "insert" grant select on tables to "select";
alter default privileges for user "insert" grant select on sequences to "select";
alter default privileges for user "insert" grant execute on functions to "select";

Disconnect. Reconnect as user "insert", then...

create table foo (a text);
insert into foo (a) values ('aaa');

Disconnect. Reconnect as user "select", expecting to be able to select contents of the "foo" table, but fails with "permission denied for relation foo".

Bottom line is that I want the "select" user to be able to query any table, sequence or use any function created by user "insert".

Thanks for any help !

"Gauthier, Dave" <dave.gauthier@intel.com> writes:

create user "select" password 'select';
create user "insert" password 'insert';
alter default privileges for user "insert" grant select on tables to "select";
alter default privileges for user "insert" grant select on sequences to "select";
alter default privileges for user "insert" grant execute on functions to "select";

Disconnect. Reconnect as user "insert", then...

create table foo (a text);
insert into foo (a) values ('aaa');

Disconnect. Reconnect as user "select", expecting to be able to select contents of the "foo" table, but fails with "permission denied for relation foo".

Works for me. Maybe you've got some schema search path confusion,
or some such? "\dp foo" in psql might be enlightening, too. What
I see is

regression=> \dp foo
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+-----------------------+--------------------------
public | foo | table | select=r/insert +|
| | | insert=arwdDxt/insert |
(1 row)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general