Re: how do I grant select to one user for all tables in a DB?

Actually, maybe it didn't work. What's wrong with this picture...

.

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 | | {}

sde=# drop table foo;
DROP TABLE
sde=# \q
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 | | {}

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
sde=#
sde=# create table foo (a text);
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
sde=>

.

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Wednesday, December 05, 2012 8:00 AM
To: Chris Angelico; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] how do I grant select to one user for all tables in a DB?

This worked. Thank You Chris!

One problem remains. The "select" user can also create tables, and then insert into them. Need to prevent "select" user from being able to create tables. When "select" user was created, no privs given to it...

postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
insert | | {}
pgdbadm | Superuser, Create role, Create DB, Replication | {}
select | | {}

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Chris Angelico
Sent: Tuesday, December 04, 2012 11:41 PM
To: pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] how do I grant select to one user for all tables in a DB?

On Wed, Dec 5, 2012 at 2:12 PM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com<mailto:dave(dot)gauthier(at)intel(dot)com>> wrote:
> V9.1.5 on linux
> User "select" created (yup, that's right, they want the user name to
> be "select". Guess what ptivs it is to have! Don't kill the messanger
> :-) )
>
> postgres=# grant select on all tables in schema sde to "select";
>
> ERROR: schema "sde" does not exist
>
> postgres=# \l
>
> List of databases

Your immediate problem is that sde is a database, not a schema.
They're different things, despite MySQL conflating the terms.

What you're trying to do is a perfectly reasonable way to create a backup user. And it's definitely possible; check out ALTER DEFAULT
PRIVILEGES:

http://www.postgresql.org/docs/9.1/static/sql-alterdefaultprivileges.html

I think that's what you need there!

ChrisA

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general