Re: 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] On Behalf Of Chris Angelico
Sent: Tuesday, December 04, 2012 11:41 PM
To: 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