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

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:

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.

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.

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
---------------------------------------------------------------

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.

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:

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:

-----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:

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

<korryd@enterprisedb.com> wrote:

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. 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.

- John D. Burger
MITRE