Thread: 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.gauthier@intel.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

Dave Gauthier 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
>    Name    |  Owner  | Encoding | Collate |    Ctype    |  Access privileges
> -----------+---------+----------+---------+-------------+---------------------
>  postgres  | pgdbadm | UTF8     | C       | en_US.UTF-8 |
>  sde       | pgdbadm | UTF8     | C       | en_US.UTF-8 |
>  template0 | pgdbadm | UTF8     | C       | en_US.UTF-8 | =c/pgdbadm         +
>            |         |          |         |             | pgdbadm=CTc/pgdbadm
>  template1 | pgdbadm | UTF8     | C       | en_US.UTF-8 | =c/pgdbadm         +
>            |         |          |         |             | pgdbadm=CTc/pgdbadm
> (4 rows)
>
> postgres=# \du
>                              List of roles
>  Role name |                   Attributes                   | Member of
> -----------+------------------------------------------------+-----------
>  insert    |                                                | {}
>  pgdbadm   | Superuser, Create role, Create DB, Replication | {}
>  select    |                                                | {}

I'm not surprised; there probably is no schema "sde" in your
current database.

Could it be that you mix up databases and schemas?

> Bottom line is that I want this "select" user to be able to query all tables yet to be created in the
> DB without having to issue grant statments after table craation.  But just select, no more.

Use the command ALTER DEFAULT PRIVILEGES:
http://www.postgresql.org/docs/9.1/static/sql-alterdefaultprivileges.html

At the risk to confuse you, I'll mention that there is no
ALTER DEFAULT PRIVILEGES for schema objects.
So it could still be that your user cannot access a table
if it is in a schema on which she has no USAGE privilege.

Yours,
Laurenz Albe

On Wed, Dec 5, 2012 at 7:02 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Actually, maybe it didn't work.  What's wrong with this picture...
>
>
> sde=# alter default privileges for user "select" grant select on tables to
> "select";

Remove the 'for user "select"'

What that does is make the default permissions apply only to tables
created *by* the user "select".

This is essentially a no-op, since the user that creates an object
already has privs on those objects.  In fact, this usage should
probably generate a warning.

Cheers,

Jeff