SEO

#1 (**permalink**) 04-19-2008, 10:43 AM

So your suggestion is first to come up with a query that dynamically checks
permissions and create a view for it. Secondly, change pgAdmin to reference
this view in place of pg_proc. Actually, it should be extended to all
objects in the database, not just pg_proc. If you don't have this
dictionary role, you shouldn't be able to look at any of the objects in the
database unless you own the object or have been granted rights to the
object.

I don't know the information_schema that well so I don't know if this is
something that should sit on top of PostgreSQL with views and then make
subsequent changes to pgAdmin or if the database itself needs to change to
handle this.

Jon

> -----Original Message-----
> From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
> Sent: Wednesday, December 19, 2007 9:56 AM
> To: Roberts, Jon
> Cc: 'Trevor Talbot'; Joshua D. Drake; Kris Jurka; Merlin Moncure; Jonah H.
> Harris; Bill Moran; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] viewing source code
>
> Roberts, Jon escribió:
>
> > The more I thought about a counter proposal to put views on pg_proc, I
> > realized that isn't feasible either. It would break functionality of
> > pgAdmin because users couldn't view their source code with the tool.
>
> What's wrong with patching pgAdmin?
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

#2 (**permalink**) 04-19-2008, 10:43 AM

On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
> So your suggestion is first to come up with a query that dynamically checks
> permissions and create a view for it. Secondly, change pgAdmin to reference
> this view in place of pg_proc. Actually, it should be extended to all

This solution will not work. It requires cooperation from pgAdmin
which is not going to happen and does nothing about psql or direct
queries from within pgadmin. Considered from a security/obfuscation
perspective, its completely ineffective. As I've said many times,
there are only two solutions to this problem:

1. disable permissions to pg_proc and deal with the side effects
(mainly, pgadmin being broken).

2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that
the procedure code is encrypted in pg_proc. this is an ideal
solution, but the most work.

merlin

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

#3 (**permalink**) 04-19-2008, 10:43 AM

Roberts, Jon escribiÃ³:
> So your suggestion is first to come up with a query that dynamically checks
> permissions and create a view for it. Secondly, change pgAdmin to reference
> this view in place of pg_proc. Actually, it should be extended to all
> objects in the database, not just pg_proc. If you don't have this
> dictionary role, you shouldn't be able to look at any of the objects in the
> database unless you own the object or have been granted rights to the
> object.

Right.

Another thing that just occured to me was to rename pg_proc to something
else, and create the restricted view using the pg_proc name. This
sounds dangerous in terms of internals, but actually the system catalogs
are invoked by OID not name, so maybe it will still work.

You do need to make sure that superusers continue to see all functions
though ... (the view test should really be "does the current user have
access to this function".)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: viewing source code