Thread: How to allow users to log on only from my application not from pgadmin

My application implements field and row level security.
I have custom table of users where user privileges are described.

However user can login directly to database using pgAdmin. This bypasses
the security.

How to allow users to login only from my application ?
I think I must create server-side pgsql procedure for login validation.

How to implement custom authentication in server side ?

How to force PostgreSQL to call stored procedure for user logon validation
when user logins to PostgreSQL ?

Andurs.

Andrus wrote:

> My application implements field and row level security.
> I have custom table of users where user privileges are described.
>
> However user can login directly to database using pgAdmin. This bypasses
> the security.
>
> How to allow users to login only from my application ?
> I think I must create server-side pgsql procedure for login validation.

What role are your users using to login via PgAdmin? Why not simply deny
them access in pg_hba.conf?

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Doesn't pg_hba.conf just deal with user connections?  If you denied via
pg_hba.conf, wouldn't you also deny access for the application?  Can
pg_hba.conf authenticate based on a per application basis?  I wasn't
aware of anything like that.  I'm not an expert on this, so I could be
wrong.

This is similar to my problem discussed in my question about the maximum
number of users.  What I would do is create a user group that isn't
given access to the privs table first of all.  Fine grained access is a
much trickier problem, though.  Think about triggers and database
procedures to stop people from messing with data at a row level.  You
also need to think about issues like referential integrity at the
database level instead of having your application enforce it.

What are the types of things you want to protect against?  Here are the
things I'm working on.

1. Per user access.  For instance in a timesheet file, users should only
be able to access/update data on their own timesheets, and only if the
timesheets have not been approved by an administrator.  This is typical
row level authentication, I think, where individual users can only deal
with data that relates to them individually and only under certain
circumstances.

2. Restricting certain tables to certain users.  Well that's easy.  You
just use the "grant" command.

3. Restricting certain columns of certain tables to certain users.  This
would be something like an "approved" or "active" column where only
administrators can set these values.  This would have to be done with
triggers and procedures.

4. Read only for certain users.  Again, you can user the "grant" command
to grant privs to only one user.

I'll forward what I develop to anybody who's interested when I finish it up.

In a certain sense, the proxy app I described and then admitted was
incorrect in another thread solves this problem much more simply.
You're defining your security at an application level, which is much
simpler and probably less error prone, than writing a slue of triggers
and procedures in sql.  I have to admit I still go back and forth on
this issue, although I understand the reasons for keeping it all on a
single, proven, fast, robust server.  Still, exposing even a minor
subset of raw sql access to a database can be potentially very
dangerous.  To be honest, at this point I wouldn't even consider
non-verified (meaning I have a real name, credit cart, etc) Internet
access directly to my database server.

Just some thoughts.





Raymond O'Donnell wrote:
> Andrus wrote:
>
>> My application implements field and row level security.
>> I have custom table of users where user privileges are described.
>>
>> However user can login directly to database using pgAdmin. This bypasses
>> the security.
>>
>> How to allow users to login only from my application ?
>> I think I must create server-side pgsql procedure for login validation.
>
> What role are your users using to login via PgAdmin? Why not simply
> deny them access in pg_hba.conf?
>
> Ray.
>
>
> ---------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> ---------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Furface wrote:

> 2. Restricting certain tables to certain users.  Well that's easy.  You
> just use the "grant" command.
>
> 3. Restricting certain columns of certain tables to certain users.  This
> would be something like an "approved" or "active" column where only
> administrators can set these values.  This would have to be done with
> triggers and procedures.
>

What I would do here is break the table in two. One table is where the
user enters their data, the other is where the admin enters their's and
use privileges to stop the user seeing the admin section.

I may think about using an archive table that stores approved timesheets
when the admin approves the timesheet, and prevent users access
(preventing them from accessing other than current data)



--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

>> My application implements field and row level security.
>> I have custom table of users where user privileges are described.
>>
>> However user can login directly to database using pgAdmin. This bypasses
>> the security.
>>
>> How to allow users to login only from my application ?
>> I think I must create server-side pgsql procedure for login validation.
>
> What role are your users using to login via PgAdmin?

Users should always login  form my application only.
pgAdmin login is reserved only for sysadmins who login as user postgres
always.

> Why not simply deny  them access in pg_hba.conf?

I have 5432 port opened to public internet and users use my application from
internet.

pg_hba doesn't allow access per application basics.

