Обсуждение: pg_role vs. pg_shadow or pg_user
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
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@rrd.com
http://www.rrdonnelley.com
On Wed, Mar 14, 2012 at 16:04, Alexander Reichstadt <lxr@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 | ******** | | | 10testa | f | t | f | f | f | t | f | -1 | ******** | | | 24761abcd | f | t | f | f | f | f | f | -1 | ******** | | | 24762testb | 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 doingcreate role testb with role testaI was assuming that this would sort of clone the settings of testa into a new user testb. testa was created using "create user".RegardsAlex
Alexander Reichstadt <lxr@mac.com> writes: > 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>. 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
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 <http://www.postgresql.org/docs/9.0/interactive/role-membership.html> which cleared things up exhaustively and by example.
Alex
Am 14.03.2012 um 22:52 schrieb Tom Lane:
Alexander Reichstadt <lxr@mac.com> writes:in the documentation of 8.1 the concept of roles is outlined comparedto users and groups at<http://www.postgresql.org/docs/8.1/static/user-manag.html>.
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 theusers as opposed to pg_user which is only a view and one neverdisplaying anything but **** for the password. I don't have the linkwhere 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