Skip site navigation (1) Skip section navigation (2)

Re: Problem migrating from 8.0.3 to 8.2.3

From: "Anton Pikhteryev" <apikhteryev(at)sandvine(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Problem migrating from 8.0.3 to 8.2.3
Date: 2007-03-19 14:24:20
Message-ID: F489AB573A749146B33461ECE080913A0108BEFE@EXCHANGE-1.sandvine.com (view raw or flat)
Thread:
Lists: pgsql-admin
BTW creating domain didn't throw any error on the destination server
(8.2.3), but I found that after migration permissions are not
consistent:

...
COMMENT
CREATE DOMAIN valid_user AS name DEFAULT "session_user"()
        CONSTRAINT user_exists CHECK (has_table_privilege(VALUE,
'pg_user'::text, 'SELECT'::text));
CREATE DOMAIN
ALTER DOMAIN public.valid_user OWNER TO labadmin;
ALTER DOMAIN
COMMENT ON DOMAIN valid_user IS 'valid PostgreSQL user name';
COMMENT
...

New server (8.2.3)

=> select has_table_privilege('mfgtest','clap_file_info','SELECT');
 has_table_privilege
---------------------
 f
(1 row)

Old one (8.0.3): 

# select has_table_privilege('mfgtest','clap_file_info','SELECT');
 has_table_privilege
---------------------
 t
(1 row)



Anton Pikhteryev
 

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org 
> [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Anton 
> Pikhteryev
> Sent: Monday, March 19, 2007 9:52 AM
> To: Tom Lane
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Problem migrating from 8.0.3 to 8.2.3 
> 
> On the destination box:
> 
> #\dD+ valid_user
>                                                        List of domains
>  Schema |    Name    | Type |         Modifier         |
> Check
> --------+------------+------+--------------------------+------
> ----------
> -----------------------------------------------------
>  public | valid_user | name | default "session_user"() | 
> CHECK (has_table_privilege(VALUE, 'pg_user'::text, 'SELECT'::text))
> (1 row)
> 
> On the source:
> 
> # \dD+ valid_user
>                     List of domains
>  Schema |    Name    | Type |         Modifier
> --------+------------+------+--------------------------
>  public | valid_user | name | default "session_user"()
> (1 row)
> 
> 
> I created valid_user domain on the old system (8.0.3) a long time ago
> as:
> 
> CREATE DOMAIN valid_user
>     AS name     -- pg_catalog.pg_user.usename%TYPE
>     DEFAULT SESSION_USER
>     CONSTRAINT user_exists
>         CHECK (has_table_privilege(VALUE,'pg_user','SELECT'));
> COMMENT ON DOMAIN valid_user IS 'valid PostgreSQL user name';
> 
> I was also thinking that there is no need to recreate the 
> domain again as a part of migration and hoping that 
> pg_dumpall will take care of it.
> 
> 
> 
> Anton Pikhteryev
>  
>  
> 
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> > Sent: Friday, March 16, 2007 3:34 PM
> > To: Anton Pikhteryev
> > Cc: pgsql-admin(at)postgresql(dot)org
> > Subject: Re: [ADMIN] Problem migrating from 8.0.3 to 8.2.3
> > 
> > "Anton Pikhteryev" <apikhteryev(at)sandvine(dot)com> writes:
> > > There is that part and doesn't have any errors:
> > > CREATE TABLE clap_file_info (
> > >     fileid integer DEFAULT
> > > nextval('public.clap_file_info_fileid_seq'::text) NOT NULL,
> > >     filename text NOT NULL,
> > >     created timestamp with time zone DEFAULT
> > > ('now'::text)::timestamp(6) with time zone NOT NULL,
> > >     "owner" valid_user NOT NULL
> > > );
> > 
> > What is a valid_user?  (No, I don't want \dT output, I want the 
> > definition of the type.)
> > 
> > 			regards, tom lane
> > 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2007-03-19 16:38:16
Subject: Re: Problem migrating from 8.0.3 to 8.2.3
Previous:From: Tom LaneDate: 2007-03-19 14:16:42
Subject: Re: [SQL] create view with check option

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group