Denying acces from pg_hba.conf  also denies access from my application.

Andrus.

> Doesn't pg_hba.conf just deal with user connections?  If you denied via
> pg_hba.conf, wouldn't you also deny access for the application?  Can
> pg_hba.conf authenticate based on a per application basis?  I wasn't aware
> of anything like that.  I'm not an expert on this, so I could be wrong.
>
> This is similar to my problem discussed in my question about the maximum
> number of users.  What I would do is create a user group that isn't given
> access to the privs table first of all.  Fine grained access is a much
> trickier problem, though.  Think about triggers and database procedures to
> stop people from messing with data at a row level.  You also need to think
> about issues like referential integrity at the database level instead of
> having your application enforce it.

I have implemented row and column based access from my application.

I do'nt want to re-implement this is server-side since this is huge work and
PostgreSQL (and probably any other dbms) does not support this easily.

> What are the types of things you want to protect against?  Here are the
> things I'm working on.
>
> 1. Per user access.  For instance in a timesheet file, users should only
> be able to access/update data on their own timesheets, and only if the
> timesheets have not been approved by an administrator.  This is typical
> row level authentication, I think, where individual users can only deal
> with data that relates to them individually and only under certain
> circumstances.

I'd call this row level acces. Yes, I need this since I hold different
documents in same table.

> 2. Restricting certain tables to certain users.  Well that's easy.  You
> just use the "grant" command.

This does'nt solve my major issues: column and row level acces restriction.

> 3. Restricting certain columns of certain tables to certain users.  This
> would be something like an "approved" or "active" column where only
> administrators can set these values.  This would have to be done with
> triggers and procedures.

I have implemented this already in my application.
I should be huge very PostgreSQL specific work, using rules for write access
etc.
I do'nt want to implement this.

> 4. Read only for certain users.  Again, you can user the "grant" command
> to grant privs to only one user.

I have singe tabel contianing invoices, orders etc.
Some users are allowd to see only invoice rows some users only order rows.

Some users are allowed to see purchase price column, some not.
Sme users can modify sales price column, some not.

> I'll forward what I develop to anybody who's interested when I finish it
> up.
>
> In a certain sense, the proxy app I described and then admitted was
> incorrect in another thread solves this problem much more simply.

Can yuo provide some link where I can read about this ?

> You're defining your security at an application level, which is much
> simpler and probably less error prone, than writing a slue of triggers and
> procedures in sql.  I have to admit I still go back and forth on this
> issue, although I understand the reasons for keeping it all on a single,
> proven, fast, robust server.  Still, exposing even a minor subset of raw
> sql access to a database can be potentially very dangerous.  To be honest,
> at this point I wouldn't even consider non-verified (meaning I have a real
> name, credit cart, etc) Internet access directly to my database server.

Yes, I have opened 5432 port for direct internet access.

I do'nt wat to re-design the whole application by using application server
or web services in server side.
I have few knowledge of PostgreSQL server side languages required to
re-implement appli server in PostgreSql server side.
I do'nt want to use PostgreSQL as application server by adding views , rules
and triggers since this all is huge work.

This is why I'm looking for application level authentication for PostgreSQL.
In this case I can use my existing application without making changes.
I'm currently using password mangling (application applies secret conversion
to password) but looking for better solution.
like using public key certificates.

Andrus.

On Sun, Jan 28, 2007 at 23:46:27 +0200,
  Andrus <kobruleht2@hot.ee> wrote:
> My application implements field and row level security.
> I have custom table of users where user privileges are described.
>
> However user can login directly to database using pgAdmin. This bypasses
> the security.
>
> How to allow users to login only from my application ?
> I think I must create server-side pgsql procedure for login validation.

Run the application on a machine you control. Then the application can
authenticate without the users being able to steal or piggyback on its
credentials.

>> However user can login directly to database using pgAdmin. This bypasses
>> the security.

If only certain privileged users are supposed to use pgAdmin, can you
arrange so that only they have access to it in the first place? - such
as granting execute permissions on pgAdmin only to the privileged users?

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

> Run the application on a machine you control. Then the application can
> authenticate without the users being able to steal or piggyback on its
> credentials.

Thank you for reply.

My application is GUI applicatio which must run in customer computer and
accesses to 5432 port in remote PostgreSQL server located in customer side
over internet.
I cannot control customer computers.

Andrus.

> If only certain privileged users are supposed to use pgAdmin, can you
> arrange so that only they have access to it in the first place? - such as
> granting execute permissions on pgAdmin only to the privileged users?

