grant select on all tables of schema or database

when u connect to the database type:
\h GRANT
and you will get all the Grant options:

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

This will grant the privileges on all tables under the database ....

HTH

Gene <genekhart@gmail.com> wrote: It seems like it should be a very easy problem to solve I just need one role to have select privileges on all the tables of a particular schema or database including any new tables that are created since they are created programmatically daily. I've combed google and the docs to no avail. Do I need to write pg/plsql scripts just to so something like that?? I believe on other dbms you can just say grant all on schema.* or something to that effect. The script i found below would be ok if tables weren't being created constantly.

using version 8.1.4

thanks,
Gene

----
CREATE OR REPLACE FUNCTION pgx_grant(text, text, text)
RETURNS int4 AS
$BODY$
DECLARE
priv ALIAS FOR $1;
patt ALIAS FOR $2;
user ALIAS FOR $3;
obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class
WHERE relname LIKE patt || '%' AND relkind in ('r','v','S') LOOP
EXECUTE 'GRANT ' || priv || ' ON ' || obj.relname || ' TO ' || user;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION pgx_grant(text, text, text) OWNER TO root;

---------------------------------
Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.

2006/9/28, Najib Abi Fadel <nabifadel@yahoo.com>:

when u connect to the database type:
\h GRANT
and you will get all the Grant options:

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

This will grant the privileges on all tables under the database ....

HTH

Gene <genekhart@gmail.com> wrote:
It seems like it should be a very easy problem to solve I just need one
role to have select privileges on all the tables of a particular schema or
database including any new tables that are created since they are created
programmatically daily. I've combed google and the docs to no avail. Do I
need to write pg/plsql scripts just to so something like that?? I believe on
other dbms you can just say grant all on schema.* or something to that
effect. The script i found below would be ok if tables weren't being created
constantly.

using version 8.1.4

thanks,
Gene

----
CREATE OR REPLACE FUNCTION pgx_grant(text, text, text)
RETURNS int4 AS
$BODY$
DECLARE
priv ALIAS FOR $1;
patt ALIAS FOR $2;
user ALIAS FOR $3;
obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class
WHERE relname LIKE patt || '%' AND relkind in ('r','v','S') LOOP
EXECUTE 'GRANT ' || priv || ' ON ' || obj.relname || ' TO ' || user;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION pgx_grant(text, text, text) OWNER TO root;

I don't get it. I grant all privileges on a database to a role, but
the server won't let it access the schemas. I grant all privileges on
the schema to the same role, but the server won't let it access the
relations in the schema.

GRANT ALL ON DATABASE testdb TO myuser
GRANT ALL ON SCHEMA testschema TO myuser;

Any idea what I'm doing wrong?

TIA,
t.n.a.

On Wed, Dec 13, 2006 at 01:42:32PM +0000, Tomi N/A wrote:

I don't get it. I grant all privileges on a database to a role, but
the server won't let it access the schemas. I grant all privileges on
the schema to the same role, but the server won't let it access the
relations in the schema.

GRANT ALL ON DATABASE testdb TO myuser
GRANT ALL ON SCHEMA testschema TO myuser;

Any idea what I'm doing wrong?

Did you grant access to the individual tables?

I don't beleive you have to explicitly grant access to the database, or
the schema, but you definitly have to grant access to the tables
directly.

Have you used \z to check the permissions? Please post actual psql
output.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

I don't beleive you have to explicitly grant access to the database, or
the schema, but you definitly have to grant access to the tables
directly.

They're completely separate privileges. GRANT ON DATABASE grants or
revokes permissions associated with database-level operations, not
permissions on specific objects contained in the database. Likewise
for GRANT ON SCHEMA.

What the OP seems to be wishing for is a wild-card grant operation,
viz

GRANT ALL ON TABLE *.* TO joeblow

which would indeed be a useful thing to have, but it's *not* GRANT ON
DATABASE.

regards, tom lane

That would indeed be a handy feature... I was surprised when I discovered
this was not available like in mysql, it's a real pain to grant permissions
to a bunch of tables without querying table metadata.

GRANT ALL ON TABLE *.* TO joeblow

which would indeed be a useful thing to have, but it's *not* GRANT ON
DATABASE.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Gene Hart
cell: 443-604-2679

2006/12/13, Tom Lane <tgl@sss.pgh.pa.us>:

Martijn van Oosterhout <kleptog@svana.org> writes:

I don't beleive you have to explicitly grant access to the database, or
the schema, but you definitly have to grant access to the tables
directly.

They're completely separate privileges. GRANT ON DATABASE grants or
revokes permissions associated with database-level operations, not
permissions on specific objects contained in the database. Likewise
for GRANT ON SCHEMA.

What the OP seems to be wishing for is a wild-card grant operation,
viz

GRANT ALL ON TABLE *.* TO joeblow

which would indeed be a useful thing to have, but it's *not* GRANT ON
DATABASE.

Exactly.
Thank you Martijn and Tom for the help and clarification.

Cheers,
t.n.a.