Help or Bug?

[Cláudia Morgado]

Hello!

Good Morning!

My name´s Carla and I work how System Analyse in company Widesoft Sistemas.

Currently we work with the Oracle and we are studying a possible migration for the Postgre. However we are having problems to relate one different table of schemas.

It follows below the carried through stages:
(The database used is Postgres 7.1.4 for Linux)

1º) We created a database;
CREATE DATABASE DEVEL;

2º) We created two users;

CREATE USER wlog_data WITH PASSWORD 'xxx';
CREATE USER wlog_uni WITH PASSWORD 'yyy';

3º) We created two schemas and reationship with users.

CREATE SCHEMA wlog_data AUTHORIZATION wlog_data;
CREATE SCHEMA wlog_uni AUTHORIZATION wlog_uni;

4º) After, We conceded the permissions.

GRANT SELECT ON wlog_data.cr_messagetype_mul TO wlog_uni;
GRANT REFERENCES ON wlog_data.cr_messagetype_mul TO wlog_uni;

5º) Now We try to create the foreing key :

ALTER TABLE wlog_uni.cr_processload_pri ADD CONSTRAINT
FK_CR_PROCESSLOAD_CD_MESSAGE FOREIGN KEY
(CD_MESSAGE) REFERENCES wlog_data.cr_messagetype_mul
(CD_MESSAGE);

ERROR: wlog_data: permission denied

Somebody could help me?

Thanks,

-----------------------------------------------------------------------------------------
Carla Mello ca*********@widesoft.com.br
Banco de Dados
Fone: (19) 3451-6300 www.widesoft.com.br
-----------------------------------------------------------------------------------------

[Tom Lane]

=?iso-8859-1?Q?Cl=E1udia_Morgado?= <cl*************@widesoft.com.br> writes:

CREATE SCHEMA wlog_data AUTHORIZATION wlog_data;
...
5=BA) Now We try to create the foreing key :
ERROR: wlog_data: permission denied

I think you forgot to grant USAGE on the wlog_data schema to the other
user. This permission bit doesn't have any equivalent in Oracle AFAIK,
but it corresponds to execute permission on a Unix directory, if that
helps you at all...

regards, tom lane

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

http://archives.postgresql.org

[Carla Mello]

Tom Lane,

This problem was solved with the suggested permission.

GRANT USAGE ON SCHEMA wlog_data TO wlog_uni;

There is the possibility of us to consult the permission above in the
database?

Thanks very much,
Carla Mello and Cláudia Morgado.

Thanks very much.
We resolve this problem with the permission
----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Cláudia Morgado" <cl*************@widesoft.com.br>
Cc: <pg***********@postgresql.org>; "Carla Mello"
<ca*********@widesoft.com.br>
Sent: Saturday, October 18, 2003 12:57 AM
Subject: Re: [GENERAL] Help or Bug?

=?iso-8859-1?Q?Cl=E1udia_Morgado?= <cl*************@widesoft.com.br>

writes:

CREATE SCHEMA wlog_data AUTHORIZATION wlog_data;
...
5=BA) Now We try to create the foreing key :
ERROR: wlog_data: permission denied

I think you forgot to grant USAGE on the wlog_data schema to the other
user. This permission bit doesn't have any equivalent in Oracle AFAIK,
but it corresponds to execute permission on a Unix directory, if that
helps you at all...

regards, tom lane

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