PgAdmin can be ran from customer computer.
It is not possible to disable customer running pgAdmin

Andrus.

Andrus wrote:
>> Run the application on a machine you control. Then the application can
>> authenticate without the users being able to steal or piggyback on its
>> credentials.
>
> Thank you for reply.
>
> My application is GUI applicatio which must run in customer computer and
> accesses to 5432 port in remote PostgreSQL server located in customer side
> over internet.
> I cannot control customer computers.
>
> Andrus.
>
>
>
>
> ---------------------------(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
>
>

If the users have access to the database via having a username/password
then it seems to me that they could use basically anything to connect
via ODBC to the database and retrive/look at/update data. M$ Excel,
Acces, reporting things like crystal reports etc and of course pgAdmin.

If you hide the database username and password within your application
(i.e. encrypted within the source code) so they cannot see the
credentials that you connect to the database with internally then they
have no means by which to connect to it using any other programs.

What I gather is users in your case are set up as database users rather
then having a users table on which your application authenticates. The
downside of doing it the way you are doing it is always going to be that
any user with a database username and password can connect to the
database by any means they come by. I'm no Postgres expert, but I'm sure
like any other RDBMS, postgres does not know, nor care, what application
is doing the connection but rather just accepts an ODBC connection and
the credentials that are passed to it.

Regards,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers

If hiding the password in your application is an option, i.e. you only
have one database your application will ever connect to, then at least
scramble the password within your application with some complex algorithm.

If you can't hide the password in your application, then you need to
deny access to whatever parts of the database you don't want people to
look at and only allow access through stored procedures.  For instance,
you obviously wouldn't want access to your user/password table.  Grant
access to that only to an administrator.  Then create a function like
"checkPassword" for people to logon to your database through your
application.  You need to do something like this for every table/row you
want to hide.  If you don't want ANY raw access to your database, then
create your application with stored procedures, functions, and triggers,
basically have no raw sql in your application.  Call those procedures
and functions from your application without any raw sql.  That's the
only way to deal with it.

The other alternative, which I mentioned in another thread, is to create
a proxy server application where you put your sql calls in a server that
sits between your application and postgresql.   I'm actually designing
such a server for Internet users who want to access my application.
I'll probably open source it, so let me know if your interested in the
source.  I know this is a controversial subject.  I personally prefer to
code my applications with C++ and Java instead of server side sql, but
of course, that's just me, and there are different situations where
different methodologies are appropriate or not.



>
> If you hide the database username and password within your application
> (i.e. encrypted within the source code) so they cannot see the
> credentials that you connect to the database with internally then they
> have no means by which to connect to it using any other programs.
>
> What I gather is users in your case are set up as database users
> rather then having a users table on which your application
> authenticates. The downside of doing it the way you are doing it is
> always going to be that any user with a database username and password
> can connect to the database by any means they come by. I'm no Postgres
> expert, but I'm sure like any other RDBMS, postgres does not know, nor
> care, what application is doing the connection but rather just accepts
> an ODBC connection and the credentials that are passed to it.
>
> Regards,
> Paul.
>

One other thing.  Another approach to this problem would be to have some
sort of code signing/authentication capabilities for the postgresql
server.  For instance, you login as an administrator (some sort of
enhanced privs), you get to look at the databases you have permission
for.  Otherwise, postgresql has to recognize the application.  Has this
ever been discussed?



Paul Lambert wrote:
> Andrus wrote:
>>> Run the application on a machine you control. Then the application can
>>> authenticate without the users being able to steal or piggyback on its
>>> credentials.
>>
>> Thank you for reply.
>>
>> My application is GUI applicatio which must run in customer computer and
>> accesses to 5432 port in remote PostgreSQL server located in customer
>> side over internet.
>> I cannot control customer computers.
>>
>> Andrus.
>>
>>
>>
>>
>> ---------------------------(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
>>
>>
>
> If the users have access to the database via having a
> username/password then it seems to me that they could use basically
> anything to connect via ODBC to the database and retrive/look
> at/update data. M$ Excel, Acces, reporting things like crystal reports
> etc and of course pgAdmin.
>
> If you hide the database username and password within your application
> (i.e. encrypted within the source code) so they cannot see the
> credentials that you connect to the database with internally then they
> have no means by which to connect to it using any other programs.
>
> What I gather is users in your case are set up as database users
> rather then having a users table on which your application
> authenticates. The downside of doing it the way you are doing it is
> always going to be that any user with a database username and password
> can connect to the database by any means they come by. I'm no Postgres
> expert, but I'm sure like any other RDBMS, postgres does not know, nor
> care, what application is doing the connection but rather just accepts
> an ODBC connection and the credentials that are passed to it.
>
> Regards,
> Paul.
>

Mark Walker wrote:
> One other thing.  Another approach to this problem would be to have some
> sort of code signing/authentication capabilities for the postgresql
> server.  For instance, you login as an administrator (some sort of
> enhanced privs), you get to look at the databases you have permission
> for.  Otherwise, postgresql has to recognize the application.  Has this
> ever been discussed?
>
>
>

I don't think it would be feasible for any RDBMS to recognise the
connecting application, certainly in my view the effort it would take to
alter the postmaster/odbc driver and others would be a lot more than the
apparent gain from having that functionality.

--
Paul Lambert
Database Administrator
AutoLedgers

But you don't have to turn it on by default for any particular database,
and you could sign any application you want for your individual server.


Paul Lambert wrote:
> Mark Walker wrote:
>> One other thing.  Another approach to this problem would be to have
>> some sort of code signing/authentication capabilities for the
>> postgresql server.  For instance, you login as an administrator (some
>> sort of enhanced privs), you get to look at the databases you have
>> permission for.  Otherwise, postgresql has to recognize the
>> application.  Has this ever been discussed?
>>
>>
>>
>
> I don't think it would be feasible for any RDBMS to recognise the
> connecting application, certainly in my view the effort it would take
> to alter the postmaster/odbc driver and others would be a lot more
> than the apparent gain from having that functionality.
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/31/07 20:00, Paul Lambert wrote:
> Mark Walker wrote:
>> One other thing.  Another approach to this problem would be to have
>> some sort of code signing/authentication capabilities for the
>> postgresql server.  For instance, you login as an administrator (some
>> sort of enhanced privs), you get to look at the databases you have
>> permission for.  Otherwise, postgresql has to recognize the
>> application.  Has this ever been discussed?
>
> I don't think it would be feasible for any RDBMS to recognise the
> connecting application, certainly in my view the effort it would take to
> alter the postmaster/odbc driver and others would be a lot more than the
> apparent gain from having that functionality.

I think that capability is already part of the ODBC spec.  Well, at
least, the ODBC driver for Rdb (http://www.oracle.com/rdb) and it's
remote-access framework (SQL/Services) knows the name of the apps
that attach.

$ MCR SQLSRV_MANAGE71
SQLSRV> CONNECT SERVER;
Connecting to server ...
Connected
SQLSRV> SHOW CLIENT;
Service: VECTOR_REF

  Connect    Client                          Executor
  Username   Node             State          PID       Application
  PROC_RPT   138.69.183.166   RUNNING BOUND  2025a062  Winsql
  PROC_RPT   138.69.183.216   RUNNING BOUND  202864f7  erdpro


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwUzAS9HxQb37XmcRAhc9AJ9QBUaSIWi7zNXaViGPE1CfgiG8qQCgump0
Ys78nYGFPzNjKrlnu/CfU0M=
=0OCA
-----END PGP SIGNATURE-----

Oops, making a fool of myself again.  I don't think this is possible.
Code signing authentication works by comparing an application to a
digital signature that can't be generated without a password.  Since the
server doesn't have a copy of the application or signature, it won't
work.  Oh well, it sounded good.



Mark Walker wrote:
> But you don't have to turn it on by default for any particular
> database, and you could sign any application you want for your
> individual server.
>
>
> Paul Lambert wrote:
>> Mark Walker wrote:
>>> One other thing.  Another approach to this problem would be to have
>>> some sort of code signing/authentication capabilities for the
>>> postgresql server.  For instance, you login as an administrator
>>> (some sort of enhanced privs), you get to look at the databases you
>>> have permission for.  Otherwise, postgresql has to recognize the
>>> application.  Has this ever been discussed?
>>>
>>>
>>>
>>
>> I don't think it would be feasible for any RDBMS to recognise the
>> connecting application, certainly in my view the effort it would take
>> to alter the postmaster/odbc driver and others would be a lot more
>> than the apparent gain from having that functionality.
>>
>
>
> ---------------------------(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
>

On Thu, Feb 01, 2007 at 10:24:51 +0900,
  Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
>
> If you hide the database username and password within your application
> (i.e. encrypted within the source code) so they cannot see the
> credentials that you connect to the database with internally then they
> have no means by which to connect to it using any other programs.

This is not real security. Encrypting the data in the application only works
if the application is running on a computer you control. If the "customer"
can get their own copy of the client and run it on a computer they control
then they can steal or borrow the applications credentials.

You want to either run the app on a computer you control or have a contract
with the customers prohibiting them from connecting to the database other than
by using the app.

>> My application implements field and row level security.
>> I have custom table of users where user privileges are described.
>>
>> However user can login directly to database using pgAdmin. This bypasses
>> the security.
>>
>> How to allow users to login only from my application ?
>> I think I must create server-side pgsql procedure for login validation.

Say that your application offers a way for each user to set/change his own password.

When I (using your application) change my password, you could combine my new password with a secret value and then send the result to the PG server (so now the PG server thinks that my password is my_password+your_secret).

Then each time I log into your application (and I provide a password), you combine my password with the same secret before sending the login request to the PG server.

Every user can have his/her own account (in the PG server) but they won't be able to log into the server without going through your application first.

Does that help?


            -- Korry

> Say that your application offers a way for each user to set/change  
> his own password.
>
> When I (using your application) change my password, you could  
> combine my new password with a secret value and then send the  
> result to the PG server (so now the PG server thinks that my  
> password is my_password+your_secret).

This is a special case of (2,2) secret sharing:

   http://en.wikipedia.org/wiki/Secret_sharing

Here the secret is the actual password, a+b, shared into two parts, a  
and b. The above scheme suffers from the problem that the user now  
knows quite a lot about the secret.  

Hmmm... how would the user know anything about the secret unless he could somehow get to the resulting combined password?

For example, if my password is "chocolate" and the application secret is "fudge", I can't recover any part of the combination "chocolate-fudge" unless I can:

a) see "chocolate-fudge" in the network data stream (assume that I can't because Andrus is smart enough to avoid sending cleartext passwords over the net)
b) see "chocolate-fudge" in the PG password table (again, Andrus is smart enough to use MD5 authentication so "chocolate-fudge" is never stored on the server)

If this is an issue, there are  
more sophisticated combining schemes that give the user no advantage  
over someone who knows neither half of the secret.

Absolutely, but I wanted to get the basic idea across.  Andrus' application knows the correct password for each user (or, more precisely, his application knows how to derive the correct password from the user-supplied password - Andrus doesn't have to store each password on the client side or anything like that) so his application can log in, but the users don't know any valid passwords so they can't get into the database with a tool like PgAdmin.

So, in your opinion, this isn't a crazy idea?  It should work?  But it could be made more secure if Andrus is particularly paranoid.

            -- Korry

P.S. Thanks for the feedback.

Bruno Wolff III wrote:
> On Thu, Feb 01, 2007 at 10:24:51 +0900,
>   Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
>> If you hide the database username and password within your application
>> (i.e. encrypted within the source code) so they cannot see the
>> credentials that you connect to the database with internally then they
>> have no means by which to connect to it using any other programs.
>
> This is not real security. Encrypting the data in the application only works
> if the application is running on a computer you control. If the "customer"
> can get their own copy of the client and run it on a computer they control
> then they can steal or borrow the applications credentials.

How?

If it is encrypted within the source code then the only way to steal the
credentials would be to reverse engineer the application. And if someone
is going to do that then you can be relatively assured that they are
going to do anything and everything to get around whatever other
security you can offer. At which point you could send the law after them
for breach of copyright or other such law - at least that is the case
down here in Australia.

We have an application which connects to a database in MySQL. Each user
has their own username/password to log onto the application which does
so through authenticating against a users table in the db. The
application itself has hard-coded within a username/password to get the
initial access to the database. With somewhere in the vicinity of 1,000
people using this particular application we've not seen a case of anyone
accessing it using anything other than our application.

>
> You want to either run the app on a computer you control

It's not always feasible to host the application main on your own
server. Depending on network distance, traffic, size of application,
number of users etc, it could require some extremely high spec hardware
to host and beefed up network connections. This is not possible for a
lot of service providers out there, not to mention that those willing to
reverse engineer the software (or run packet sniffers and decrypt
network traffic) to get the password out of it would still find a way of
determining the password your hosted app is using.

> or have a contract
> with the customers prohibiting them from connecting to the database other than
> by using the app.

If customers access a database hosted by a service provider it is
generally the norm to have some clauses in the contract pertaining to
data protection and ownership making "access to provider hosted data by
any means other than those authorised by the provider" a breach of contract.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

If security is as big a concern as it appears to be for Andrus then I
would suggest that a bigger approach involving a number of security
steps need to be taken. The password used to connect to the db initially
  is just the first. My standard practice however is to never allow the
users to see the password that they connect to the db with.
Options as I see it:

1.) Hard-code the password (perhaps in encrypted form) within the source
of the application and do not supply that password to anyone else. This
app knows how to connect to the db, and then data access is determined
by credentials supplied by the user that authenticate against a users
table in the db.

2.) Allow the user to specify their own password, but hash the password
using some hashing algorithm - perhaps even your own custom written one
- before sending that in the create user command to Postgres. Connecting
via other means (pgAdminIII, M$ Access etc) would therefore fail as they
cannot provide the same hashed password.

