pgsql-general@postgresql.org
I have a PL/Perl stored procedure that's worked fine on my development boxes but is suddenly failing when installed on a test machine. Both my test machines and the new machine are RHEL AS5. The differences are the test machine is 64-bit, and running Postgres 8.1.18 instead of my development machine which is 32-bit and running Postgres 8.2.7. The error in the postgresql log is: ERROR: error from Perl function: Undefined subroutine &main::spi_prepare called at line 2. LOG: unexpected EOF on client connection My first thought was that the postgresql-pl package wasn't installed, but it appears to be there. Does anyone else know what sort of installation/configuration error could cause a failure like this? Thanks. -Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
pgsql-general@postgresql.org
I have a PL/Perl stored procedure that's worked fine on my development boxes but is suddenly failing when installed on a test machine. Both my test machines and the new machine are RHEL AS5. The differences are the test machine is 64-bit, and running Postgres 8.1.18 instead of my development machine which is 32-bit and running Postgres 8.2.7. The error in the postgresql log is: ERROR: error from Perl function: Undefined subroutine &main::spi_prepare called at line 2. LOG: unexpected EOF on client connection My first thought was that the postgresql-pl package wasn't installed, but it appears to be there. Does anyone else know what sort of installation/configuration error could cause a failure like this? Thanks. -Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Advice needed on application/database authentication/authorization/auditing model
I have a web application with a Postgres backend. In my initial
prototype, I decided not to have a Postgres database user created for
each application user, opting instead to use my own users table.
Authentication of application users is done via PAM, so no password is
necessary in my users table -- I just let PAM do its thing, and if the
user is authenticated, I check for a record in my application's users
table to see if they're authorized to use the app, along with what
privileges they have, e.g.:
CREATE TYPE USER_ROLE AS ENUM ('User', 'Auditor', 'Administrator');
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
displayname TEXT NOT NULL,
role USER_ROLE NOT NULL DEFAULT 'User'
);
Now that this is moving beyond a prototype stage, I need to tighten up
the authentication/authorization/access control model. In particular, I
need to add some basic audit trail functionality. I found a couple of
projects that help make auditing easy (tablelog and EMaj being the most
promising) but they both rely on the database users mapping 1:1 to
application users, which is currently not the case -- right now I've
only got one database user that's used for all of the pooled
connections, so the audit logs are showing that user instead of my
application user.
So, I'm wondering what others have done in similar situations. It seems
to me like the database connection pooling means I probably need the
connections to be made with a privileged "database superuser" account
that has permission to "SET ROLE" to each and every application user,
then "RESET ROLE" when it's done. That's a bit of a pain, but doable
Then there's the issue of application roles vs. database roles. I
wanted to have three roles in this application: regular users, auditors
(who can do everything regular users can, plus access audit tables to
view audit log tables and potentially restore data from them) and
administrators (who can do everything in the application, but shouldn't
be Postgres superusers.) Unfortunately, I can't figure out a clever way
to do this mapping, especially because Postgres doesn't allow users to
refer to system tables like pg_authid to do an explicit mapping of app
roles to database roles.
So, does anyone have any suggestions here? I feel like there's got to
be a way to do this, but I can't find anything relevant in the list
archives.
Thanks.
-Tony
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model
Hi Dmitriy, On 10/21/10 4:21 PM, Dmitriy Igrishin wrote: > IMO, you are trying to reinvent the wheel. Although, you may do it just for > fun. :-) Surely I am, but I think it made sense at the time. It doesn't make as much sense now that I need to audit every insert/update/delete in the database. > Why not just create "groups" via CREATE ROLE User ... and grants this > roles to the "users" (created via CREATE USER or CREATE ROLE ... LOGIN)Â ? The reason I shied away from this initially was the overhead of having to maintain user info in two places (the pg_catalog schema for postgres users, and in my application schema, with the user's real name, application preferences, etc.) It also seemed like the role information wasn't very accessible in the system catalogs -- I had noticed that the pg_group view was deprecated, and the query to get group information out of the pg_auth_members and pg_roles tables started to look very ugly, when I could just do a quick "is the user an administrator" check via a boolean flag in my app user's table. With my new requirements for auditing, using the database's roles makes more sense, but I still see some problems with it, even if I can solve the connection pooling problem by using persistent connections as you suggest. For one thing, in this app, all higher permissions include the lower permissions -- all administrators are auditors and regular users, and all auditors are regular users. So, my normal instinct would be to set it up like this: GRANT g_user TO g_auditor WITH ADMIN OPTION; GRANT g_auditor TO g_admin WITH ADMIN OPTION; Then, in theory, I could grant administrators the g_admin group, auditors the g_auditor group, etc. and they could do all the things the lower groups can. BUT, in my app, to check for access to audit functions, I can't do a simple query to see if the user is in the "g_auditor" group, because administrators aren't explicitly granted this group -- they get those permissions implicitly, but how do I know this from my application? Is there some kind of query I can do to get back all the groups a role is a member of? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model
On 10/23/10 11:01 AM, Craig Ringer wrote: > Yep. As for not explicitly mentioning "lower" roles when granting a > higher role (ie "admin" isn't also a "user") - role inheritance. I knew about role inheritance, I just didn't know about the pg_has_role() function for determining if a user has a role. That's helpful, but I really don't want to be hitting the database with a pg_has_role() call for every time I want to check if a user should have access to a certain page or function in my application. Normally, when the user logs in, I'd cache their user info, and any roles they have, either directly or indirectly. But how can I do this if I'm not directly making administrators members of the other groups they inherit the rights of? In other words, is there a convenience function or view I can use to get a list of all roles the user has access to, both directly or indirectly? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
