Thread: which database to login to to create global users?

hello,

i've been a filemaker pro user for quite awhile, and have decided to take the plunge with postgresql.

i just installed the latest release version, v8.1.1, and am slowly making my way through documentation and tutorials.

i can launch postgres successfully, creating databases & users, reading & writing data, etc.

it seems clear to me that if i want to create a user with access rights for a particular database, then i do so by logging in to that particular database, and creating ROLES, etc. that makes sense.

i'm confused about one issue -- what database should i login to if i wish to create 'global' users? is it "template1"? "template0" seems to be generaly inaccessible.

any advice or pointers to the right documentation would be very helpful!

thank you.

pres

On 1/3/06, pres <pgn.lists@gmail.com> wrote:
> hello,
>
> i've been a filemaker pro user for quite awhile, and have decided to take
> the plunge with postgresql.
>
> i just installed the latest release version, v8.1.1, and am slowly making my
> way through documentation and tutorials.
>
> i can launch postgres successfully, creating databases & users, reading &
> writing data, etc.
>
> it seems clear to me that if i want to create a user with access rights for
> a particular database, then i do so by logging in to that particular
> database, and creating ROLES, etc. that makes sense.
>
> i'm confused about one issue -- what database should i login to if i wish to
> create 'global' users? is it "template1"? "template0" seems to be generaly
> inaccessible.
>
> any advice or pointers to the right documentation would be very helpful!
>
> thank you.
>
> pres
>

roles are always global... i don't think you can create roles that
exist just in one database...

you assign permissions to roles via the GRANT statement...

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

hello jaime,

pres <pgn.lists@gmail.com> writes:
> so, if i understand you correctly, it does not matter which database i'm
> logged into when i create a role?

Correct.

> where (what database/table), then, are theses assignments stored?

The info related to roles is stored in some "shared" catalogs that are
visible in every database --- just the same way that pg_database is
visible in every database.  Try this to see which catalogs are shared:

    select relname from pg_class where relisshared and relkind = 'r';

See also the documentation on the system catalogs:
http://www.postgresql.org/docs/8.1/static/catalogs.html
The description of each catalog takes care to mention it if the catalog
is shared across databases.

            regards, tom lane

hello tom,



thank you for the clarification & reference.  exactly what i needed.

i must admit that i'd managed to get a completely different, and incorrect, understanding from the documentation ... i think i was carrying over the (my?) "old way" of doing things.

i think i'd best reread the roles section after reading your reference above.

thank you again.

pres

hello tom,



i'm not yet sure if/where we can make enduser requests, but in reading the documentation's "System Catalogs" section you'd pointed me to, i, of course, find:

"... A few catalogs are physically shared across all databases in a cluster; these are noted in the descriptions of the individual catalogs.

Table 42-1. System Catalogs..."

i would find it helpful if Table 42-1 included a column that identified whehter each catalog is shared or not.  convenient, but given that the documentation does contain it, not necessary.

simply, it would be nice to see it as an overview.

thank you again.

pres