3.) Do as Korry suggested in appending (or perhaps prepending) a 'secret
value' to the user supplied password.

4.) Combine all above options... Allow the user to specify their own
password to which you append a hard-coded 'secret value' which has been
encrypted in the source of your application and then you hash the
resultant password string before sending to Postgres.

My AUD$0.02

--
Paul Lambert
Database Administrator
AutoLedgers

I'm curious.  How do you feel about having a scrambling algorithm
embedded in your application, but having the scrambled password publicly
readable in a config file?  Does that seem secure?  This is what you
have to do if you want your users to connect to different databases
choosing their own password.

How would you deal with open source applications where the
scrambling/unscrambling algorithms would presumably be public?  Are
there  methodologies for developing custom algorithms that could be
triggered during builds?

>
> If it is encrypted within the source code then the only way to steal
> the credentials would be to reverse engineer the application.

Mark Walker wrote:
> I'm curious.  How do you feel about having a scrambling algorithm
> embedded in your application, but having the scrambled password publicly
> readable in a config file?  Does that seem secure?  This is what you
> have to do if you want your users to connect to different databases
> choosing their own password.

I never said anything about a readable config file.

If your users are specifying their own password and you want to store
passwords in a local config file on the users system then nothing is
stopping you doing so. If this was the case I would put only what the
user specifies in said config file, then when the password is retrieved
from the file on application startup, perform your hashing/adding secret
words etc. Anything I've done I do this way (in most cases though I use
the registry rather then a config file since I deal primarily with
weenblows.


