pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Preventing changes to default settings of a collective account?
5+ messages / 4 participants
[nested] [flat]

* Preventing changes to default settings of a collective account?
@ 2005-02-24 13:33  Alex Gutman <agutman@emc.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Alex Gutman @ 2005-02-24 13:33 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Preventing changes to default settings of a collective account?
@ 2005-02-24 15:45  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alex Gutman <agutman@emc.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2005-02-24 15:45 UTC (permalink / raw)
  To: Alex Gutman <agutman@emc.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Preventing changes to default settings of a collective account?
@ 2005-02-24 17:36  Goulet, Dick <DGoulet@vicr.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Goulet, Dick @ 2005-02-24 17:36 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Alex Gutman <agutman@emc.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Preventing changes to default settings of a collective
@ 2005-02-24 17:43  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Goulet, Dick <DGoulet@vicr.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Scott Marlowe @ 2005-02-24 17:43 UTC (permalink / raw)
  To: Goulet, Dick <DGoulet@vicr.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alex Gutman <agutman@emc.com>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Preventing changes to default settings of a collective account?
@ 2005-02-24 18:22  Alex Gutman <agutman@emc.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Alex Gutman @ 2005-02-24 18:22 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-admin


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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-02-24 18:22 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-24 13:33 Preventing changes to default settings of a collective account? Alex Gutman <agutman@emc.com>
2005-02-24 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-24 18:22   ` Alex Gutman <agutman@emc.com>
2005-02-24 17:36 Re: Preventing changes to default settings of a collective account? Goulet, Dick <DGoulet@vicr.com>
2005-02-24 17:43 ` Re: Preventing changes to default settings of a collective Scott Marlowe <smarlowe@g2switchworks.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox