working with users

[Sam Carleton]

Ok, I am a newbie, but I simply cannot find anything in the
documentation so I hope you all are kind to me...

I have installed Postgres 7.3.4 (will reinstall once I get OpenSLL
compiled into it). I created the postgres user, and the first
database. As the postgress user, I am able to run psql and access
the database that I created. I have been able to run the create
user command.

But how do I see what users exist for the database? Because I was
the user postgres when I created the database, does that mean there
was a postgres user added during creation? If not, what user am I
when I run psql as postgres? If I am postgres, what is the password
to the database and how do I rest it?

Now I am not a DBA, but I have been around enough databases to know
that most of them have their own system tables. Does postgres have
system tables? If so, where do I find a list of them and their
usage?

Sam

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Oliver Elphick]

Post by Sam Carleton
Ok, I am a newbie, but I simply cannot find anything in the
documentation so I hope you all are kind to me...

Read through the Administrator's Manual - client authentication section.

Post by Sam Carleton
I have installed Postgres 7.3.4 (will reinstall once I get OpenSLL
compiled into it). I created the postgres user, and the first
database. As the postgress user, I am able to run psql and access
the database that I created. I have been able to run the create
user command.
But how do I see what users exist for the database?

SELECT * FROM pg_user;

Users exist for the whole database cluster, not just one database.
However you can use client authentication (see below) to limit access to
particular databases.

Post by Sam Carleton
Because I was
the user postgres when I created the database, does that mean there
was a postgres user added during creation?

Yes (unless you specified differently on initdb's command line.)

Post by Sam Carleton
If not, what user am I
when I run psql as postgres? If I am postgres, what is the password
to the database and how do I rest it?

User authentication is controlled by the file $PGDATA/pg_hba.conf

By default, access is allowed without any check or password to anyone
connecting through the Unix socket; users are trusted to tell the truth
about who they are.

You can set a password with ALTER USER but it won't be used until you
change the authentication policy.

Post by Sam Carleton
Now I am not a DBA, but I have been around enough databases to know
that most of them have their own system tables. Does postgres have
system tables? If so, where do I find a list of them and their
usage?

They are in the documentation.

In psql, the command \dS will list them.
--
Oliver Elphick ***@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If ye love me, keep my commandments." John 14:15


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Kris Kiger]

Is it possible to make a schema change to a dumped backup? I've
un-tarred the file and edited toc.dat and restore.sql, but when I
attempt to pg_restore the re-tarred file, I get an archiver out of
memory error.
This whole issue arose when I set a default in a table row equal to a
value that a plpgsql function returns (ie the table default =
functionname(variable)). However, after exploration of the dump files,
it appears that pg_restore tries to restore the tables before it
restores functions...hence I get an error upon restore of the original
dump, because the function does not yet exist. Does anyone know if the
ordering of the restore is in fact the problem? Any input is appreciated!

Kris


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Kris Kiger
This whole issue arose when I set a default in a table row equal to a
value that a plpgsql function returns (ie the table default =
functionname(variable)). However, after exploration of the dump files,
it appears that pg_restore tries to restore the tables before it
restores functions...hence I get an error upon restore of the original
dump, because the function does not yet exist. Does anyone know if the
ordering of the restore is in fact the problem? Any input is appreciated!

pg_restore has facilities for telling it to restore the dump contents in
a particular order (basically, get a table-of-contents listing and then
edit the listing). I don't think it works to hack the tar file
directly.

Also you could try using development tip pg_dump, which should get the
dump order right to begin with. AFAIK it will work okay with a 7.4
server.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html