> How would you deal with open source applications where the
> scrambling/unscrambling algorithms would presumably be public?  Are
> there  methodologies for developing custom algorithms that could be
> triggered during builds?
>

Open source applications are a different situation altogether. The kind
of security that Andrus appears to be looking for would give the
impression that it is not an open source application he is dealing with.
I could be wrong though.
Having said that, yes if you are using an open source application any
scrambling algorithms would be public, as would any passwords embedded
within your source.

In this case, then perhaps a 'proxy' application running on your own
server would be the best option (I think someone else suggested this
earlier in the thread) for serving the database requests.


--
Paul Lambert
Database Administrator
AutoLedgers

OK, I've thought about this a bit more and have come to the conclusion
that storing the password locally in any way is completely insecure.
Here are simple ways of hacking it:

1. If you use libpq in a shared lib(dll, etc).  Replace PQconnectdb with
your own version, rebuild and use your new dll to snatch the password.

2. If you use libpq in a static lib.  Search for the PQconnectdb's image
and do the same as #1.

3. If you don't use libpq.  Search for strings that contain things like
"host = ", "user = ", "password = ", etc and hack in your own code.

I think there are really only 2 ways to securely deal with this:

1. Each user has a postgresql role in a way that I mentioned in a
previous thread concerning the limit on number of users.  You'd also
have to secure your database via stored procedures and individual table
role based access.

