Thread: grant select on all tables of schema or database

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;

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/
> From each according to his ability. To each according to his ability to litigate.

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



--
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.