Thread: existence of user

existence of user

From
"Annie Bai"
Date:
Hi,

After I used 'create user', Is there a database to keep all the information
about users? How can i check the existence of users?

Thanks,
Yan

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.


Re: existence of user

From
Stephen Ingram
Date:
On Saturday 02 February 2002 07:18 pm, Annie Bai wrote:
> Hi,
>
> After I used 'create user', Is there a database to keep all the information
> about users? How can i check the existence of users?
>
> Thanks,
> Yan
>

Hi,

Sure. Check out the pg_user table.

Postgres 'system' tables all seem to start with 'pg_'

si@guinness > psql -c "select * from pg_user"
 usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |  passwd  | valuntil
----------+----------+-------------+----------+----------+-----------+----------+----------
 postgres |       26 | t           | t        | t        | t         | ******** |
 si       |       27 | t           | f        | t        | t         | ******** |
 nrg      |       30 | t           | f        | t        | t         | ******** |
 testuser |       28 | t           | f        | t        | t         | ******** |
 sm       |       31 | t           | f        | t        | t         | ******** |
 tc       |       32 | t           | f        | t        | t         | ******** |
 nrgadmin |       33 | t           | f        | t        | t         | ******** |
 dude     |       29 | t           | f        | t        | t         | ******** |
 ken      |       34 | t           | f        | t        | t         | ******** |
(9 rows)

steve


> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: existence of user

From
"Annie Bai"
Date:
Hi Steve,

How can I access the system databases? when I use '\l', i can only get those
databases created by specific users. Is there a particular directory holding
all the system databases?

Thanks a lot


>From: Stephen Ingram <ingram@samsix.com>
>Reply-To: ingram@samsix.com
>To: "Annie Bai" <annie_job@hotmail.com>, pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] existence of user
>Date: Sat, 2 Feb 2002 19:35:27 -0500
>
>On Saturday 02 February 2002 07:18 pm, Annie Bai wrote:
> > Hi,
> >
> > After I used 'create user', Is there a database to keep all the
>information
> > about users? How can i check the existence of users?
> >
> > Thanks,
> > Yan
> >
>
>Hi,
>
>Sure. Check out the pg_user table.
>
>Postgres 'system' tables all seem to start with 'pg_'
>
>si@guinness > psql -c "select * from pg_user"
>  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
>passwd  | valuntil
>----------+----------+-------------+----------+----------+-----------+----------+----------
>  postgres |       26 | t           | t        | t        | t         |
>******** |
>  si       |       27 | t           | f        | t        | t         |
>******** |
>  nrg      |       30 | t           | f        | t        | t         |
>******** |
>  testuser |       28 | t           | f        | t        | t         |
>******** |
>  sm       |       31 | t           | f        | t        | t         |
>******** |
>  tc       |       32 | t           | f        | t        | t         |
>******** |
>  nrgadmin |       33 | t           | f        | t        | t         |
>******** |
>  dude     |       29 | t           | f        | t        | t         |
>******** |
>  ken      |       34 | t           | f        | t        | t         |
>******** |
>(9 rows)
>
>steve
>
>
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster


_________________________________________________________________
Join the world�s largest e-mail service with MSN Hotmail.
http://www.hotmail.com


Re: existence of user

From
"Annie Bai"
Date:
Hi Steve,

I tried the command as you did
%psql -c "select * from pg_user"

but I got an error as 'Database ""select * from pg_user"" does not exist in
the system catalog. Previous connection kept'.

Need I change some configurations?

Thanks,
Annie


>From: Stephen Ingram <ingram@samsix.com>
>Reply-To: ingram@samsix.com
>To: "Annie Bai" <annie_job@hotmail.com>, pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] existence of user
>Date: Sat, 2 Feb 2002 19:35:27 -0500
>
>On Saturday 02 February 2002 07:18 pm, Annie Bai wrote:
> > Hi,
> >
> > After I used 'create user', Is there a database to keep all the
>information
> > about users? How can i check the existence of users?
> >
> > Thanks,
> > Yan
> >
>
>Hi,
>
>Sure. Check out the pg_user table.
>
>Postgres 'system' tables all seem to start with 'pg_'
>
>si@guinness > psql -c "select * from pg_user"
>  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
>passwd  | valuntil
>----------+----------+-------------+----------+----------+-----------+----------+----------
>  postgres |       26 | t           | t        | t        | t         |
>******** |
>  si       |       27 | t           | f        | t        | t         |
>******** |
>  nrg      |       30 | t           | f        | t        | t         |
>******** |
>  testuser |       28 | t           | f        | t        | t         |
>******** |
>  sm       |       31 | t           | f        | t        | t         |
>******** |
>  tc       |       32 | t           | f        | t        | t         |
>******** |
>  nrgadmin |       33 | t           | f        | t        | t         |
>******** |
>  dude     |       29 | t           | f        | t        | t         |
>******** |
>  ken      |       34 | t           | f        | t        | t         |
>******** |
>(9 rows)
>
>steve
>
>
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster


