pg_role vs. pg_shadow or pg_user

Hi,

in the documentation of 8.1 the concept of roles is outlined compared to
users and groups at <
http://www.postgresql.org/docs/8.1/static/user-manag.html&gt;. I am running
9.1 and due to currently learning about the ins and outs of users and
permissions in postgres as opposed to mysql, and because of needing to read
system tables, I also read today that pg_shadow is the real table
containing the users as opposed to pg_user which is only a view and one
never displaying anything but **** for the password. I don't have the link
where that was, but anyways, this lead me to check:

PW=# select * FROM pg_catalog.pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl |
passwd | valuntil | useconfig

----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | t |
md5d63999e27600a80bb728cc0d7c2d6375 | |
testa | 24761 | f | f | f | f |
md52778dfab33f8a7197bce5dfaf596010f | |
(2 rows)

PW=# select * FROM pg_catalog.pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb |
rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword |
rolvaliduntil | rolconfig | oid

----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
postgres | t | t | t | t | t
| t | t | -1 | ******** |
| | 10
testa | f | t | f | f | f
| t | f | -1 | ******** |
| | 24761
abcd | f | t | f | f | f
| f | f | -1 | ******** |
| | 24762
testb | f | t | f | f | f
| f | f | -1 | ******** |
| | 24763
(4 rows)
^
PW=# select * FROM pg_catalog.pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl |
passwd | valuntil | useconfig

----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres | 10 | t | t | t | t |
******** | |
testa | 24761 | f | f | f | f |
******** | |
(2 rows)

Why is there a difference in these tables? Shouldn't pg_user, pg_shadow
and pg_roles have entries where usename equals rolename and moreover should
contain the same amount of entries?

testb was created doing

*create role testb with role testa*
*
*
I was assuming that this would sort of clone the settings of testa into a
new user testb. testa was created using "create user".

Regards
Alex

Alexander Reichstadt <lxr@mac.com> writes:

in the documentation of 8.1 the concept of roles is outlined compared
to users and groups at
<http://www.postgresql.org/docs/8.1/static/user-manag.html&gt;.

Um ... why are you reading 8.1 documentation while running 9.1? There
are likely to be some obsolete things in there.

I also read today that pg_shadow is the real table containing the
users as opposed to pg_user which is only a view and one never
displaying anything but **** for the password. I don't have the link
where that was,

Whereever it was, it was even more obsolete than the 8.1 docs.
pg_shadow has been a view (on pg_authid) for quite a while now.
Try "\d+ pg_shadow" in psql.

The reason this is such a mess is that we've changed the catalog
representation several times, each time leaving behind a view that
was meant to emulate the old catalog. For some time now, pg_authid
has been the ground truth, but it stores entries for both login and
non-login roles, which more or less correspond to what used to be
users and groups. pg_roles is the only non-protected view that
shows you all the entries.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general