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

Started by Gauthier, Daveover 13 years ago6 messagesgeneral

dave.gauthier@intel.com

over 13 years ago

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 !

rosuav@gmail.com

over 13 years ago

In reply to: Gauthier, Dave (#1)

Re: 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

laurenz.albe@cybertec.at

over 13 years ago

In reply to: Gauthier, Dave (#1)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

dave.gauthier@intel.com

over 13 years ago

In reply to: Chris Angelico (#2)

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@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<mailto: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

dave.gauthier@intel.com

over 13 years ago

In reply to: Gauthier, Dave (#4)

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

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> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chris Angelico
Sent: Tuesday, December 04, 2012 11:41 PM
To: pgsql-general@postgresql.org<mailto: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<mailto: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

jeff.janes@gmail.com

over 13 years ago

In reply to: Gauthier, Dave (#5)

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general