Обсуждение: 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 !
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 "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