2. Proxy server method.

Storing passwords locally would be anywhere from trivial to moderately
difficult to hack.


Paul Lambert wrote:
> Mark Walker wrote:
>> I'm curious.  How do you feel about having a scrambling algorithm
>> embedded in your application, but having the scrambled password
>> publicly readable in a config file?  Does that seem secure?  This is
>> what you have to do if you want your users to connect to different
>> databases choosing their own password.
>
> I never said anything about a readable config file.
>
> If your users are specifying their own password and you want to store
> passwords in a local config file on the users system then nothing is
> stopping you doing so. If this was the case I would put only what the
> user specifies in said config file, then when the password is
> retrieved from the file on application startup, perform your
> hashing/adding secret words etc. Anything I've done I do this way (in
> most cases though I use the registry rather then a config file since I
> deal primarily with weenblows.
>
>
>> How would you deal with open source applications where the
>> scrambling/unscrambling algorithms would presumably be public?  Are
>> there  methodologies for developing custom algorithms that could be
>> triggered during builds?
>>
>
> Open source applications are a different situation altogether. The
> kind of security that Andrus appears to be looking for would give the
> impression that it is not an open source application he is dealing
> with. I could be wrong though.
> Having said that, yes if you are using an open source application any
> scrambling algorithms would be public, as would any passwords embedded
> within your source.
>
> In this case, then perhaps a 'proxy' application running on your own
> server would be the best option (I think someone else suggested this
> earlier in the thread) for serving the database requests.
>
>

