Re: pg_role vs. pg_shadow or pg_user

[Mike Blackwell <mike.blackwell@xxxxxxx>]

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.Blackwell@xxxxxxx
http://www.rrdonnelley.com

On Wed, Mar 14, 2012 at 16:04, Alexander Reichstadt <lxr@xxxxxxx> 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