From: | "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Setting default privs for a user doesn't seem to work. |
Date: | 2012-12-06 22:26:53 |
Message-ID: | 0AD01C53605506449BA127FB8B99E5E13E1173A9@FMSMSX105.amr.corp.intel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
What's wrong with this picture. Trying (failing) to create a user called "select" with default select privs and nothing else. Demo below. Comments in red...
fcadsql7> psql sde
psql (9.1.5)
Type "help" for help.
sde=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
insert | | {}
pgdbadm | Superuser, Create role, Create DB, Replication | {}
select | | {} <-- the "select" user
sde=# alter default privileges for user "select" grant select on tables to "select";
ALTER DEFAULT PRIVILEGES
sde=# alter default privileges for user "select" grant select on sequences to "select";
ALTER DEFAULT PRIVILEGES
sde=# alter default privileges for user "select" grant execute on functions to "select";
ALTER DEFAULT PRIVILEGES
"select" user should now get 'select' priv for all future tables and sequences, and execute functions.
sde=#
sde=# create table foo (a text); <-- note, the "postgres" user is creating the foo table, not "select"
CREATE TABLE
sde=# insert into foo (a) values ('aaa'), ('bbb');
INSERT 0 2
sde=# select * from foo;
a
-----
aaa
bbb
(2 rows)
sde=# \q
fcadsql7> psql --user=select sde <-- connect as "select" user and try to select from the new "foo" table. This fails.
psql (9.1.5)
Type "help" for help.
sde=> select * from foo;
ERROR: permission denied for relation foo <--- Brrrrrt!
sde=>
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-12-06 23:23:56 | Re: Setting default privs for a user doesn't seem to work. |
Previous Message | Sergey Konoplev | 2012-12-06 21:51:47 | Re: unlooged tables |