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