"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
"permissiondenied 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