pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Paul Ogden <pogden@claresco.com>
To: Andrew G. Hammond <drew@xyzzy.dhs.org>
Cc: Eric Du <duxy@CDSC.COM.CN>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to grant a privilege on all tables or views or both of a database to someone?
Date: Thu, 7 Mar 2002 13:01:29 -0800
Message-ID: <NAEOJBHEEOEHNNICGFADIEOACDAA.pogden@claresco.com> (raw)
In-Reply-To: <20020307204059.GA22890@xyzzy.dhs.org>

Andrew,

That's a nice solution.  Was wondering if you think its feasible to do the
same, but as only one function with an additional parameter for relation
type ( table, view, sequence, all ).

Also, say I have tables owned by different users ( ie some tables owned by a
taskmanager process and others owned by web application ) and I only want to
issue grants on objects owned by one of these users?

Thanks,

Paul Ogden

-----Original Message-----
From: Andrew G. Hammond [mailto:drew@xyzzy.dhs.org]
Sent: Thursday, March 07, 2002 12:41
To: Paul Ogden
Cc: Eric Du; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to grant a privilege on all tables or views or
both of a database to someone?


On Thu, Mar 07, 2002 at 10:55:51AM -0800, Paul Ogden wrote:
> How to grant a privilege on all tables or views or both of a database to
> someone?Here's what I did, using psql:

Personally I solved this using plpgsql:

-- usage: SELECT grant_all('privilidge', 'user');
-- grants privilidge (SELECT, UPDATE, INSERT, DELETE or ALL) to the user
-- for all non-pg_ objects in the database (except indices).

DROP FUNCTION grant_all(text, text);
CREATE FUNCTION grant_all(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind <> ''i''
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables(text, text);
CREATE FUNCTION grant_all_tables(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind = ''r''
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables_and_views(text, text);
CREATE FUNCTION grant_all_tables_and_views(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind  IN (''r'', ''v'')
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

--
Andrew G. Hammond  mailto:drew@xyzzy.dhs.org  http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F              613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me




view thread (4+ messages)

Message-ID: <NAEOJBHEEOEHNNICGFADIEOACDAA.pogden@claresco.com>
Permalink:  ../NAEOJBHEEOEHNNICGFADIEOACDAA.pogden@claresco.com/
Also on:    postgresql.org/message-id/NAEOJBHEEOEHNNICGFADIEOACDAA.pogden@claresco.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: pogden@claresco.com, drew@xyzzy.dhs.org, duxy@CDSC.COM.CN
  Subject: Re: How to grant a privilege on all tables or views or both of a database to someone?
  In-Reply-To: <NAEOJBHEEOEHNNICGFADIEOACDAA.pogden@claresco.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox