Thread: default console user authentication?

George Clark <grclark78@gmail.com> wrote:

> I'm trying to do a pg_dump to backup a database that is part of
> our phone system.  The original installers (phone people) seem to
> know nothing about the superuser account, or the db admin account,
> and the pg_dump needs a password authentication.
>
> The pg_dump command is asking for the password for user
> "Administrator."  I'm logged into the server (a Windows Server) as
> the Admin user, but it is NOT THAT password it wants, because that
> one fails.  I am using the command:
>
> pg_dump -p 6432 eware -f D:\Util\pgdump.log
>
> If they did not configure a default password at install, how can I
> set up a superuser acct and password so I can do some admin work
> with this pg database?  Right now I have no console level access
> to this thing.

You might want to temporarily modify pg_hba.conf or pgpass to get
through this, and then review how you want to handle security long
term (which *might* involve making your emergency fix permanent).

http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html

http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html

-Kevin

Admins,

What is the best way to compare two rows from within psql cli client?

It has ~30 fields, and the two rows are duplicate data but there might
be differences.

id field1  field2  field3 ...
===========================
id1 value1 value2 value3  ...
id2 value1 value2 value3  ...

I could write a generic script to iterate through the list of fields and
compare each field values, or concatenate the fields to a string, but wondered what is out there.

Also, is there a way around to use select row(...) = row(...)?


Thanks.
--
Ben Kim

Ben Kim <bkim@tamu.edu> writes:
> What is the best way to compare two rows from within psql cli client?

In 8.4 and up you can just compare the row values, eg
    select x.* = y.*
if x and y are known to be of the same rowtype.

            regards, tom lane

On Tue, Oct 12, 2010 at 1:17 PM, Ben Kim <bkim@tamu.edu> wrote:
> Admins,
>
> What is the best way to compare two rows from within psql cli client?
>
> It has ~30 fields, and the two rows are duplicate data but there might
> be differences.
>
> id field1  field2  field3 ...
> ===========================
> id1 value1 value2 value3  ...
> id2 value1 value2 value3  ...
>
> I could write a generic script to iterate through the list of fields and
> compare each field values, or concatenate the fields to a string, but wondered what is out there.
>
> Also, is there a way around to use select row(...) = row(...)?

Does: (T1.value1,T1.value2, T1.value3, ... ) IS NOT DISTINCT FROM
(T2.value1,T2.value2, T2.value3,...)
or: Row( T1.* ) IS NOT DISTINCT FROM ( T2.* )
work for you?





--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Thanks.

I'm on 8.4.2.

I was not clear but the two rows are from the same table. Here's the test case.

create table test_dup (id serial primary key, val text);
insert into test_dup(val)values('some text');
insert into test_dup(val)values('some texta');

select * from test_dup;
 id |    val
----+------------
  1 | some text
  2 | some texta



How can I check whether the two rows are the same or different?


Thanks.

On Tue, Oct 12, 2010 at 02:12:36PM -0700, Richard Broersma wrote:
> On Tue, Oct 12, 2010 at 1:17 PM, Ben Kim <bkim@tamu.edu> wrote:
> > Admins,
> >
> > What is the best way to compare two rows from within psql cli client?
> >
> > It has ~30 fields, and the two rows are duplicate data but there might
> > be differences.
> >
> > id field1  field2  field3 ...
> > ===========================
> > id1 value1 value2 value3  ...
> > id2 value1 value2 value3  ...
> >
> > I could write a generic script to iterate through the list of fields and
> > compare each field values, or concatenate the fields to a string, but wondered what is out there.
> >
> > Also, is there a way around to use select row(...) = row(...)?
>
> Does: (T1.value1,T1.value2, T1.value3, ... ) IS NOT DISTINCT FROM
> (T2.value1,T2.value2, T2.value3,...)
> or: Row( T1.* ) IS NOT DISTINCT FROM ( T2.* )
> work for you?
>
>
>
>
>
> --
> Regards,
> Richard Broersma Jr.
>
> Visit the Los Angeles PostgreSQL Users Group (LAPUG)
> http://pugs.postgresql.org/lapug

On Tue, Oct 12, 2010 at 3:10 PM, Ben Kim <bkim@tamu.edu> wrote:

> create table test_dup (id serial primary key, val text);
> insert into test_dup(val)values('some text');
> insert into test_dup(val)values('some texta');
>
> select * from test_dup;
>  id |    val
> ----+------------
>  1 | some text
>  2 | some texta


  SELECT Array_agg( id ) AS duplicate_ids,  val
    FROM test_dup
GROUP BY val
HAVING COUNT(*) > 1;

   SELECT T1.id, T2.id, T.val
     FROM test_dup AS T1
INNER JOIN test_dup AS T2
     USING ( val );

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Ben Kim <bkim@tamu.edu> wrote:

> create table test_dup (id serial primary key, val text);

> How can I check whether the two rows are the same or different?

Well, with a primary key in there, they had *better* be different.

It would seem you want to see if some *subset* of the columns in two
rows match?  All columns except those in the primary key?

The next question is whether you want to just compare two specific
rows or list all duplicates.

-Kevin

> > create table test_dup (id serial primary key, val text);
>
> > How can I check whether the two rows are the same or different?
>
> Well, with a primary key in there, they had *better* be different.
>
> It would seem you want to see if some *subset* of the columns in two
> rows match?  All columns except those in the primary key?
>
> The next question is whether you want to just compare two specific
> rows or list all duplicates.

I was wanting something like

select row(select ... from test_dup where id=1) = row(select ... from
  test_dup where id=2)

where ... is all fields except the primary key field. (Which can be
more than 100 fields in some tables.)

I guess I've roughly found what I need between the answers posted.

select row(t1.*) = row(t2.*) from
(select val1, val2, val3, ..., val100 from test_dup where id=1) t1,
(select val1, val2, val3, ..., val100 from test_dup where id=2) t2
;


This gave me a blank row when all of val1 ... val100 matched, and a
value of "f" when something did not match.

(Wish there was a shorthand way to express "all fields (*)  except id field". Is it possible?)


Thanks.

Ben Kim