Обсуждение: Hiding table structure information

Hello,

Is there a way to hide the table definition structure from an user, even if
the user has privileges to select data from that table?

Reimer

I think you have to use a view! You create a view on this table specifying
only the columns the user may have access, and then you give privileges on
this view instead of giving privileges on the table!



-----Mensagem original-----
De: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] Em nome de Carlos H. Reimer
Enviada em: segunda-feira, 26 de junho de 2006 14:40
Para: pgsql-admin@postgresql.org
Assunto: [ADMIN] Hiding table structure information

Hello,

Is there a way to hide the table definition structure from an user, even if
the user has privileges to select data from that table?

Reimer


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

Yes, a but even so, if the client uses a tool like pgAdmin, he can see all
the database structure. It?s because this kind of tool uses data from the
pg_catalog schema.

Maybe the question should be reformulted to: is there a way to hide
pg_catalog schema? I think it can?t not be done because all metadata are
there, but maybe somebody can see a solution.

Thanks in advance,

Reimer

> -----Mensagem original-----
> De: Rodrigo Sakai [mailto:rodrigo.sakai@zanthus.com.br]
> Enviada em: segunda-feira, 26 de junho de 2006 17:03
> Para: carlosreimer@terra.com.br; pgsql-admin@postgresql.org
> Assunto: RES: [ADMIN] Hiding table structure information
>
>
> I think you have to use a view! You create a view on this table specifying
> only the columns the user may have access, and then you give privileges on
> this view instead of giving privileges on the table!
>
>
>
> -----Mensagem original-----
> De: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] Em nome de Carlos H. Reimer
> Enviada em: segunda-feira, 26 de junho de 2006 14:40
> Para: pgsql-admin@postgresql.org
> Assunto: [ADMIN] Hiding table structure information
>
> Hello,
>
> Is there a way to hide the table definition structure from an
> user, even if
> the user has privileges to select data from that table?
>
> Reimer
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>

"Carlos H. Reimer" <carlosreimer@terra.com.br> writes:
> Maybe the question should be reformulted to: is there a way to hide
> pg_catalog schema?

There is not.  My advice would be to put clients in separate databases
if you need that much isolation between them.

            regards, tom lane

On 6/26/06, Carlos H. Reimer <carlosreimer@terra.com.br> wrote:
> Maybe the question should be reformulted to: is there a way to hide
> pg_catalog schema? I think it can?t not be done because all metadata are
> there, but maybe somebody can see a solution.

You could use a set returning function and a view to hide the original
table's details.  As for hiding pg_catalog... I believe you could
create a rule on it.

Never tried to hide something this extensively... and don't really
understand a practical reason for doing so.  Remember, security
through obscurity is flawed from the get go.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 2nd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

Would there be a way to set up the 2 databases - one with the tables and data and the other that connects to the first with views into the second database?

Actually I am interested because I will soon need to set up a way to feed data to multiple databases and would love to have a stored procedure or trigger that, when the data is updated in one database, the changes are propagated to all the other databases.

Thanks,
Aaron

On Mon, Jun 26, 2006 at 19:48:32 -0500,
  Aaron Bono <postgresql@aranya.com> wrote:
> Would there be a way to set up the 2 databases - one with the tables and
> data and the other that connects to the first with views into the second
> database?
>
> Actually I am interested because I will soon need to set up a way to feed
> data to multiple databases and would love to have a stored procedure or
> trigger that, when the data is updated in one database, the changes are
> propagated to all the other databases.

There is a contrib package dblink that will do at least some of what you
want.

You might also see if just separating stuff into different schemas will
be good enough for you. That would allow you to have separate name spaces
but still be able to reference tables in other schemas efficiently.

Actually most of the updates will be in the same database but different schemas as you suggest.  Unfortunately, due to client requirements, not everything will be structured that way.  I may have some other options though...

On Jun 26, 2006, at 7:48 PM, Aaron Bono wrote:
> Would there be a way to set up the 2 databases - one with the
> tables and data and the other that connects to the first with views
> into the second database?

google: postgresql dblink

> Actually I am interested because I will soon need to set up a way
> to feed data to multiple databases and would love to have a stored
> procedure or trigger that, when the data is updated in one
> database, the changes are propagated to all the other databases.

http://gborg.org/slony1
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461