Thread: Re-nameing a database?

Re-nameing a database?

From
Chris Ruprecht
Date:
Hi all,

is there an easy way to re-name a database?

Best regards,
Chris

--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


user to database restriction?

From
Jon August
Date:
Can I restrict a user to a single database?  It seems like any user can
connect to any database. . .

    -Jon

---
Voice: (973) 560-9095  | Internection - Electronic Commerce, Internet
Cell : (973) 801-6071  | Consulting and Grade A Web Hosting Since 1995.


Re: Re-nameing a database?

From
Tom Lane
Date:
Chris Ruprecht <chrup999@yahoo.com> writes:
> is there an easy way to re-name a database?

Officially, no.  Unofficially, since 7.1 it should work to UPDATE the
appropriate row in pg_database.  I think.  Better try it on a test
database...

            regards, tom lane

Re: user to database restriction?

From
Chris Ruprecht
Date:
Jon,

the only way I can think of doing this is by revoking the user's
rights to the tables of the databases they have no business in.

I know this is not exactly the answer you're looking for ...

Best regards,
Chris

At 17:54 -0500 02/10/2002, Jon August wrote:
>Can I restrict a user to a single database?  It seems like any user can
>connect to any database. . .
>
>    -Jon
>
>---
>Voice: (973) 560-9095  | Internection - Electronic Commerce, Internet
>Cell : (973) 801-6071  | Consulting and Grade A Web Hosting Since 1995.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html


--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Re: Re-nameing a database?

From
Chris Ruprecht
Date:
Thanks, Tom, I will play around with it a bit.

Best regards,
Chris

At 19:07 -0500 02/10/2002, Tom Lane wrote:
>Chris Ruprecht <chrup999@yahoo.com> writes:
>>  is there an easy way to re-name a database?
>
>Officially, no.  Unofficially, since 7.1 it should work to UPDATE the
>appropriate row in pg_database.  I think.  Better try it on a test
>database...
>
>            regards, tom lane


--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Re: user to database restriction?

From
Jon August
Date:
That seems like a lot of work. . . and could become easily outdated.  No?

How about in pg_hba.conf?  There's no way to say only user xyz can connect
to database abc?

I tried something like this in my pg_hba.conf, but now nobody can connect
to it.

local     all                                       password    postgres
host      all     xxx.xxx.xxx.xxx  255.255.255.255  password    postgres
local     shopdb                                    password        mark
host      shopdb  xxx.xxx.xxx.xxx  255.255.255.255  password        mark

I was hoping this config would permit user "postgres" to connect to any
database, but limit user "mark" to shopdb.

Maybe I'm missing something, but isn't this a serious security problem for
postgres installations used by many people - like in an ISP environment?

    -Jon



On Sun, 10 Feb 2002, Chris Ruprecht wrote:

> Jon,
>
> the only way I can think of doing this is by revoking the user's
> rights to the tables of the databases they have no business in.
>
> I know this is not exactly the answer you're looking for ...
>
> Best regards,
> Chris
>
> At 17:54 -0500 02/10/2002, Jon August wrote:
> >Can I restrict a user to a single database?  It seems like any user can
> >connect to any database. . .
> >
> >    -Jon
> >



Re: user to database restriction?

From
Stephan Szabo
Date:
On Sun, 10 Feb 2002, Jon August wrote:

>
> That seems like a lot of work. . . and could become easily outdated.  No?
>
> How about in pg_hba.conf?  There's no way to say only user xyz can connect
> to database abc?
>
> I tried something like this in my pg_hba.conf, but now nobody can connect
> to it.
>
> local     all                                       password    postgres
> host      all     xxx.xxx.xxx.xxx  255.255.255.255  password    postgres
> local     shopdb                                    password        mark
> host      shopdb  xxx.xxx.xxx.xxx  255.255.255.255  password        mark
>
> I was hoping this config would permit user "postgres" to connect to any
> database, but limit user "mark" to shopdb.

