Slony1 PostgreSQL Replication - Replicating pg

Author

Replicating pg_users

Kevin Gill

2006-02-13, 7:24 am

Is there a method for replicating the database users (pg_users)?

My application can create users and users can change passwords.

I use slony to create a backup database on my backup server. However, If I
have to switchover, I will not have my users and users I have will not have
passwords.

Thanks in advance.

Kevin

Andrew Sullivan

2006-02-13, 7:24 am

On Mon, Feb 13, 2006 at 11:36:06AM -0000, Kevin Gill wrote:
> Is there a method for replicating the database users (pg_users)?

No, because it's a system table, and system tables can't have
passwords.

> My application can create users and users can change passwords.

In general, that's a pretty dangerous thing to do anyway. Unless
you're actually building an administration engine for Postgres, it's
a loaded foot gun with pretty serious security implications. I'd
suggest you not do it this way. If you _do_ do it this way, then
your application must have superuser access, which means you can
read the slony schema. That means you could connect to the other
database engine(s) and add the users there, too.

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
You may save him from referring to variables in another package, but
you can't save him from writing a badly designed program to solve the
wrong problem, and taking forever to do it. --Paul Graham

Hannu Krosing

2006-02-13, 9:24 am

w5xoZWwga2VuYWwgcMOk
ZXZhbCwgRSwgMjAwNi0w
Mi0xMyBrZWxsIDExOjM2
LCBraXJqdXRhcyBL
ZXZpbiBHaWxsOgo+IElz
IHRoZXJlIGEgbWV0aG9k
IGZvciByZXBsaWNhdGlu
ZyB0aGUgZGF0YWJh
c2UgdXNlcnMgKHBnX3Vz
ZXJzKT8KCnBnX3VzZXIg
aXMgYSBWSUVXIGFuZCBh
cyBzdWNoIHlvdSB3
aWxsIHByb2JhYmx5IG5v
dCBiZSBhYmxlIHRvIHJl
cGxpY2F0ZQppdC4KCllv
dSBjb3VsZCB0cnkg
dG8gcmVwbGljYXRlIHRo
ZSB1bmRlcmx5aW5nIHRh
YmxlIChwZ19zaGFkb3cp
LCB0aG91Z2ggSSdt
Cm5vdCBzdXJlIGlmIHNs
b255IGFsbG93cyB5b3Ug
dG8gZG8gaXQuCgotLS0t
LS0tLS0tLS0tLS0K
SGFubnUKCgpfX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fXwpT
bG9ueTEtZ2VuZXJhbCBt
YWlsaW5nIGxpc3QKU2xv
bnkxLWdlbmVyYWxAZ2Jv
cmcucG9zdGdyZXNx
bC5vcmcKaHR0cDovL2di
b3JnLnBvc3RncmVzcWwu
b3JnL21haWxtYW4vbGlz
dGluZm8vc2xvbnkx
LWdlbmVyYWwK

Christopher Browne

2006-02-13, 9:24 am

Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org> writes:
> =C3hel kenal p=C3=A4eval, E, 2006-02-13 kell 11:36, kirjutas Kevin Gill:
>
> pg_user is a VIEW and as such you will probably not be able to
> replicate it.
>
> You could try to replicate the underlying table (pg_shadow), though
> I'm not sure if slony allows you to do it.

The trouble with trying to replicate system tables is that you can't
put triggers on them, which is what Slony-I requires...
-- =

let name=3D"cbbrowne" and tld=3D"ca.afilias.info" in String.concat "@" [nam=
e;tld];;
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Hannu Krosing

2006-02-13, 9:24 am