Mark Walker wrote:
> OK, I've thought about this a bit more and have come to the conclusion
> that storing the password locally in any way is completely insecure.
> Here are simple ways of hacking it:
>
> 1. If you use libpq in a shared lib(dll, etc).  Replace PQconnectdb with
> your own version, rebuild and use your new dll to snatch the password.
>
> 2. If you use libpq in a static lib.  Search for the PQconnectdb's image
> and do the same as #1.
>
> 3. If you don't use libpq.  Search for strings that contain things like
> "host = ", "user = ", "password = ", etc and hack in your own code.
> I think there are really only 2 ways to securely deal with this:
>
> 1. Each user has a postgresql role in a way that I mentioned in a
> previous thread concerning the limit on number of users.  You'd also
> have to secure your database via stored procedures and individual table
> role based access.

This solution won't help the initial problem of users being able to
connect with programs other then the original posters application. If
the user has a role in Postgres and they know the username/password -
which surely they will - then they will be able to connect using
pgAdminIII, M$ Access, M$ Excel, any other program that can open an ODBC
connection to look at and update a db which would then bypass any
business rules that have been built into the main application.

>
> 2. Proxy server method.

If it came down to it, a proxy server method would be preferable.

>
> Storing passwords locally would be anywhere from trivial to moderately
> difficult to hack.
>
>

I accept your reasonings in not wanting the password held within the
application. However that wouldn't rule out one of my suggestions in
supplying Postgres a hashed password. I.e. a new user is set up and they
specify their password as 'changeme'. If they know their password is
'changeme' they can open up pgAdminIII and log in with that password, or
the same with access, excel et al. If however your application hashes
that password and when doing the create role in PG, it sends the
password through as xH6&_33pq (made up hashing, not generated by any
formula) using md5 to further encrypt and PG stores that, then the user
can log in to your application which will do the translation in sending
the md5 encoded ODBC connect request, however 'changeme' will not work
in trying to connect from anything else, as that is not what PG has been
given as the users password.

Potential points of finding the password inappropriately will always
exist. Regardless of what you do to secure your data, there's a fair
chance that someone is going to get it. The question is, will the users
of your application have the ability/tools to reverse engineer a program
to find out its hashing algorithm or sniff network traffic and decrypt
it to find the password being used in the connection string.

As I keep telling my customers, the easiest way for me to secure your
data is to unplug the network cable on the server. ;)


--
Paul Lambert
Database Administrator
AutoLedgers

On 1/29/07, Andrus <kobruleht2@hot.ee> wrote:
> My application implements field and row level security.
> I have custom table of users where user privileges are described.
>
> However user can login directly to database using pgAdmin. This bypasses
> the security.
>
> How to allow users to login only from my application ?
> I think I must create server-side pgsql procedure for login validation.
>
> How to implement custom authentication in server side ?
>
> How to force PostgreSQL to call stored procedure for user logon validation
> when user logins to PostgreSQL ?

1. Make sure your application does not log in as database superuser.
Grant access to all tables but do not give the super user account/pw
to your customers or your application.
2..You can disable pgadmin/psql by doing things like:

revoke select on pg_proc to public;

This will completely hose pgadmin and also make it hard to work from
psql.  It will not hurt your ability to run functions.  Keep in mind
you cannot reduce access to below what is allowed by the application,
just make it a bit harder to browse the database, etc.

merlin

On Fri, Feb 02, 2007 at 07:20:04 +0900,
  Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
> How?

Use a debugger.

