[SQL] Hide schemas and tables

[Markus Schaber]

Hello,

Is there any way to hide schemas and relations a user does not have
access privileges for?

I suspect that the client (in this case, unavoidably excel via OLAP and
ODBC) gets this information via querying meta tables, so there is no way
to protect foreign schemas and relations from beeing seen.

Thanks,
Markus
-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Unresolved external: tgetent

[Christoph Haller]

I did 8.0.0rc1 - same behavior 
psql template1
Welcome to psql 8.0.0rc1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit


/usr/lib/dld.sl: Unresolved symbol: tgetent (code)  from
/usr/local/...libreadline.sl.5
Abort(coredump)

Furthermore, the regression test fails badly on 
parallel group (20 tests):  point lseg
running forever. The process list shows 
ps -fu rodos
 UID   PID  PPID  CSTIME TTY   TIME COMMAND
   rodos 14786  6955 228 10:26:43 pts/0 3:08 /bin/sh ./pg_regress
--temp-install --top-builddir=../../..
   rodos  6796 18757  0 10:25:18 pts/0 0:00 gmake check
   rodos 14892 14786  0 10:26:46 pts/0 0:00 
   rodos  6946  6945  0 10:25:22 pts/0 0:00 gmake -C regress check
   rodos 14758 14755  0 10:26:41 pts/0 0:00 postgres: writer process
   rodos 14787  6955  0 10:26:43 pts/0 0:00 tee ./regression.out
   rodos 14755  6955  0 10:26:41 pts/0 0:00
/fd1/rodos/ropub/postgresql-8.0.0rc1/src/test/regress/./tmp_
   rodos  6955  6946  0 10:25:22 pts/0 0:00 /bin/sh ./pg_regress
--temp-install --top-builddir=../../..
   rodos 15064  6498  1 10:30:06 pts/1 0:00 ps -fu rodos
   rodos 14890 14786  1 10:26:46 pts/0 0:00 
   rodos  6498  6496  0 10:20:04 pts/1 0:00 -ksh
   rodos  6945  6796  0 10:25:22 pts/0 0:00 gmake -C src/test check
   rodos 18757 18755  0 09:41:07 pts/0 0:00 -ksh
If this is of any help ... 
If I can do anything else, let me know. 

Regards, Christoph 


Bruce Momjian wrote:
> 
> Would you please test 8.0RC release on the ftp site.  It might already
> be fixed.
> 
> ---
> 
> Christoph Haller wrote:
> > I doubt this is the right list, but it's the only one I'm subscribed to,
> >
> > so please, if one of the core members is reading this, forward it to the
> > right one.
> >
> > I have successfully compiled and installed 7.4.5 yesterday.
> > After doing 'make' I saw the line "Ready to install".
> > template1=# select version();
> >   version
> > 
> >  PostgreSQL 7.4.5 on hppa2.0w-hp-hpux11.00, compiled by GCC gcc (GCC)
> > 3.3.1
> > (1 row)
> >
> > But when I started psql it immediately core dumped with
> > libreadline.sl.5 Unresolved external: tgetent
> >
> > I did a search on the mailing list archives and got 63 hits on
> > 'tgetent',
> > dating from 1997 to 2004, all having the same problem,
> > the missing '-ltermcap' in LIBS in src/Makefile.global.
> >
> > After distclean and doing configure LIBS="-ltermcap" ...
> > and make again it finally worked.
> >
> > In http://archives.postgresql.org/pgsql-bugs/2001-09/msg00023.php
> > Peter Eisentraut writes
> > > LIBS = -lz -lresolv -lcompat -lm -lutil -ltermcap -lreadline
> >
> > That's a good fix.
> >
> > The current CVS tip also works around this problem.
> >
> > But the 7.4.5 src/Makefile.global still has this:
> > LIBS = -lz -lreadline -lPW -lgen -lBSD -ldld -lnsl -lm
> >
> > So it appears this fix must have been lost anywhere.
> >
> > Regards, Christoph
> >

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] postgressql.org

[Christoph Haller]

Has anybody accidentally hit this "postgressql.org"? 
I did. 

I don't know if freeloader is the correct english term 
for the german "Trittbrettfahrer", but that's what it 
looks like. 

Regards, Christoph

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Hide schemas and tables

[John DeSoi]

On Dec 13, 2004, at 5:25 AM, Markus Schaber wrote:
Is there any way to hide schemas and relations a user does not have
access privileges for?
I suspect that the client (in this case, unavoidably excel via OLAP and
ODBC) gets this information via querying meta tables, so there is no 
way
to protect foreign schemas and relations from beeing seen.
Yes, you are correct. I'm not aware of any way to hide this information.
Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] mysql set datatype

[Richard Huxton]

