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 mike.blackw...@rrd.com http://www.rrdonnelley.com <http://www.rrdonnelley.com/> * <mike.blackw...@rrd.com>* On Wed, Mar 14, 2012 at 16:04, Alexander Reichstadt <l...@mac.com> wrote: > 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 >