_________________________________________________________________
Join the world�s largest e-mail service with MSN Hotmail.
http://www.hotmail.com


Re: existence of user

From
Stephen Ingram
Date:
On Saturday 02 February 2002 08:37 pm, Annie Bai wrote:
> Hi Steve,
>
> I tried the command as you did
> %psql -c "select * from pg_user"
>
> but I got an error as 'Database ""select * from pg_user"" does not exist in
> the system catalog. Previous connection kept'.
>
> Need I change some configurations?
>

Hi Annie,

I suspect your PGDATABASE environment variable is not set.


You have two choices (at least)

a) set it :)

b) use the -d flag on psql (to stipulate a database), because
by default I guess it is looking at oe of the template databases.

Oh,

c) both :)


Here are some examples

si@guinness > echo $PGDATABASE
bugdb

si@guinness > psql -c "select * from pg_user"
 usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |  passwd  | valuntil
----------+----------+-------------+----------+----------+-----------+----------+----------
 postgres |       26 | t           | t        | t        | t         | ******** |
 si       |       27 | t           | f        | t        | t         | ******** |
 nrg      |       30 | t           | f        | t        | t         | ******** |
 testuser |       28 | t           | f        | t        | t         | ******** |
 sm       |       31 | t           | f        | t        | t         | ******** |
 tc       |       32 | t           | f        | t        | t         | ******** |
 nrgadmin |       33 | t           | f        | t        | t         | ******** |
 dude     |       29 | t           | f        | t        | t         | ******** |
 ken      |       34 | t           | f        | t        | t         | ******** |
(9 rows)


Now if I unset the PGDATABASE...

si@guinness > unset PGDATABASE
si@guinness > echo $PGDATABASE

si@guinness > psql -c "select * from pg_user"
psql: FATAL 1:  Database "testuser" does not exist in the system catalog.

I get what you get.



So I can use the -d flag (for database), with PGDATABASE unset

si@guinness > psql -d bugdb -c "select * from pg_user"
 usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |  passwd  | valuntil
----------+----------+-------------+----------+----------+-----------+----------+----------
 postgres |       26 | t           | t        | t        | t         | ******** |
 si       |       27 | t           | f        | t        | t         | ******** |
 nrg      |       30 | t           | f        | t        | t         | ******** |
 testuser |       28 | t           | f        | t        | t         | ******** |
 sm       |       31 | t           | f        | t        | t         | ******** |
 tc       |       32 | t           | f        | t        | t         | ******** |
 nrgadmin |       33 | t           | f        | t        | t         | ******** |
 dude     |       29 | t           | f        | t        | t         | ******** |
 ken      |       34 | t           | f        | t        | t         | ******** |
(9 rows)


Does that make any sense?

s.




> Thanks,
> Annie
>
>
> From: Stephen Ingram <ingram@samsix.com>
>
> >Reply-To: ingram@samsix.com
> >To: "Annie Bai" <annie_job@hotmail.com>, pgsql-novice@postgresql.org
> >Subject: Re: [NOVICE] existence of user
> >Date: Sat, 2 Feb 2002 19:35:27 -0500
> >
> >On Saturday 02 February 2002 07:18 pm, Annie Bai wrote:
> > > Hi,
> > >
> > > After I used 'create user', Is there a database to keep all the
> >
> >information
> >
> > > about users? How can i check the existence of users?
> > >
> > > Thanks,
> > > Yan
> >
> >Hi,
> >
> >Sure. Check out the pg_user table.
> >
> >Postgres 'system' tables all seem to start with 'pg_'
> >
> >si@guinness > psql -c "select * from pg_user"
> >  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> >passwd  | valuntil
> >----------+----------+-------------+----------+----------+-----------+----
> >------+---------- postgres |       26 | t           | t        | t
> > | t         | ******** |
> >  si       |       27 | t           | f        | t        | t         |
> >******** |
> >  nrg      |       30 | t           | f        | t        | t         |
> >******** |
> >  testuser |       28 | t           | f        | t        | t         |
> >******** |
> >  sm       |       31 | t           | f        | t        | t         |
> >******** |
> >  tc       |       32 | t           | f        | t        | t         |
> >******** |
> >  nrgadmin |       33 | t           | f        | t        | t         |
> >******** |
> >  dude     |       29 | t           | f        | t        | t         |
> >******** |
> >  ken      |       34 | t           | f        | t        | t         |
> >******** |
> >(9 rows)
> >
> >steve
> >
> > > _________________________________________________________________
> > > Get your FREE download of MSN Explorer at
> >
> >http://explorer.msn.com/intl.asp.
> >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > postmaster
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
>
> _________________________________________________________________
> Join the world’s largest e-mail service with MSN Hotmail.
> http://www.hotmail.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: existence of user

