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
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 |