Thread: existence of user
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.
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
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
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
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 worlds 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
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 worlds 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
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