From
Stephen Ingram
Date:
On Saturday 02 February 2002 08:30 pm, Annie Bai wrote:
> Hi Steve,
>
> How can I access the system databases? when I use '\l', i can only get
> those databases created by specific users. Is there a particular directory
> holding all the system databases?
>
> Thanks a lot
>

Hi Annie,

Well I will probably have to defer to those with more experience of PG
than me (quite a list!)

I get this...

si@guinness > psql -l
        List of databases
 Database  |  Owner   | Encoding
-----------+----------+-----------
 bugdb     | testuser | SQL_ASCII
 nrgdb     | si       | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(5 rows)


I am guessing when one creates a database, the template information
is copied across and PG then holds separate instances of system tables
in each database.

So, as for system databases, I guess you mean system tables in the databases
you have created?

Do you get the 'template' databases listed above?

I don't know the details but I can only 'connect' to template1,
not template0.

You'll get an explanation for this I am sure; there are some
great people on this list.

Hope I have pointed you in a good direction at least.

s.




>
> From: Stephen Ingram <ingram@samsix.com>
>
> >Reply-To: ingram@samsix.com
> >To: "Annie Bai" <annie_job@hotmail.com>, pgsql-novice@postgresql.org
> >Subject: Re: [NOVICE] existence of user
> >Date: Sat, 2 Feb 2002 19:35:27 -0500
> >
> >On Saturday 02 February 2002 07:18 pm, Annie Bai wrote:
> > > Hi,
> > >
> > > After I used 'create user', Is there a database to keep all the
> >
> >information
> >
> > > about users? How can i check the existence of users?
> > >
> > > Thanks,
> > > Yan
> >
> >Hi,
> >
> >Sure. Check out the pg_user table.
> >
> >Postgres 'system' tables all seem to start with 'pg_'
> >
> >si@guinness > psql -c "select * from pg_user"
> >  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> >passwd  | valuntil
> >----------+----------+-------------+----------+----------+-----------+----
> >------+---------- postgres |       26 | t           | t        | t
> > | t         | ******** |
> >  si       |       27 | t           | f        | t        | t         |
> >******** |
> >  nrg      |       30 | t           | f        | t        | t         |
> >******** |
> >  testuser |       28 | t           | f        | t        | t         |
> >******** |
> >  sm       |       31 | t           | f        | t        | t         |
> >******** |
> >  tc       |       32 | t           | f        | t        | t         |
> >******** |
> >  nrgadmin |       33 | t           | f        | t        | t         |
> >******** |
> >  dude     |       29 | t           | f        | t        | t         |
> >******** |
> >  ken      |       34 | t           | f        | t        | t         |
> >******** |
> >(9 rows)
> >
> >steve
> >
> > > _________________________________________________________________
> > > Get your FREE download of MSN Explorer at
> >
> >http://explorer.msn.com/intl.asp.
> >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 4: Don't 'kill -9' the
> > > postmaster
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
>
> _________________________________________________________________
> Join the world’s largest e-mail service with MSN Hotmail.
> http://www.hotmail.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: existence of user

From
Tom Lane
Date:
Stephen Ingram <ingram@samsix.com> writes:
> On Saturday 02 February 2002 08:30 pm, Annie Bai wrote:
>> How can I access the system databases? when I use '\l', i can only get
>> those databases created by specific users. Is there a particular directory
>> holding all the system databases?

There are no "system databases"; only the ones you see with -l.

There are a few tables (pg_database, pg_shadow, pg_group) that are
actually system-wide; each has a single instance that's visible
in all databases of an installation.  Everything else is local to
a particular database.  For example, each database has its own
copy of pg_class, which is what makes it possible for each database
to have a different set of tables.

> I am guessing when one creates a database, the template information
> is copied across and PG then holds separate instances of system tables
> in each database.

Check.

> I don't know the details but I can only 'connect' to template1,
> not template0.

This is intentional; template0 isn't supposed to be modified, or indeed
used for anything except as a source template for CREATE DATABASE.
There is a better explanation of this in the 7.2 docs than there was in
7.1, see "template databases" at
http://developer.postgresql.org/docs/postgres/managing-databases.html

            regards, tom lane