Search Postgresql Archives

Re: pg_role vs. pg_shadow or pg_user

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux