Thread

Columns in pg_shadow?

Michael A. Mayo <mmayo@mcauleybrooklyn.org> — 2000-05-20T05:08:13Z

I have been looking for information on the meaning of each column in
pg_shadow, but have been unable to find any thus far.  It would be good if
someone could enlighten me, or point me to existing documentation.  The
columns I am unsure of are listed below:

usecreatedb:
    Ability to create new databases?

usetrace:
    ?

usesuper:
    ?
     I assume this makes the user a "superuser," but I am not sure exactly
what that means.

usecatupd:
    Ability to change system tables?


       Thanks,
                  -Mike

Re: Columns in pg_shadow?

Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-20T06:01:22Z

"Michael A. Mayo" <mmayo@mcauleybrooklyn.org> writes:
> I have been looking for information on the meaning of each column in
> pg_shadow, but have been unable to find any thus far.  It would be good if
> someone could enlighten me, or point me to existing documentation.  The
> columns I am unsure of are listed below:

> usecreatedb:
>     Ability to create new databases?

Check.

> usetrace:
>     ?

Searching through the sources shows that this flag is not used anywhere.
The catalogs.sgml doc file defines it as "can user set trace flags?"
but whatever code it controlled must be long dead...

> usesuper:
>      I assume this makes the user a "superuser," but I am not sure exactly
> what that means.

A superuser is God as far as the database is concerned: she's not
subject to any protection checks, and there are some security-critical
operations like backend COPY that are only allowed to superusers.
Pretty much the same concept as being "root" on a Unix system.

> usecatupd:
>     Ability to change system tables?

Right.  The point of this flag is to let superusers be slightly less
Godlike: by turning off her usecatupd flag, a superuser can revoke her
right to alter system tables via direct INSERT/UPDATE/DELETE commands,
and thereby avoid foolish mistakes.  Or that seemed to be the plan
anyway.  Since there's no really convenient interface for twiddling
this flag, I doubt anyone actually bothers to change it.  It starts
out the same as one's usesuper flag, and probably stays that way...

BTW, the easiest way to learn about this sort of stuff is to scan the
source code --- and if you don't have a handy tool for that, allow me
to recommend "glimpse", http://glimpse.cs.arizona.edu/.  I wasn't
too sure about usetrace or usecatupd either, but it took just a few
seconds to examine their uses and learn what I said above.  (For fans of
the One True Editor: I have an emacs macro that invokes glimpse in the
same way as grep is called by the standard "grep" macro, so that you can
step through all the hits with C-x `.  Let me know if you need it.)

			regards, tom lane

Re: Columns in pg_shadow?

Giles Lean <giles-pgsql@nemeton.com.au> — 2000-05-20T09:31:03Z

[ The spam filter's biting me.  If I've managed to send duplicates of
  this message I appologise and ask that someone let me know please! ]

On Sat, 20 May 2000 02:01:22 -0400  Tom Lane wrote:

> BTW, the easiest way to learn about this sort of stuff is to scan the
> source code --- and if you don't have a handy tool for that, allow me
> to recommend "glimpse", http://glimpse.cs.arizona.edu/.

For alternatives that understand source code as code, I recommend
global or cscope:

     http://www.tamacom.com/global/
     http://cscope.sourceforge.net

FreeBSD users will find global has been integrated into the base
system; NetBSD users can add it from the packages collection and I'm
fairly sure there are Linux versions around too.

Regards,

Giles

Re: Columns in pg_shadow?

Peter Eisentraut <peter_e@gmx.net> — 2000-05-21T16:45:31Z

Tom Lane writes:

> > usetrace:
> Searching through the sources shows that this flag is not used anywhere.
> The catalogs.sgml doc file defines it as "can user set trace flags?"
> but whatever code it controlled must be long dead...

Remnants of a three-quarters baked pg_options implementation I suppose.


> > usecatupd:
> >     Ability to change system tables?

> Since there's no really convenient interface for twiddling this flag,
> I doubt anyone actually bothers to change it.  It starts out the same
> as one's usesuper flag, and probably stays that way...

Also note that once you unset this flag you can't set it again because you
no longer have write access to pg_shadow.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: Columns in pg_shadow?

Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-21T17:16:39Z

Peter Eisentraut <peter_e@gmx.net> writes:
>>>> usecatupd:
>>>> Ability to change system tables?

>> Since there's no really convenient interface for twiddling this flag,
>> I doubt anyone actually bothers to change it.  It starts out the same
>> as one's usesuper flag, and probably stays that way...

> Also note that once you unset this flag you can't set it again because you
> no longer have write access to pg_shadow.

Yeek.  Gun ... foot ... shoot ...

I suppose you'd still have the rights to create new users, so you could
create a new superuser and then log in as him to fix the problem.

			regards, tom lane

Re: Columns in pg_shadow?

Michael A. Mayo <mmayo@mcauleybrooklyn.org> — 2000-05-22T19:32:23Z

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> BTW, the easiest way to learn about this sort of stuff is to scan the
> source code --- and if you don't have a handy tool for that, allow me
> to recommend "glimpse", http://glimpse.cs.arizona.edu/.  I wasn't
> too sure about usetrace or usecatupd either, but it took just a few
> seconds to examine their uses and learn what I said above.  (For fans of
> the One True Editor: I have an emacs macro that invokes glimpse in the
> same way as grep is called by the standard "grep" macro, so that you can
> step through all the hits with C-x `.  Let me know if you need it.)

    Many thanks for your response; that was exactly what I wanted to know.

    I always assumed that in order to check the source code, I would have to
