Re: [GENERAL] pg_role vs. pg_shadow or pg_user

[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 
 which 
cleared things up exhaustively and by example.

Alex

 

Am 14.03.2012 um 22:52 schrieb Tom Lane:

> Alexander Reichstadt  writes:
>> in the documentation of 8.1 the concept of roles is outlined compared
>> to users and groups at
>> .
> 
> Um ... why are you reading 8.1 documentation while running 9.1?  There
> are likely to be some obsolete things in there.
> 
>> 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
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

[Tom Lane]

Alexander Reichstadt  writes:
> in the documentation of 8.1 the concept of roles is outlined compared
> to users and groups at
> .

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

> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

[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
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *


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

[GENERAL] 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 . 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