Horton

Thread

  1. User Management

    Chris Cameron <chris@upnix.com> — 2001-07-04T22:41:51Z

    Coming from a MySQL background, the way PostgreSQL deals with users
seems whacked out to say the least. And that's where I'm having my
problem.

Now in my pg_hba.conf file I have;
local   all     password
local   chris   password        passwd

so when I go 'select * from pg_shadow' I see the root user I added with
'create user'. With this user I can log into any database and create any
database. Which is perfect. However, my problem comes in when I try
delegate access to different databases for different users. Say I want
the user 'joe' to have access to the database 'chris'. The way I tried
to do this is in the file 'passwd' I added;
joe:joepass

which is in the same directory as my pg_hba.conf. Not only does it seem
Postgres isn't reading this file, but how can I have 'joe' own the
database when a database is owned my whoever made it? Joe doesn't have
the ability to create databases because I only want joe to play with his
own database.

Needless to say I'm confused. I've scoured the online manuals, and the
PostgreSQL book is next to useless for indepth administration.

Any help, pointers, or links would be much appreciated,

Chris

  2. Re: User Management

    D. Duccini <duccini@backpack.com> — 2001-07-04T22:47:41Z

    use the "createuser" command


On Wed, 4 Jul 2001, Chris Cameron wrote:

> Coming from a MySQL background, the way PostgreSQL deals with users
> seems whacked out to say the least. And that's where I'm having my
> problem.
> 
> Now in my pg_hba.conf file I have;
> local   all     password
> local   chris   password        passwd
> 
> so when I go 'select * from pg_shadow' I see the root user I added with
> 'create user'. With this user I can log into any database and create any
> database. Which is perfect. However, my problem comes in when I try
> delegate access to different databases for different users. Say I want
> the user 'joe' to have access to the database 'chris'. The way I tried
> to do this is in the file 'passwd' I added;
> joe:joepass
> 
> which is in the same directory as my pg_hba.conf. Not only does it seem
> Postgres isn't reading this file, but how can I have 'joe' own the
> database when a database is owned my whoever made it? Joe doesn't have
> the ability to create databases because I only want joe to play with his
> own database.
> 
> Needless to say I'm confused. I've scoured the online manuals, and the
> PostgreSQL book is next to useless for indepth administration.
> 
> Any help, pointers, or links would be much appreciated,
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  3. RE: User Management

    D. Duccini <duccini@backpack.com> — 2001-07-04T23:01:18Z

    postgres=# \h grant
Command:     GRANT
Description: Grants access privilege to a user, a group or all users
Syntax:
GRANT privilege [, ...] ON object [, ...]
    TO { PUBLIC | GROUP group | username }



> That doesn't solve my problem of whoever creates the database owns the
> database.
> 
> I'd like to have user 'joe' have access /only/ to the database 'chris'.
> As far as I can tell the only way to do this is have 'joe' create the
> database.
> 
> I'm looking for the equivalent to 'GRANT ALL ON chris to joe@localhost
> identified by 'joepass';' in MySQL.
> 
> Chris
> 
> -----Original Message-----
> From: D. Duccini [mailto:duccini@backpack.com]
> Sent: Wednesday, July 04, 2001 4:48 PM
> To: Chris Cameron
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] User Management
> 
> 
> 
> use the "createuser" command
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  4. RE: User Management

    Chris Cameron <chris@upnix.com> — 2001-07-04T23:02:10Z

    That doesn't solve my problem of whoever creates the database owns the
database.

I'd like to have user 'joe' have access /only/ to the database 'chris'.
As far as I can tell the only way to do this is have 'joe' create the
database.

I'm looking for the equivalent to 'GRANT ALL ON chris to joe@localhost
identified by 'joepass';' in MySQL.

Chris

-----Original Message-----
From: D. Duccini [mailto:duccini@backpack.com]
Sent: Wednesday, July 04, 2001 4:48 PM
To: Chris Cameron
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] User Management



use the "createuser" command

  5. RE: User Management

    Chris Cameron <chris@upnix.com> — 2001-07-04T23:35:13Z

    This has been working well so far, thanks.

Um, looking at http://www.postgresql.org/idocs/index.php?privileges.html
it seems to be that I'm not actually able to do what I want. Am I
correct in believing that to let a user create tables and change tables
in a database, they actually have to had created it? The best I can do
is grant privileges on premade tables?

Thanks,
Chris

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of D. Duccini
Sent: Wednesday, July 04, 2001 5:01 PM
To: Chris Cameron
Cc: pgsql-novice@postgresql.org
Subject: RE: [NOVICE] User Management



postgres=# \h grant
Command:     GRANT
Description: Grants access privilege to a user, a group or all users
Syntax:
GRANT privilege [, ...] ON object [, ...]
    TO { PUBLIC | GROUP group | username }



