Re: Is it possible to findout actual owner of table?

Hi,

I have created the stored procedure that allows particular users to create
the table. I want to prevent users to drop the tables owned by someone esle
and hence, I am making owner of each table created by this stored procedure
to super user(postgres) so that no one will be allowed to drop/alter table.
I want to allow to drop/alter the table by actuall user who ran the stored
procedure to create the table. Could anyone please suggest me how to proceed
with this?

Thanks,
Dipti

dipti shah <shahdipti1980(at)gmail(dot)com> writes:
> I have created the stored procedure that allows particular users to create
> the table. I want to prevent users to drop the tables owned by someone esle
> and hence, I am making owner of each table created by this stored procedure
> to super user(postgres) so that no one will be allowed to drop/alter table.
> I want to allow to drop/alter the table by actuall user who ran the stored
> procedure to create the table. Could anyone please suggest me how to proceed
> with this?

It sounds like you are going to a great deal of trouble to reinvent the
standard behavior. Why not forget the stored procedure and just let
users create and drop their own tables?

regards, tom lane

I don't want users to create/drop/alter anything directly. They have to use
stored procedure for everything. The stored procedure creates logging tables
and stores many other auditing information so it is madatory to prevent
users from running any direct commands.

Thanks,
Dipti

On Tue, Mar 9, 2010 at 11:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> dipti shah <shahdipti1980(at)gmail(dot)com> writes:
> > I have created the stored procedure that allows particular users to
> create
> > the table. I want to prevent users to drop the tables owned by someone
> esle
> > and hence, I am making owner of each table created by this stored
> procedure
> > to super user(postgres) so that no one will be allowed to drop/alter
> table.
> > I want to allow to drop/alter the table by actuall user who ran the
> stored
> > procedure to create the table. Could anyone please suggest me how to
> proceed
> > with this?
>
> It sounds like you are going to a great deal of trouble to reinvent the
> standard behavior. Why not forget the stored procedure and just let
> users create and drop their own tables?
>
> regards, tom lane
>

dipti shah wrote:
> I don't want users to create/drop/alter anything directly. They have
> to use stored procedure for everything. The stored procedure creates
> logging tables and stores many other auditing information so it is
> madatory to prevent users from running any direct commands.
>

may be you should use middleware instead and not let your users connect
directly to SQL at all.

so, any DDL changes, you'd call the middleware server, it would
authenticate the user, decide whats allowed, maintain your audit trail,
logging, etc. ditto, any operations that require database queries, etc,
would all be done by this middleware.

What is middleware?

Is it similar to stored procedure? That what I have been doing. I have
revoked all permissions from mydb schema from public and have SECURITY
DEFINER enable for stored procedure to allow creating/droping/altering
tables.

Thanks.

On Tue, Mar 9, 2010 at 12:19 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> dipti shah wrote:
>
>> I don't want users to create/drop/alter anything directly. They have to
>> use stored procedure for everything. The stored procedure creates logging
>> tables and stores many other auditing information so it is madatory to
>> prevent users from running any direct commands.
>>
>>
>
> may be you should use middleware instead and not let your users connect
> directly to SQL at all.
>
> so, any DDL changes, you'd call the middleware server, it would
> authenticate the user, decide whats allowed, maintain your audit trail,
> logging, etc. ditto, any operations that require database queries, etc,
> would all be done by this middleware.
>
>
>
>
>

dipti shah wrote:
> What is middleware?

An application server that does all the business logic. your user
software calls the application server to do things, and it in turn
accesses the database. User written software is not alloweed to
directly connecct to the databases at all. Classic example of a
middleware platform Apache Tomcat

Hmm...that would be too much work I think. Ayway, could you point me some
useful link for postgresql middleware?

Thanks.

On Tue, Mar 9, 2010 at 1:13 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> dipti shah wrote:
>
>> What is middleware?
>>
>
> An application server that does all the business logic. your user software
> calls the application server to do things, and it in turn accesses the
> database. User written software is not alloweed to directly connecct to
> the databases at all. Classic example of a middleware platform Apache
> Tomcat
>
>
>