Kenneth Gonsalves wrote:
hi,
how does one replicate the myql 'set' datatype in pg? i tried using 'check', 
but apparently there is much more to this
You could use a bit-string if you are just tracking set membership, but 
that doesn't exactly duplicate the behaviour. It should be 
straightforward enough to write some support functions and just use a 
text type, but that'd use a lot more storage.

Of course, another table is the accepted way of doing this relationally. 
 Are there any particular features you need, or are you just porting an 
application from Mysql?

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[SQL] Similar tables, different indexes performance

[Alvaro Nunes Melo]

Hi,

I know that it's not very polite thing re-send a question, but I don't
have any idea about why can this be happening. I have two almost
identical tables, with equivalent indexes, but their performances are
very different. In this case, I'm sending the queries, explains,
tables'structures and record counts. I think this is the place where I
can most probably get help about performance issues.

Thanks in advance,

-- 
+---+
|  Alvaro Nunes MeloAtua Sistemas de Informacao |
| [EMAIL PROTECTED]www.atua.com.br   |
|UIN - 42722678(54) 327-1044|
+---+
db=> SELECT COUNT(*) FROM titulo WHERE cd_pessoa = 1;
 count 
---
   220
(1 record)

Time: 48,762 ms
db=> SELECT COUNT(*) FROM movimento WHERE cd_pessoa = 1;
 count 
---
   221
(1 record)

Time: 1158,463 ms
db=> EXPLAIN ANALYZE SELECT COUNT(*) FROM movimento WHERE cd_pessoa = 1;
  QUERY PLAN



--
 Aggregate  (cost=279.99..279.99 rows=1 width=0) (actual time=1.263..1.264 
rows=1 loops=1)
   ->  Index Scan using idx_movimento_cd_pessoa on movimento  
(cost=0.00..279.80 rows=74 width=0) (actual time=0.043..0.984 rows=221 loops=1)
 Index Cond: (cd_pessoa = 1)
 Total runtime: 1.388 ms
(4 records)

Time: 6,279 ms
db=> EXPLAIN ANALYZE SELECT COUNT(*) FROM titulo WHERE cd_pessoa = 1;
QUERY 
PLAN
  
 
---
 Aggregate  (cost=6.02..6.02 rows=1 width=0) (actual time=1.105..1.105 rows=1 
loops=1)
   ->  Index Scan using idx_titulo_cd_pessoa_id_tipo_dt_vencimento on titulo  
(cost=0.00..6.01 rows=1 width=0)
  
 (actual time=0.040..0.828 rows=220 loops=1)
 Index Cond: (cd_pessoa = 1)
 Total runtime: 1.209 ms
(4 records)

Time: 6,993 ms
db=> SELECT COUNT(*) FROM movimento;
 count  

 347355
(1 record)

Time: 640,686 ms
db=> SELECT COUNT(*) FROM titulo;   
 count  

 347354
(1 record)

Time: 3274,281 ms
db=> \d movimento
Table "public.movimento"
  Column   | Type  |Modifiers   
 
---+---+-
 cd_movimento  | integer   | not null default 
nextval('public.movimento_cd_movimento_seq'::text)
 cd_pessoa | integer   | not null
 cd_pessoa_usuario | integer   | not null
 cd_pessoa_matriz  | integer   | not null
 cd_pessoa_filial  | integer   | not null
 dt_movimento  | date  | not null
 vl_movimento  | numeric(15,2) | not null
 id_origem | smallint  | not null default 0
 id_tipo   | smallint  | not null
 nr_nota_fiscal| text  | 
Índices:
"pk_movimento"chave primária, btree (cd_movimento)
"idx_movimento_cd_pessoa" btree (cd_pessoa)
"idx_movimento_cd_pessoa_id_tipo" btree (cd_pessoa, id_tipo)
Restrições de checagem:
"ckc_id_tipo_moviment" CHECK (id_tipo = 1 OR id_tipo = 2)
"ckc_id_origem_moviment" CHECK (id_origem = 0 OR id_origem = 1 OR id_origem 
= 2)
Restrições de chave estrangeira:
"fk_movimento_filial" FOREIGN KEY (cd_pessoa_matriz, cd_pessoa_filial) 
REFERENCES filial(cd_pessoa, cd_pessoa_filial) ON UPDATE RESTRICT ON DELETE 
RESTRICT
"fk_movimento_usuario" FOREIGN KEY (cd_pessoa_usuario) REFERENCES 
usuario(cd_pessoa) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_movimento_pessoa" FOREIGN KEY (cd_pessoa) REFERENCES pessoa(cd_pessoa) 
ON UPDATE RESTRICT ON DELETE RESTRICT

db=> \d titulo   
 Table "public.titulo"
 Column | Type  | Modifiers 

+---+---
 cd_titulo  | integer   | not null default 