> That doesn't solve my problem of whoever creates the database owns the
> database.
>
> I'd like to have user 'joe' have access /only/ to the database
'chris'.
> As far as I can tell the only way to do this is have 'joe' create the
> database.
>
> I'm looking for the equivalent to 'GRANT ALL ON chris to joe@localhost
> identified by 'joepass';' in MySQL.
>
> Chris
>
> -----Original Message-----
> From: D. Duccini [mailto:duccini@backpack.com]
> Sent: Wednesday, July 04, 2001 4:48 PM
> To: Chris Cameron
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] User Management
>
>
>
> use the "createuser" command
>


------------------------------------------------------------------------
-----
david@backpack.com            BackPack Software, Inc.
www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
------------------------------------------------------------------------
-----


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

http://www.postgresql.org/users-lounge/docs/faq.html

  6. Re: User Management

    Giorgio A. <jh@libero.it> — 2001-07-05T09:09:29Z

    hi, i have this very lame problem: i created a column as varchar(15) but now
i need to have more than 15 char... how can i change this ? changing it into
"text" will also be good !

tnx
Giorgio A.

  7. Re: User Management

    Andrew McMillan <andrew@catalyst.net.nz> — 2001-07-05T09:13:14Z

    Chris Cameron wrote:
> 
> This has been working well so far, thanks.
> 
> Um, looking at http://www.postgresql.org/idocs/index.php?privileges.html
> it seems to be that I'm not actually able to do what I want. Am I
> correct in believing that to let a user create tables and change tables
> in a database, they actually have to had created it? The best I can do
> is grant privileges on premade tables?

It's not as straightforward, but you should also be able to grant suitable
privileges on the metadata tables.  Use \dS to list the metadata tables.

Cheers,
					Andrew.
-- 
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709

  8. Re: User Management

    Nabil Sayegh <nsmail@sayegh.de> — 2001-07-05T11:22:28Z

    On 05 Jul 2001 11:09:29 +0200, Giorgio A. wrote:
> hi, i have this very lame problem: i created a column as varchar(15) but now
> i need to have more than 15 char... how can i change this ? changing it into
> "text" will also be good !

You can't change the type of fields.
You could create a new table with proper cols and 'INSERT INTO newtable
SELECT * FROM oldtable;DROP TABLE oldtable;ALTER TABLE newtable RENAME
TO oldtable;' or something like that.

This leaves you in a mess if there are triggers involved (references
etc.) 

I would suggest you 'pg_dump -D dbname > db.sql' modify the table
definitions, 'dropdb dbname;createdb dbname;psql dbname -f db.sql'
(If you can live with a short downtime and loosing some information like
oids, but that should be irrelevant in most cases) 

cu

--
 Nabil Sayegh

  9. Re: User Management

    Nabil Sayegh <nsmail@sayegh.de> — 2001-07-05T11:22:42Z

    On 05 Jul 2001 11:09:29 +0200, Giorgio A. wrote:
> hi, i have this very lame problem: i created a column as varchar(15) but now
> i need to have more than 15 char... how can i change this ? changing it into
> "text" will also be good !

You can't change the type of fields.
You could create a new table with proper cols and 'INSERT INTO newtable
SELECT * FROM oldtable;DROP TABLE oldtable;ALTER TABLE newtable RENAME
TO oldtable;' or something like that.

This leaves you in a mess if there are triggers involved (references
etc.) 

I would suggest you 'pg_dump -D dbname > db.sql' modify the table
definitions, 'dropdb dbname;createdb dbname;psql dbname -f db.sql'
(If you can live with a short downtime and loosing some information like
oids, but that should be irrelevant in most cases) 

cu

--
 Nabil Sayegh

  10. Re: User Management

    Nabil Sayegh <nsmail@sayegh.de> — 2001-07-05T11:22:53Z

    On 05 Jul 2001 11:09:29 +0200, Giorgio A. wrote:
> hi, i have this very lame problem: i created a column as varchar(15) but now
> i need to have more than 15 char... how can i change this ? changing it into
> "text" will also be good !

You can't change the type of fields.
You could create a new table with proper cols and 'INSERT INTO newtable
SELECT * FROM oldtable;DROP TABLE oldtable;ALTER TABLE newtable RENAME
TO oldtable;' or something like that.

This leaves you in a mess if there are triggers involved (references
etc.) 

I would suggest you 'pg_dump -D dbname > db.sql' modify the table
definitions, 'dropdb dbname;createdb dbname;psql dbname -f db.sql'
(If you can live with a short downtime and loosing some information like
oids, but that should be irrelevant in most cases) 

cu

--
 Nabil Sayegh