Thread: working with users

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

On Wed, 2003-10-15 at 02:10, Sam Carleton wrote:
> 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.

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

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

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

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

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

Kris Kiger <kris@musicrebellion.com> writes:
> 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