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

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