know which file the particular bit of code is in.  Somehow, I never thought
of applying ultra-modern computing techniques like "searches."  ;)  In the
near future, I will try setting up glimpse or one of the other tools Giles
mentioned.

    The emacs macro could certianly be useful; I would appreciate it if you
could send it as an attachment, or better yet, post it on the postgresql web
page so everyone can enjoy it. =)

                -Mike

Re: Columns in pg_shadow?

Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-22T23:41:58Z

"Michael A. Mayo" <mmayo@mcauleybrooklyn.org> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> the One True Editor: I have an emacs macro that invokes glimpse in the
>> same way as grep is called by the standard "grep" macro, so that you can
>> step through all the hits with C-x `.  Let me know if you need it.)

>     The emacs macro could certianly be useful;

It's just a shameless ripoff of the standard 'grep' macro from
compile.el.  (I'm still using Emacs 19, not sure if there are any
changes needed for Emacs 20.)


;; Create 'glimpse', which is essentially just like 'grep' except that
;; we add </dev/null instead of /dev/null to the end of the command.

(require 'compile)

(defvar glimpse-command "glimpse -n "
  "Last glimpse command used in \\[glimpse]; default for next glimpse.")
(defvar glimpse-history nil)

(defun glimpse (command-args)
  "Run glimpse, with user-specified args, and collect output in a buffer.
While glimpse runs asynchronously, you can use the \\[next-error] command
to find the text that glimpse hits refer to.

This command uses a special history list for its arguments, so you can
easily repeat a glimpse command."
  (interactive
   (list (read-from-minibuffer "Run glimpse (like this): "
			       glimpse-command nil nil 'glimpse-history)))
  (let ((buf (compile-internal (concat command-args " <" grep-null-device)
			       "No more glimpse hits" "glimpse"
			       ;; We can use the same match regexp as for grep.
			       nil grep-regexp-alist)))
    (save-excursion
      (set-buffer buf)
      (set (make-local-variable 'compilation-exit-message-function)
	   (lambda (status code msg)
	     (if (eq status 'exit)
		 (cond ((zerop code)
			'("finished\n" . "done"))
		       ((= code 1)
			'("finished with no matches found\n" . "no match"))
		       (t
			(cons msg code)))
	       (cons msg code)))))))



			regards, tom lane