Thread: Preventing changes to default settings of a collective account?
Greetings, I've established a read-only collective account (sort of guest) with no privileges. Nevertheless, the collective user in question can still issue commands ALTER USER guest SET parameter TO value and so change the default setting of any user-alterable run-time configuration variable. Naturally, I'd like to prevent it - but there seems to be no privilege that I could revoke from that collective user in order to disable his changing default values of run-time configuration variables. (The NOCREATEUSER option used when creating the collective user does prevent it from changing its own password via ALTER USER guest WITH ... PASSWORD ... - which is good but not enough.) I've tried to prevent changes to the field pg_shadow.useconfig of the row pertaining to the collective user through CREATE OR REPLACE RULE pg_shadow_update_rule AS ON UPDATE TO pg_shadow WHERE NEW.usename = 'guest' DO INSTEAD NOTHING This trick didn't work. (I had little hope for its success from the beginning, as the table pg_shadow doesn't belong to any database, so I didn't expect the rule mechanism to work for it. I was connected to database template1 as user postgres when issuing the above CREATE OR REPLACE RULE command, if it's of any relevance.) Is there any way I could achieve my goal? With thanks in advance, Alex Gutman, grateful PostgreSQL user since 1998
Alex Gutman <agutman@emc.com> writes:
> (The NOCREATEUSER option used when creating the collective user does
> prevent it from changing its own password via
> ALTER USER guest WITH ... PASSWORD ...
You think so?
This approach is doomed to failure --- the system sees no reason not to
allow a user to change his own configuration, including his password.
> Is there any way I could achieve my goal?
Use more than one username.
regards, tom lane
Collective user accounts are all well & good so long as everyone using
it understands that you don't change stuff. If you've got a user who is
adamant that they have to have a specific password, etc... Then your
only recourse is to create them their own user account. I've done that.
It's a bit painful, but a lot less than having the collective/generic
access account messed with. Therefore I agree wholeheartedly with Tom.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, February 24, 2005 10:46 AM
To: Alex Gutman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Preventing changes to default settings of a
collective account?
Alex Gutman <agutman@emc.com> writes:
> (The NOCREATEUSER option used when creating the collective user does
> prevent it from changing its own password via
> ALTER USER guest WITH ... PASSWORD ...
You think so?
This approach is doomed to failure --- the system sees no reason not to
allow a user to change his own configuration, including his password.
> Is there any way I could achieve my goal?
Use more than one username.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Most of the time you have one login account, the problem would be better solved with a group. Most of the time... On Thu, 2005-02-24 at 11:36, Goulet, Dick wrote: > Collective user accounts are all well & good so long as everyone using > it understands that you don't change stuff. If you've got a user who is > adamant that they have to have a specific password, etc... Then your > only recourse is to create them their own user account. I've done that. > It's a bit painful, but a lot less than having the collective/generic > access account messed with. Therefore I agree wholeheartedly with Tom. > > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, February 24, 2005 10:46 AM > To: Alex Gutman > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Preventing changes to default settings of a > collective account? > > Alex Gutman <agutman@emc.com> writes: > > (The NOCREATEUSER option used when creating the collective user does > > prevent it from changing its own password via > > ALTER USER guest WITH ... PASSWORD ... > > You think so? > > This approach is doomed to failure --- the system sees no reason not to > allow a user to change his own configuration, including his password. > > > Is there any way I could achieve my goal? > > Use more than one username. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
>> (The NOCREATEUSER option used when creating the collective user does >> prevent it from changing its own password via >> ALTER USER guest WITH ... PASSWORD ... >You think so? > >This approach is doomed to failure --- the system sees no reason not to >allow a user to change his own configuration, including his password. I stand corrected. The command ALTER USER guest WITH ... PASSWORD ... now fails under my experimental setup - but it's not due to the NOCREATEUSER option used when creating the user guest. I haven't figured out yet how I've finally managed to make it fail (I've lost count of all the ruses I've tried). Yet in the worst case, I can use a special PAM authentication method for the user in question - in which case changing its password via ALTER USER will either fail or be null and void. So the protection of the user guest's password from user-made changes must allow for a documented solution. As to whether this approach is doomed to failure, I'm sure that it can succeed - the question is only the price. It may require a custom version of PostgreSQL - either built from patched sources or using a special loadable shared object (say, 'anon_ro_user.so') preloaded into each postmaster through a line like preload_libraries = '$libdir/anon_ro_user:anon_ro_user_init' in postgresql.conf. It's a very steep price that I'd like to avoid, though. >Use more than one username. Sorry, it's no option. I currently have about 700 users that could potentially use the same account (associated with an application through which solely these ~700 users connect to the database). I must eliminate the administrative overhead of adding/deleting these PostgreSQL users that shouldn't need to be declared in the first place. PS My PostgreSQL version is 7.4.7. Thanks, ----------------------------------------------------------------------------- Alex Gutman, EMC Corp. :: Tel (877) 362-2887 ext. 44962 :: Fax (508) 435-8852