Thread: Preventing changes to default settings of a collective account?

Preventing changes to default settings of a collective account?

From
Alex Gutman
Date:
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

Re: Preventing changes to default settings of a collective account?

From
Tom Lane
Date:
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

Re: Preventing changes to default settings of a collective account?

From
"Goulet, Dick"
Date:
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

Re: Preventing changes to default settings of a collective

From
Scott Marlowe
Date:
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

Re: Preventing changes to default settings of a collective account?

From
Alex Gutman
Date:
>> (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