On 11/30/06, John McCawley [EMAIL PROTECTED] wrote:
I am faced with a very new problem for me, which is that my app is going
to be used directly by several companies utilizing one server. (i.e.
these companies will be able to go under the hood quite a bit more
than we typically allow with this
John McCawley wrote:
Of course I still have to modify my web app and schema to facilitate the
new security structure, but I was never too worried about handling it in
my app...My concern was allowing people direct access to the underlying
DB while a) blocking them from viewing others' data,
How about this:
* Have one master schema that holds all physical tables. This schema
is accessible only by the superuser.
* Create a schema which contains views that mirror the master schema.
This is the schema that the customers connect to, each using a
different db role, and since it's
That's the first idea I've seen that looks like it might actually
work... (Not that the other ideas were bad, but I just couldn't see how
I could fit the solutions into my current app)
So what would my user setup look like? Would it look something like this:
createuser joe
grant select on
On 1 dec 2006, at 15.19, John McCawley wrote:
That's the first idea I've seen that looks like it might actually
work... (Not that the other ideas were bad, but I just couldn't see
how I could fit the solutions into my current app)
So what would my user setup look like? Would it look
Oh, I see, so there's one master schema, and one customer schema, and
the customer schema views are automatically filtered based on
login...Makes sense...I will definitely try to implement this, thanks!
Niklas Johansson wrote:
On 1 dec 2006, at 15.19, John McCawley wrote:
That's the first
John McCawley wrote:
Oh, I see, so there's one master schema, and one customer schema, and
the customer schema views are automatically filtered based on
login...Makes sense...I will definitely try to implement this, thanks!
I've on-and-off toyed with the idea of accomplishing a similar
I have been using PostgreSQL for years in my web apps, and so far in my
career I have not had to deal with database-side permissions issues.
i.e. when I have multiple clients, or hands on the data, everyone
interfaces through my web app and I handle security there. The app
enforces what data
On Thu, Nov 30, 2006 at 12:48 PM, in message
[EMAIL PROTECTED],
John McCawley [EMAIL PROTECTED] wrote:
4) Create views for each client that filter the underlying table data
to
only show them their data. The only database objects they would have
read permission on are these views. Come
I have been using PostgreSQL for years in my web apps, and so far in my
career I have not had to deal with database-side permissions issues.
i.e. when I have multiple clients, or hands on the data, everyone
interfaces through my web app and I handle security there. The app
enforces what data
It seems that this approach would suffer the same problem as the one I
outlined in 1) Actually separate client data by table. I would have
to modify the logic of my web app...My web app currently handles all of
the data, regardless of company, so it would have to aggregate the data
from the
search_path=$user in postgresql.conf
and you create one schema for each user with the user name as name
Rodrigo
John McCawley wrote:
It seems that this approach would suffer the same problem as the one I
outlined in 1) Actually separate client data by table. I would have
to modify the
Note that my in my current code, application-level permissions are
completely detached from database permissions. The entire web app uses
one user/pass to login to the database. The web app is used both by
individual companies who can only view their data, and also the
overseeing company
Is it possible to have each user connect via different postgresql
account? if so, then you can use
alter user set search_path='common_schema','user_schema';
where common schema has the things that would be the same for each
instance of the app, and user_schema is the name of that user's schema.
On Thu, 2006-11-30 at 13:45, John McCawley wrote:
Note that my in my current code, application-level permissions are
completely detached from database permissions. The entire web app uses
one user/pass to login to the database. The web app is used both by
individual companies who can
It seems that this approach would suffer the same problem as the one I
outlined in 1) Actually separate client data by table. I would have
to modify the logic of my web app...My web app currently handles all of
the data, regardless of company, so it would have to aggregate the data
from the
Maybe I'm not understanding what you're getting at, so I'll throw out an
example:
--
With my current architecture, smartlowe logs in, but his login is
handled at the application layer, so his database connection is simply
foo. He inserts a hundred records in the
On Thu, 2006-11-30 at 14:07, John McCawley wrote:
Maybe I'm not understanding what you're getting at, so I'll throw out an
example:
I completely understand what you're saying, but I'm not quite getting
the reasons for it. For instance:
HOWEVER, when user big_daddy logs into the
On Thu, 2006-30-11 at 17:22 -0400, [EMAIL PROTECTED]
wrote:
Date: Thu, 30 Nov 2006 12:48:53 -0600
From: John McCawley [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Subject: Separation of clients' data within a database
Message-ID: [EMAIL PROTECTED]
... I would assume there are no row
Why does user big_daddy need to access everybody's data? Who is he?
What's his role? It seems like a big security problem waiting to
happen, but that's just me.
Uncle Sam :)
This is one of those fundamental problems you run into when you make a
design decision up front (user perms in
20 matches
Mail list logo
