Re: Re: information on users - Mailing list pgsql-general

On Sun, Apr 08, 2001 at 05:44:28AM -0400, Joel Burton wrote:
> On Tue, 3 Apr 2001, Nick T wrote:
> > I see that we can add users and delete or drop users.  As the database
> > administrator, I'd like to be able to view all of the users and all of the
> > information about them.  Couldn't find out how to do this in the
> > documentation.  Could anyone tell me how to do this?  If there is no utility
> > for this, is there a file that I can view that has this info?
>
> There are 'system catalog tables' that hold information about your
> database. If you use the command \dS in the psql, you'll get the full list
> of these system tables.
>
> pg_user contains the user information, and can be viewed by
> everyone. pg_shadow contains user information plus sensitive password
> information, and can be viewed only by the superuser.

</lurk>

Hmm. When i go to /var/cache/postgresql/data/<pick-a-database-at-random>
and do

    # ls -1 `ls pg_* | grep -v index`

i get

    pg_aggregate
    pg_am
    pg_amop
    pg_amproc
    pg_attrdef
    pg_attribute
    pg_class
    pg_description
    pg_inheritproc
    pg_inherits
    pg_ipl
    pg_language
    pg_listener
    pg_opclass
    pg_operator
    pg_proc
    pg_relcheck
    pg_rewrite
    pg_rules
    pg_statistic
    pg_tables
    pg_trigger
    pg_type
    pg_user
    pg_views

which are all the pg_* items related to specific indexes, tables,
sequences, and so forth, of this particular database.

On the other hand, the postgresql-wide stuff, the stuff that's
valid regardless of which dataset you're in, which tables you
have access to, and the like, all reside (on my debian 2.2
potato system) in /var/lib/postgres/data:

    # cd /var/lib/postgres/data
    # ls -dF1 pg_*
    pg_control
    pg_database
    pg_geqo
    pg_group
    pg_group_name_index
    pg_group_sysid_index
    pg_hba.conf
    pg_ident.conf
    pg_log
    pg_pwd
    pg_pwd.reload
    pg_shadow
    pg_variable
    pg_xlog/

Cool concepts.

So \dS will show you the current pg_* tables appropriate for your
currently-connected database, and also the pg_* that are globally
pertinent (i.e. pertinent regardless of your current database).

What's interesting is that pg_user is PER DATABASE whereas
pg_shadow is DATABASE INDEPENDENT. i have a guess as to why that
is so, but rather than 'open mouth insert foot' i'll let someone
else do the explaining.

--
americans should never read anything so subversive as what's at
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!