|
Home > Archive > PostgreSQL Administration > August 2005 > REVOKE from all tables ...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
REVOKE from all tables ...
|
|
| Marc G. Fournier 2005-08-09, 3:24 am |
|
Is there a way of REVOKEing privileges on all tables in a database? I've
checked the REVOKE man page, and there doesn't appear to be, but I figured
I'd double check to make sure ...
---------------------------(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
| |
| Joshua D. Drake 2005-08-09, 3:24 am |
| Marc G. Fournier wrote:
>
> Is there a way of REVOKEing privileges on all tables in a database?
> I've checked the REVOKE man page, and there doesn't appear to be, but
> I figured I'd double check to make sure ...
No. This has actually been a requested feature on the grant side as
well... Nothing a simple for loop can't do with perl though.
>
> ---------------------------(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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Marc G. Fournier 2005-08-09, 3:24 am |
| On Mon, 8 Aug 2005, Joshua D. Drake wrote:
> Marc G. Fournier wrote:
>
>
> No. This has actually been a requested feature on the grant side as well...
> Nothing a simple for loop can't do with perl though.
Ya, that is what I did, it just seemed that there should be a much
simplier way of doing it :( Ah well, at least I wasn't re-creating the
wheel on this one :)
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Stephen Frost 2005-08-09, 11:24 am |
| * Marc G. Fournier (scrappy@postgresql.org) wrote:
> Is there a way of REVOKEing privileges on all tables in a database? I've
> checked the REVOKE man page, and there doesn't appear to be, but I figured
> I'd double check to make sure ...
I actually wrote a little perl script which allows you to pass in a
regexp to match names against, allows for limitation to a specific
schema, and handles tables, views, sequences and functions. I'll see
about making it available (it's not very big). Of course, I think it'd
be nice to have some functions in core that did the same thing; or maybe
some ability in psql to do it.
Stephen
| |
| Guido Barosio 2005-08-09, 11:24 am |
| It would be great to look at that :)
g
On 8/9/05, Stephen Frost <sfrost@snowman.net> wrote:
>
> * Marc G. Fournier (scrappy@postgresql.org) wrote:
> figured
>
> I actually wrote a little perl script which allows you to pass in a
> regexp to match names against, allows for limitation to a specific
> schema, and handles tables, views, sequences and functions. I'll see
> about making it available (it's not very big). Of course, I think it'd
> be nice to have some functions in core that did the same thing; or maybe
> some ability in psql to do it.
>
> Stephen
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFC+K2OrzgMPqB3
kigRAqayAJ9o/ fSLEbDWTPD7LXaAkuhkU
paF1ACeKIAf
> jLhZ8F+CScCkRQtBDg3m
ucc=
> =WoOm
> -----END PGP SIGNATURE-----
>
>
>
--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.
| |
| John DeSoi 2005-08-09, 11:24 am |
|
On Aug 9, 2005, at 9:20 AM, Stephen Frost wrote:
> * Marc G. Fournier (scrappy@postgresql.org) wrote:
>
>
> I actually wrote a little perl script which allows you to pass in a
> regexp to match names against, allows for limitation to a specific
> schema, and handles tables, views, sequences and functions. I'll see
> about making it available (it's not very big). Of course, I think
> it'd
> be nice to have some functions in core that did the same thing; or
> maybe
> some ability in psql to do it.
Also, there are some pl/pgsql functions available from here which
will GRANT/REVOKE on all tables:
http://pgedit.com/node/20
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
|
| scrappy@postgresql.org wrote:
> On Mon, 8 Aug 2005, Joshua D. Drake wrote:
>
>
> Ya, that is what I did, it just seemed that there should be a much
> simplier way of doing it :( Ah well, at least I wasn't re-creating the
> wheel on this one :)
I like doing things like with just psql as shown below. And, I get to
look at the commands before running them. For example: something
like this to revoke all for all tables in the public schema:
-- Turn off headers:
\t
-- Use SQL to build SQL:
SELECT 'REVOKE ALL ON public.' || table_name || ' FROM PUBLIC;'
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema='public
';
-- If the output looks good, write it to a file and run it:
\g out.tmp
\i out.tmp
It works pretty well on similar tasks, at least until you run into string
quote/escape problems.
|
|
|
|
|