Обсуждение: Reference pg_user ?

Hello,

I need users and groups for an application and thought about
using ordinary postgres users and groups.

But I need some additional information like name, email-addres and the
like, so i tried to use something like:

test=# create table users (
test(# shortname name references pg_user,
test(# lastname varchar not null,
test(# email varchar not null
test(# );
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR:  Referenced relation "pg_user" is not a table
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR:  Referenced relation "pg_user" is not a table

Is it somehow possible to reference the system catalog with
the users ?

Thanks, Carsten

--
Dipl. Ing. Carsten Zerbst         |   carsten.zerbst@atlantec-es.com

Carsten Zerbst <carsten.zerbst@atlantec-es.com> writes:

> But I need some additional information like name, email-addres and the
> like, so i tried to use something like:
>
> test=# create table users (
> test(# shortname name references pg_user,
> test(# lastname varchar not null,
> test(# email varchar not null
> test(# );
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  Referenced relation "pg_user" is not a table
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  Referenced relation "pg_user" is not a table
>
> Is it somehow possible to reference the system catalog with
> the users ?

pg_user is a view, not a table.  The actual table is pg_shadow, which
is not (and shouldn't be) readable by non-superusers, so you may run
into problems there.

-Doug
--
Doug McNaught       Wireboard Industries      http://www.wireboard.com/

      Custom software development, systems and network consulting.
      Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...

The problem here is that pg_user is a view, not a real table.  You can
use pg_shadow, but that can only be read by super-users.  Not sure if
that is a solution.

---------------------------------------------------------------------------

Carsten Zerbst wrote:
> Hello,
>
> I need users and groups for an application and thought about
> using ordinary postgres users and groups.
>
> But I need some additional information like name, email-addres and the
> like, so i tried to use something like:
>
> test=# create table users (
> test(# shortname name references pg_user,
> test(# lastname varchar not null,
> test(# email varchar not null
> test(# );
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  Referenced relation "pg_user" is not a table
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  Referenced relation "pg_user" is not a table
>
> Is it somehow possible to reference the system catalog with
> the users ?
>
> Thanks, Carsten
>
> --
> Dipl. Ing. Carsten Zerbst         |   carsten.zerbst@atlantec-es.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

You can use a one-to-one table (as you are trying); you can use an inherited
table.

In either case, tho', pg_shadow is the user table; pg_user is a view that
hides the password column.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Carsten Zerbst
> Sent: Tuesday, March 19, 2002 10:33 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Reference pg_user ?
>
>
> Hello,
>
> I need users and groups for an application and thought about
> using ordinary postgres users and groups.
>
> But I need some additional information like name, email-addres and the
> like, so i tried to use something like:
>
> test=# create table users (
> test(# shortname name references pg_user,
> test(# lastname varchar not null,
> test(# email varchar not null
> test(# );
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  Referenced relation "pg_user" is not a table
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  Referenced relation "pg_user" is not a table
>
> Is it somehow possible to reference the system catalog with
> the users ?
>
> Thanks, Carsten
>
> --
> Dipl. Ing. Carsten Zerbst         |   carsten.zerbst@atlantec-es.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>