pg_role vs. pg_shadow or pg_user

[Alexander Reichstadt]

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>. 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

[Mike Blackwell]

You only get pg_shadow entries for roles that can login (rolcanlogin =
true).

CREATE ROLE defaults to NO LOGIN. CREATE USER defaults to LOGIN. See
http://www.postgresql.org/docs/9.1/interactive/sql-createrole.html

__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
***@rrd.com
http://www.rrdonnelley.com


<http://www.rrdonnelley.com/>

Post by Alexander Reichstadt
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>. 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
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

[Tom Lane]

Post by Alexander Reichstadt
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>.

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

Post by Alexander Reichstadt
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

[Alexander Reichstadt]

The 8.1 version of the docu explicitly outlined the migration, the 9.1 version no longer covers the way things were before 8.1. In the meantime I also found <http://www.postgresql.org/docs/9.0/interactive/role-membership.html> which cleared things up exhaustively and by example.

Alex

Post by Tom Lane

Post by Alexander Reichstadt
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>.

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

Post by Alexander Reichstadt
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
--
http://www.postgresql.org/mailpref/pgsql-general