The final argument goes to an external file in $PGDATA that lists users
(and optionally passwords) for the users that are allowed into the db.



Re: Re-nameing a database?

From
Naomi Walker
Date:
Not in postgres, unfortunately.  I'd love to be wrong.

At 04:26 PM 2/10/02 -0600, Chris Ruprecht wrote:

>Hi all,
>
>is there an easy way to re-name a database?
>
>Best regards,
>Chris
>
>--
>Chris Ruprecht
>Network grunt and bit pusher extraordinaíre
>
>_________________________________________________________
>Do You Yahoo!?
>Get your free @yahoo.com address at http://mail.yahoo.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org

--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100  ext 242


Re: Re-nameing a database?

From
Oliver Elphick
Date:
On Mon, 2002-02-11 at 15:50, Naomi Walker wrote:
> Not in postgres, unfortunately.  I'd love to be wrong.
>
> At 04:26 PM 2/10/02 -0600, Chris Ruprecht wrote:
>
> >Hi all,
> >
> >is there an easy way to re-name a database?

In 7.1.3, you can do it by updating the row in pg_database and then
restarting the postmaster:

$ psql template1
...

template1=# \l
        List of databases
 Database  |  Owner   | Encoding
-----------+----------+-----------
 junk      | olly     | SQL_ASCII
...
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(8 rows)

template1=# update pg_database set datname = 'rubbish' where datname =
'junk';
UPDATE 1
template1=# \l
        List of databases
 Database  |  Owner   | Encoding
-----------+----------+-----------
 rubbish   | olly     | SQL_ASCII
...
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(8 rows)

template1=# \c rubbish
FATAL 1:  Database "rubbish" does not exist in the system catalog.
Previous connection kept
template1=# \c junk
FATAL 1:  Database "junk", OID 44062648, has disappeared from
pg_database
Previous connection kept
template1=# \q
$ sudo /etc/init.d/postgresql restart
Password:
Restarting PostgreSQL database: postmaster
Stopped /usr/lib/postgresql/bin/postmaster (pid 15397).
Starting PostgreSQL postmaster.
postmaster successfully started

$ psql template1
...
template1=# \c rubbish
You are now connected to database rubbish.
rubbish=# \c junk
FATAL 1:  Database "junk" does not exist in the system catalog.
Previous connection kept
rubbish=#


An alternative method would be to dump everything with pg_dumpall and
then edit the dump to change the database name.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C



Re: Re-nameing a database?

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
>> is there an easy way to re-name a database?

> In 7.1.3, you can do it by updating the row in pg_database and then
> restarting the postmaster:

I don't believe a postmaster restart is required.  It might be a good
idea to vacuum pg_database and then force a checkpoint, however, just
to make sure the updated row is out on disk and not still hanging about
in a shared-memory buffer.

            regards, tom lane

Re: user to database restriction?

From
Naomi Walker
Date:
What about through pg_hba.conf?  We've set it up so to connect to a
database, you must be in pg_pwd (through pg_passwd), and know the
passwd.  I will mention, that pg_pwd should not be clear text.

Any plan to encrypt it?

At 06:12 PM 2/10/02 -0600, Chris Ruprecht wrote:

>Jon,
>
>the only way I can think of doing this is by revoking the user's rights to
>the tables of the databases they have no business in.
>
>I know this is not exactly the answer you're looking for ...
>
>Best regards,
>Chris
>
>At 17:54 -0500 02/10/2002, Jon August wrote:
>>Can I restrict a user to a single database?  It seems like any user can
>>connect to any database. . .
>>
>>         -Jon
>>
>>---
>>Voice: (973) 560-9095  | Internection - Electronic Commerce, Internet
>>Cell : (973) 801-6071  | Consulting and Grade A Web Hosting Since 1995.
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>--
>Chris Ruprecht
>Network grunt and bit pusher extraordinaíre
>
>_________________________________________________________
>Do You Yahoo!?
>Get your free @yahoo.com address at http://mail.yahoo.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100  ext 242