Re: pg_role vs. pg_shadow or pg_user

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(dot)Blackwell(at)rrd(dot)com
http://www.rrdonnelley.com

<http://www.rrdonnelley.com/>
* <Mike(dot)Blackwell(at)rrd(dot)com>*

On Wed, Mar 14, 2012 at 16:04, Alexander Reichstadt <lxr(at)mac(dot)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
>