w5xoZWwga2VuYWwgcMOk
ZXZhbCwgRSwgMjAwNi0w
Mi0xMyBrZWxsIDA4OjM0
LCBraXJqdXRhcyBD
aHJpc3RvcGhlcgpCcm93
bmU6Cj4gSGFubnUgS3Jv
c2luZyA8aGFubnVAc2t5
cGUubmV0PiB3cml0
ZXM6Cj4gPiDDg2hlbCBr
ZW5hbCBww4PCpGV2YWws
IEUsIDIwMDYtMDItMTMg
a2VsbCAxMTozNiwg
a2lyanV0YXMgS2V2aW4g
R2lsbDoKPiA+PiBJcyB0
aGVyZSBhIG1ldGhvZCBm
b3IgcmVwbGljYXRp
bmcgdGhlIGRhdGFiYXNl
IHVzZXJzIChwZ191c2Vy
cyk/ Cj4gPgo+ID4gcGdfdXNl
ciBpcyBhIFZJ
RVcgYW5kIGFzIHN1Y2gg
eW91IHdpbGwgcHJvYmFi
bHkgbm90IGJlIGFibGUg
dG8KPiA+IHJlcGxp
Y2F0ZSBpdC4KPiA+Cj4g
PiBZb3UgY291bGQgdHJ5
IHRvIHJlcGxpY2F0ZSB0
aGUgdW5kZXJseWlu
ZyB0YWJsZSAocGdfc2hh
ZG93KSwgdGhvdWdoCj4g
PiBJJ20gbm90IHN1cmUg
aWYgc2xvbnkgYWxs
b3dzIHlvdSB0byBkbyBp
dC4KPiAKPiBUaGUgdHJv
dWJsZSB3aXRoIHRyeWlu
ZyB0byByZXBsaWNh
dGUgc3lzdGVtIHRhYmxl
cyBpcyB0aGF0IHlvdSBj
YW4ndAo+IHB1dCB0cmln
Z2VycyBvbiB0aGVt
LCB3aGljaCBpcyB3aGF0
IFNsb255LUkgcmVxdWly
ZXMuLi4KCnBlcmhhcHMg
aXQgY2FuIGJlIGRv
bmUgYnkgaGF2aW5nIGEg
dXNlciB0YWJsZSBtaXJy
b3IgcGdfc2hhZG93LCB2
aWEgc29tZQpyZWd1
bGFyIHNjcmlwdCwgYW5k
IHRoZW4gcmVwbGljYXRl
IHRoYXQuCgp0aGVuIHRo
ZSBmYWlsb3ZlciBw
cm9jZXNzIHdvdWxkIG5l
ZWQgdG8gZG8gImJlZ2lu
OyBkZWxldGUgZnJvbQpw
Z19zaGFkb3c7IGlu
c2VydCBpbnRvIHBnX3No
YWRvdyBzZWxlY3QgKiBm
cm9tCnJlcGxpY2F0ZWRf
cGdfc2hhZG93O2Nv
bW1pdDsiIHRvIGhhdmUg
YSBmcmVzaCBjb3B5Cgot
LS0tLS0tLS0tLQpIYW5u
dQoKCgpfX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fX19fX19f
X19fX19fX19fXwpTbG9u
eTEtZ2VuZXJhbCBt
YWlsaW5nIGxpc3QKU2xv
bnkxLWdlbmVyYWxAZ2Jv
cmcucG9zdGdyZXNxbC5v
cmcKaHR0cDovL2di
b3JnLnBvc3RncmVzcWwu
b3JnL21haWxtYW4vbGlz
dGluZm8vc2xvbnkxLWdl
bmVyYWwK

Christopher Browne

2006-02-13, 9:24 am

Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org> writes:
> =C3=9Chel kenal p=C3=A4eval, E, 2006-02-13 kell 08:34, kirjutas Christoph=
er
> Browne:
Kevin Gill:[color=darkred]

[color=darkred]
>
> perhaps it can be done by having a user table mirror pg_shadow, via some
> regular script, and then replicate that.
>
> then the failover process would need to do "begin; delete from
> pg_shadow; insert into pg_shadow select * from
> replicated_pg_shadow
;commit;" to have a fresh copy

Yes, I guess that would work.

A way to do this that would be, oh, call it "forcibly robust" would be
for each SYNC to check to see if pg_shadow had changed, and update the
user table correspondingly.

In practice, it's probably good enough to have some script that runs
every so often to look for changes. I know we don't modify pg_shadow
on our systems so often as every week; polling for it once a day would
likely suffice, for us...
-- =

"cbbrowne","@","ca.afilias.info"
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Jan Wieck

2006-02-15, 11:24 am

On 2/13/2006 9:57 AM, Christopher Browne wrote:

> A way to do this that would be, oh, call it "forcibly robust" would be
> for each SYNC to check to see if pg_shadow had changed, and update the
> user table correspondingly.
>
> In practice, it's probably good enough to have some script that runs
> every so often to look for changes. I know we don't modify pg_shadow
> on our systems so often as every week; polling for it once a day would
> likely suffice, for us...

A DB user per application user is IMHO broken design. That said, it
could be done the other way around. Have a regular, replicated table
with the same structure as pg_shadow. Use INSERT, UPDATE and DELETE on
that table and have triggers that duplicate those actions into
pg_shadow. The triggers need to be configured in slony as well so that
replicating the table will have the same effect through triggers on the
subscribers.

Keep in mind that this whole thing will only work if the entire instance
is for one single database. pg_shadow is a shared system catalog.
Another reason why it isn't really good to have a user per application user.

Jan

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Kevin Gill

2006-02-15, 1:24 pm

> On 2/13/2006 9:57 AM, Christopher Browne wrote:
>
>
> A DB user per application user is IMHO broken design. That said, it
> could be done the other way around. Have a regular, replicated table
> with the same structure as pg_shadow. Use INSERT, UPDATE and DELETE on
> that table and have triggers that duplicate those actions into
> pg_shadow. The triggers need to be configured in slony as well so that
> replicating the table will have the same effect through triggers on the
> subscribers.
>
> Keep in mind that this whole thing will only work if the entire instance
> is for one single database. pg_shadow is a shared system catalog.
> Another reason why it isn't really good to have a user per application
> user.
>

Thanks for the input all round. I wrote a script to dump the pg_shadow file
and to
convert the output to SQL. I ran this through pgsql on my new server. I
found
that inserting the encrypted password from the pg_shadow works on the new
server.
My users are not created frequently, so I expect to use this approach
manually once per month.

In defense of my dbuser per application user design, I made a decision to
use
the database security system as the application security system for two
reasons:

1. I prefer to have a DBA responsible for user management than to create
a new process and assign responsibility to another person who has no
appreciation
for the responsibility that comes with this. Clearly whether this approach
is
applicable in another context will depend on the competence of the
target organisation.

2. Ultimately, I have to connect my user to the database. To do this
securely
involves putting in place a middleware layer or hiding the real connection
to
the database in some arbitrary way. The db user=application user mapping is
at least transparent.

Clearly these are design decisions and perhaps personal choices.

Sponsored Links