nextval('public.titulo_cd_titulo_seq'::text)
 cd_portador| integer   | 
 cd_movimento   | integer   | not null
 nr_lote| integer   | 
 cd_lote_titulo | integer   | 
 vl_titulo  | numeric(15,2) | not null
 vl_acrescimo   | numeric(15,2) | 
 vl_desconto| numeric(15,2) | 
 vl_multa   | numeric(15,2) | 
 vl_juro| numeric(15,2) | 
 vl_emolumento  | numeric(15,2) | 
 

Re: [SQL] Unresolved external: tgetent

[Tom Lane]

Christoph Haller <[EMAIL PROTECTED]> writes:
> Furthermore, the regression test fails badly on 
> parallel group (20 tests):  point lseg
> running forever. The process list shows 
> ps -fu rodos
>  UID   PID  PPID  CSTIME TTY   TIME COMMAND
>rodos 14786  6955 228 10:26:43 pts/0 3:08 /bin/sh ./pg_regress
> --temp-install --top-builddir=../../..
>rodos  6796 18757  0 10:25:18 pts/0 0:00 gmake check
>rodos 14892 14786  0 10:26:46 pts/0 0:00 

In other words, the shell is failing to notice that some of its child
processes have exited.  This is a shell bug; we've seen it before on
some platforms (obsolete HPUX versions, in my experience).  You can
probably work around it by reducing the maximum concurrency setting
in the regression script.  See the regression test discussion in the
administrator's guide.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Similar tables, different indexes performance

[Bruno Wolff III]

On Mon, Dec 13, 2004 at 15:17:49 -0200,
  Alvaro Nunes Melo <[EMAIL PROTECTED]> wrote:
> db=> SELECT COUNT(*) FROM titulo WHERE cd_pessoa = 1;
>  count 
> ---
>220
> (1 record)
> 
> Time: 48,762 ms
> db=> SELECT COUNT(*) FROM movimento WHERE cd_pessoa = 1;
>  count 
> ---
>221
> (1 record)
> 
> Time: 1158,463 ms

I suspect you have a lot of dead tuples in those tables.
Have you vacuumed them recently?
Was there enough FSM space when you did so?

You might try doing VACUUM FULL on each table now and see if that
fixes the problem.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] parse error at or near "(" -- Huh???

[D'Arcy J.M. Cain]

On Mon, 13 Dec 2004 09:27:04 +0530
Kenneth Gonsalves <[EMAIL PROTECTED]> wrote:
> and while on this topic, how does one interpret the line numbers one
> gets when running psql -f to create tables?

Forget about line numbers.  Add the -e option and your statements will
show up in your output.  Here is a sample (Unix) command line that I
commonly use to run statements from a file:

psql table -f in.file -e > out.file 2>&1

Now I can search the file for "ERROR" and see exactly what preceded it.

-- 
D'Arcy J.M. Cain <[EMAIL PROTECTED]> |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] mysql set datatype

[Kenneth Gonsalves]

hi,
how does one replicate the myql 'set' datatype in pg? i tried using 'check', 
but apparently there is much more to this

regards
kg

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Hide schemas and tables

[Richard Huxton]

Markus Schaber wrote:
Hello,
Is there any way to hide schemas and relations a user does not have
access privileges for?
I suspect that the client (in this case, unavoidably excel via OLAP and
ODBC) gets this information via querying meta tables, so there is no way
to protect foreign schemas and relations from beeing seen.
Obviously, everyone needs access to the system tables and at present 
there is no system in place to restrict access to viewing all their 
contents.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] mysql set datatype

[Kenneth Gonsalves]

On Monday 13 December 2004 06:55 pm, Richard Huxton wrote:
> Kenneth Gonsalves wrote:
> > hi,
> > how does one replicate the myql 'set' datatype in pg? i tried using
> > 'check', but apparently there is much more to this
>
> You could use a bit-string if you are just tracking set membership, but
> that doesn't exactly duplicate the behaviour. It should be
> straightforward enough to write some support functions and just use a
> text type, but that'd use a lot more storage.
>
> Of course, another table is the accepted way of doing this relationally.
>   Are there any particular features you need, or are you just porting an
> application from Mysql?

just porting

kg

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] postgressql.org

[Christopher Browne]

[EMAIL PROTECTED] (Christoph Haller) writes:
> Has anybody accidentally hit this "postgressql.org"? 
> I did. 
>
> I don't know if freeloader is the correct english term for the
> german "Trittbrettfahrer", but that's what it looks like.

This is pretty common; people "speculatively" grab domain names
involving misspellings of more-or-less prominent domain names in the
hopes that someone will offer them money for the domain.

Probably the best known one where you get _wildly_ unexpected results
is if you were to visit "whitehouse.com" expecting to get information
about the US White House (home of the US president), instead of
"whitehouse.gov", its actual address.

"postgressql.org" seems of pretty dubious value; I wouldn't worry too
much about it.
-- 
"cbbrowne","@","ca.afilias.info"

Christopher Browne
(416) 673-4124 (land)

---(end of broadcast)---
TIP 8: explain analyze is your friend