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

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

From
"Gauthier, Dave"
Date:

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

 

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.

 

Thanks in Advance !

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

From
Chris Angelico
Date:
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


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

From
Albe Laurenz
Date:
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


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

From
"Gauthier, Dave"
Date:
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chris Angelico
Sent: Tuesday, December 04, 2012 11:41 PM
To: pgsql-general@postgresql.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.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:
 
 
I think that's what you need there!
 
ChrisA
 
 
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
 

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

From
"Gauthier, Dave"
Date:
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Wednesday, December 05, 2012 8:00 AM
To: Chris Angelico; pgsql-general@postgresql.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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chris Angelico
Sent: Tuesday, December 04, 2012 11:41 PM
To: pgsql-general@postgresql.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.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:
 
 
I think that's what you need there!
 
ChrisA
 
 
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
 
 

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

From
Jeff Janes
Date:
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