> If it is encrypted within the source code then the only way to steal the
> credentials would be to reverse engineer the application. And if someone
> is going to do that then you can be relatively assured that they are
> going to do anything and everything to get around whatever other
> security you can offer. At which point you could send the law after them
> for breach of copyright or other such law - at least that is the case
> down here in Australia.
>
> We have an application which connects to a database in MySQL. Each user
> has their own username/password to log onto the application which does
> so through authenticating against a users table in the db. The
> application itself has hard-coded within a username/password to get the
> initial access to the database. With somewhere in the vicinity of 1,000
> people using this particular application we've not seen a case of anyone
> accessing it using anything other than our application.

I imagine most people's customers don't try to work around broken security.
The scheme you have described above is broken.

> >You want to either run the app on a computer you control
>
> It's not always feasible to host the application main on your own
> server. Depending on network distance, traffic, size of application,
> number of users etc, it could require some extremely high spec hardware
> to host and beefed up network connections. This is not possible for a
> lot of service providers out there, not to mention that those willing to
> reverse engineer the software (or run packet sniffers and decrypt
> network traffic) to get the password out of it would still find a way of
> determining the password your hosted app is using.
>
> >or have a contract
> >with the customers prohibiting them from connecting to the database other
> >than
> >by using the app.
>
> If customers access a database hosted by a service provider it is
> generally the norm to have some clauses in the contract pertaining to
> data protection and ownership making "access to provider hosted data by
> any means other than those authorised by the provider" a breach of contract.

Well, then that is really your protection. The above security by obscurity
is just a way to help keep the honest people honest.

<korryd@enterprisedb.com> wrote:

>> This is a special case of (2,2) secret sharing: http://
>> en.wikipedia.org/wiki/Secret_sharing Here the secret is the actual
>> password, a+b, shared into two parts, a and b. The above scheme
>> suffers from the problem that the user now knows quite a lot about
>> the secret.
>
> Hmmm... how would the user know anything about the secret unless he
> could somehow get to the resulting combined password?
>
> For example, if my password is "chocolate" and the application
> secret is "fudge", I can't recover any part of the combination
> "chocolate-fudge" unless ...

Assuming that you how the compound password is generated, you now
know that it starts with "chocolate" - any attack now has a smaller
space to search.  The more sophisticated compounding schemes work
such that even knowing part of the secret, and the compounding
method, the search space is the same size as if you don't know any
part of the secret.

> So, in your opinion, this isn't a crazy idea?  It should work?  But
> it could be made more secure if Andrus is particularly paranoid.

Yah, that's all I meant to indicate.  As others have observed, a
determined user can sniff the compound password out if they really
wish.  I suspect the only really secure approach is some sort of
challenge-response algorithm, or a one-time pad in the application -
in either case, whatever the black-hat user sniffs off the wire or
with a debugger changes every session.

- John D. Burger
   MITRE

Actually in theory it is possible to completely secure a database by
putting all your business logic in stored procedures/functions and
allowing only raw database access to administrators.  Plenty of people
do this.  In fact if I were designing something that had lots of users
who had relatively simple and repetitive interactions with my database,
for instance a financial banking system, I would probably design it that
way.


Actually I don't know a lot about banking systems.  I'm just guessing
that the types of stuff I do at ATMs and online at my bank are not as
complex as ERP and billing systems that I design for some of my
customers.  Banking systems I would think have problems of massive
amounts of users, replication, synchronization, fault tolerance, and
security which are different than problems of pure business logic.


But anyway, you can hide any table you want completely from any role in
any applications with proper use of the Grant statement.  You then meter
your access through your procedures.  This, again, is an issue of where
you want your business logic to reside, in your client application or on
the server.  As I have said in a previous thread, I prefer to code
client applications in languages I'm very familiar with like C++ and
Java.  However, I wouldn't go as far as to say that's the "right" way to
do it.




>>
>> 1. Each user has a postgresql role in a way that I mentioned in a
>> previous thread concerning the limit on number of users.  You'd also
>> have to secure your database via stored procedures and individual
>> table role based access.
>
> This solution won't help the initial problem of users being able to
> connect with programs other then the original posters application. If
> the user has a role in Postgres and they know the username/password -
> which surely they will - then they will be able to connect using
> pgAdminIII, M$ Access, M$ Excel, any other program that can open an
> ODBC connection to look at and update a db which would then bypass any
> business rules that have been built into the main application.
>