pg_role vs. pg_shadow or pg_user

From: Alexander Reichstadt <lxr(at)mac(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_role vs. pg_shadow or pg_user
Date: 2012-03-14 21:04:20
Message-ID: 7BDDD192-E621-4D37-A0F2-EE4B9D49FCB8@mac.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Blackwell 2012-03-14 21:12:22 Re: pg_role vs. pg_shadow or pg_user
Previous Message Tom Lane 2012-03-14 20:54:38 Re: Searching email, Full Text Search prefix, not expected results