Search Postgresql Archives

Re: Access to the DB

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> So: how to store the DB access password so that Joe User doesn't see it but=
>  the admin can update it when it is necessary? Should I have an app on the =
> server that the client would connect to or how?

This is probably overkill for your app (and may be for mine as well), but
here's how I addressed this situation.

First a little background:

I have been developing a program which is a table-driven web browser-based 
table query/update program designed for users rather than DBA's.  (It
allows the user to query/insert/update/delete data but not to change
the data structure.)

It was written in PHP with postgres in mind but also works with mysql 
and should work with any relational database that can be connected to 
via PHP/Pear.  

Because it is designed to work in a client-server environment, it has its 
own user/password scheme.  

The userid/password controls what applications the user can launch and
which database it connects to as well as what userid it is on that database,
and thus what data access privileges the user has.

The password itself is stored using a one-way encryption, but the connect 
string is stored using the password as an encryption key using the pgcrypto
package.  That way it cannot be easily decrypted.  

Here's my 'user' table description.  (There are a few other fields whose 
purpose I haven't referred to in this note.)

      Column       |         Type          | Modifiers
--------------------+-----------------------+-----------
pb_user_name       | character varying(20) |
pb_user_pw_timeout | integer               |
pb_user_connect    | bytea                 |
pb_user_pass       | text                  |
pb_user_perm       | text                  |
pb_user_comment    | text                  |

The tables that drive the app, including the user file, are in a separate 
database, with a connect string supplying a userid/password for read-ony 
access to that database.  (That connect string can be kept in a hidden file 
in the web directory or in the psql/lib directory.)

I hope to release this program into the open-source community either
later this year or in 2005, I don't think there's anything quite like
it out there.  It is currently in use at a client's office, and I use
it myself to manage several personal databases.  I already have a 
potential alpha tester in mind.

There are some hooks for native postgres-ism's like arrays that may have 
to be worked around in the next rewrite before I can release it, and of 
course any database-specific features could only be used in the entries 
for an app which accesses that database platform.  Also, the password 
concept may need to be reworked to make it compatible with the 
encrypt/decrypt capabilities of other database engines.

I hope this gives you some ideas.
--
Mike Nolan

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux