Postgres Pro
Downloads
Home   >   mailing lists

Thread: alter default privileges problem

alter default privileges problem

From
"Gauthier, Dave"
Date:

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 !

 

Re: alter default privileges problem

From
"Gauthier, Dave"
Date:

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 !

 

Re: alter default privileges problem

From
Tom Lane
Date:
"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