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
