Обсуждение: Questions on setup and usage

We have a commerical application which we currently are running under a different database but would like to consider a enterprise version using PostGres.  There are several features in this other database that have become important to us.

1.  The ability to encrypt stored procedures and triggers.  I know that PostGres doesn't have this feature but is there a way to protect stored procedure and trigger source yet allow it to be executed by any user and how would it be done.

2.  We do not want the table schema to be accessed by outside applications.  Since our application is so normalized, accessing these tables directly will not make a lot of sense to our users so we create views that are more user friendly for doing reports, accessing the data via Excel, etc.  How would we do this in Postgres.

3.  We use active directory to validate a user's login.  We have "rules" and functionality security built in as stored procedures. How would we do this under Postgres.  We do this with a configuration variable during the login process stored process.  If the company uses active directory we use it to validate the user, if not then we present a user login and require that a login be manually entered.  For those companies that use active directory we try to have our application validate the AD so that the user isn't required to multiple applications.

Best Regard

Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote:

> 1.  The ability to encrypt stored procedures and triggers.  I know
> that PostGres doesn't have this feature but is there a way to
> protect stored procedure and trigger source yet allow it to be
> executed by any user and how would it be done.

You could code them in a compiled language (like C) and the user would
only see a reference to the library file.

> 2.  We do not want the table schema to be accessed by outside
> applications.  Since our application is so normalized, accessing
> these tables directly will not make a lot of sense to our users so
> we create views that are more user friendly for doing reports,
> accessing the data via Excel, etc.  How would we do this in
> Postgres.

You could put the tables in a separate schema, which you would not put
on the default search path.  The views could refer to the tables with
the schema qualifier.  This would not hide the tables from the users,
per se, but would cause them not to show on casual display, or allow
them to be accessed without explicitly specifying the schema.

> 3.  We use active directory to validate a user's login.  We have
> "rules" and functionality security built in as stored procedures.
> How would we do this under Postgres.  We do this with a
> configuration variable during the login process stored process.  If
> the company uses active directory we use it to validate the user, if
> not then we present a user login and require that a login be
> manually entered.  For those companies that use active directory we
> try to have our application validate the AD so that the user isn't
> required to multiple applications.

I don't know anything about that.  Perhaps someone else will jump in
with advice.

-Kevin

Please keep the list copied.  Also, it's best to quote the minimum
required for context, and to avoid top-posting.  (It's much easier for
most people to read this way.)

Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

>>> 3.  We use active directory to validate a user's login.  We have
>>> "rules" and functionality security built in as stored procedures.
>>> How would we do this under Postgres.  We do this with a
>>> configuration variable during the login process stored process.
>>> If the company uses active directory we use it to validate the
>>> user, if not then we present a user login and require that a login
>>> be manually entered.  For those companies that use active
>>> directory we try to have our application validate the AD so that
>>> the user isn't required to multiple applications.
>>
>> I don't know anything about that.  Perhaps someone else will jump
>> in with advice.

> Thanks, I think I can use Kerbos 5 in order to handle the security
> login.

> I also find that it would be nice that instead of putting a date to
> have the password expire on a user that you could set the number of
> days that a password expires in.  I guess we could write a user
> function for that.

> Is there any way using a character set or locale setting to make
> sure that the where clause is case insensitive other than using
> ILIke?

I believe so, but I'll leave the details to those more familiar with
the issue.

-Kevin

I know that by using lowercase object names that I can work with those
object in a case insensitive way, however is there a way through maybe a
locale or character set to make the data and the indexes case insensitive
such that you don't need to use ILIKE?

Best Regards

Michael Gould



"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote:
>
>> 1.  The ability to encrypt stored procedures and triggers.  I know
>> that PostGres doesn't have this feature but is there a way to
>> protect stored procedure and trigger source yet allow it to be
>> executed by any user and how would it be done.
>
> You could code them in a compiled language (like C) and the user would
> only see a reference to the library file.
>
>> 2.  We do not want the table schema to be accessed by outside
>> applications.  Since our application is so normalized, accessing
>> these tables directly will not make a lot of sense to our users so
>> we create views that are more user friendly for doing reports,
>> accessing the data via Excel, etc.  How would we do this in
>> Postgres.
>
> You could put the tables in a separate schema, which you would not put
> on the default search path.  The views could refer to the tables with
> the schema qualifier.  This would not hide the tables from the users,
> per se, but would cause them not to show on casual display, or allow
> them to be accessed without explicitly specifying the schema.
>
>> 3.  We use active directory to validate a user's login.  We have
>> "rules" and functionality security built in as stored procedures.
>> How would we do this under Postgres.  We do this with a
>> configuration variable during the login process stored process.  If
>> the company uses active directory we use it to validate the user, if
>> not then we present a user login and require that a login be
>> manually entered.  For those companies that use active directory we
>> try to have our application validate the AD so that the user isn't
>> required to multiple applications.
>
> I don't know anything about that.  Perhaps someone else will jump in
> with advice.
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax