information on users

On Tue, 3 Apr 2001, Nick T wrote:

Howdy all:

I'm brand new to postgresql, so don't beat me too severely about the head
and shoulders over this question.

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?

Welcome to PostgreSQL.

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.

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

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!

will trillich wrote:

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.

pg_user is a view, and, in 7.0.x and earlier, views are empty tables with
rules attached. These are created by copying template1 into every new
database.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Finally, brethren, whatsoever things are true,
whatsoever things are honest, whatsoever things are
just, whatsoever things are pure, whatsoever things
are lovely, whatsoever things are of good report; if
there be any virtue, and if there be any praise, think
on these things." Philippians 4:8