pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase User Quota
3+ messages / 2 participants
[nested] [flat]
* Database User Quota
@ 2014-08-25 11:21 Horst Düster <horst.duester@sourcepole.ch>
0 siblings, 1 reply; 3+ messages in thread
From: Horst Düster @ 2014-08-25 11:21 UTC (permalink / raw)
To: pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi
I'm looking for a method to set quotas per user in a Postgresql Database
Cluster. After searching around I found the trigger called check_quota.
But this trigger has to be added to every table stored in a database.
What I'm looking for is a cluster wide quotation based on user or role.
Any help or hint are very appreciated.
All the best
Horst Düster
- --
Dr. Horst Düster
Managing Director
Sourcepole AG - Linux & Open Source Solutions
Weberstrasse 5, 8004 Zürich, Switzerland
Tel: +41 (0)44 440 77 11
Direkt: +41 (0)44 515 67 70
horst.duester@sourcepole.ch http://www.sourcepole.ch
www.sourcepole.ch/kurse - Das Kursangebot Herbst 2014
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
iQEcBAEBAgAGBQJT+xwoAAoJENxaj+NbOI6SFoAH/i5GYakf76E9mJwdtMb/5NuF
BsWuXktudYTCHDeFCpIqTnwK8wRWlcxN/aBQI0RQWHse5wSEAljctE26EkBPkL/c
ZYAIi2t2p7nmOk190kYqp8i+65PLow8UdOVA/sTp/xf6LUr7XKWPA1feTDCesBev
m5a4cOKZyqOMx+Ax2/JYkwT9s9TRZD8CB8A1dmQIvPzihL7g1YxXu7lfne6O4TTK
EMuvM5xx/NZblaANJVPATI+jVGv50Vd2YvBwewD58YMZOA5gHcRF4ntMvNab0Xzs
Py26Li76xT7vo1E5dI2dKYkLozwpkQOnmH5eIJ4nsabXVSUp7abAd6qH8WvM9AI=
=qhey
-----END PGP SIGNATURE-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Database User Quota
@ 2014-08-25 11:34 hubert depesz lubaczewski <depesz@gmail.com>
parent: Horst Düster <horst.duester@sourcepole.ch>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2014-08-25 11:34 UTC (permalink / raw)
To: Horst Düster <horst.duester@sourcepole.ch>; +Cc: pgsql-admin
You could add script that would scan databases, and summarize size of
objects per owner role.
More or less something like:
for a in $( psql -c "select datname from pg_database where datallowconn"
-qAtX )
do
psql -d $a -c "select u.usename, pg_table_size(c.oid) from pg_class c
join pg_user u on c.relowner = u.usesysid where c.relkind in ('r', 'i')"
-qAtX -F,
done | \
awk -F, '{s[$1]+=$2} END{for (i in s) {printf "%-30s : %20d\n", i,
s[i]}}' | \
sort -nk2
And then, run it from cron, add some logic to alert user/admin or simply
block user access.
depesz
On Mon, Aug 25, 2014 at 1:21 PM, Horst Düster <horst.duester@sourcepole.ch>
wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi
>
> I'm looking for a method to set quotas per user in a Postgresql Database
> Cluster. After searching around I found the trigger called check_quota.
> But this trigger has to be added to every table stored in a database.
> What I'm looking for is a cluster wide quotation based on user or role.
>
> Any help or hint are very appreciated.
>
> All the best
> Horst Düster
>
>
>
> - --
> Dr. Horst Düster
> Managing Director
> Sourcepole AG - Linux & Open Source Solutions
> Weberstrasse 5, 8004 Zürich, Switzerland
> Tel: +41 (0)44 440 77 11
> Direkt: +41 (0)44 515 67 70
> horst.duester@sourcepole.ch http://www.sourcepole.ch
>
> www.sourcepole.ch/kurse - Das Kursangebot Herbst 2014
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1
>
> iQEcBAEBAgAGBQJT+xwoAAoJENxaj+NbOI6SFoAH/i5GYakf76E9mJwdtMb/5NuF
> BsWuXktudYTCHDeFCpIqTnwK8wRWlcxN/aBQI0RQWHse5wSEAljctE26EkBPkL/c
> ZYAIi2t2p7nmOk190kYqp8i+65PLow8UdOVA/sTp/xf6LUr7XKWPA1feTDCesBev
> m5a4cOKZyqOMx+Ax2/JYkwT9s9TRZD8CB8A1dmQIvPzihL7g1YxXu7lfne6O4TTK
> EMuvM5xx/NZblaANJVPATI+jVGv50Vd2YvBwewD58YMZOA5gHcRF4ntMvNab0Xzs
> Py26Li76xT7vo1E5dI2dKYkLozwpkQOnmH5eIJ4nsabXVSUp7abAd6qH8WvM9AI=
> =qhey
> -----END PGP SIGNATURE-----
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Database User Quota
@ 2014-08-25 12:36 Horst Düster <horst.duester@sourcepole.ch>
parent: hubert depesz lubaczewski <depesz@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Horst Düster @ 2014-08-25 12:36 UTC (permalink / raw)
To: hubert depesz lubaczewski <depesz@gmail.com>; +Cc: pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi depesz
Thanks a lot for your quick response. I'll have a look on your solution
soon.
All the best
Horst
Am 25/08/14 um 13:34 schrieb hubert depesz lubaczewski:
> You could add script that would scan databases, and summarize size of
> objects per owner role.
>
> More or less something like:
>
> for a in $( psql -c "select datname from pg_database where datallowconn"
> -qAtX )
> do
> psql -d $a -c "select u.usename, pg_table_size(c.oid) from pg_class c
> join pg_user u on c.relowner = u.usesysid where c.relkind in ('r', 'i')"
> -qAtX -F,
> done | \
> awk -F, '{s[$1]+=$2} END{for (i in s) {printf "%-30s : %20d\n", i,
> s[i]}}' | \
> sort -nk2
>
> And then, run it from cron, add some logic to alert user/admin or simply
> block user access.
>
> depesz
>
>
>
> On Mon, Aug 25, 2014 at 1:21 PM, Horst Düster
<horst.duester@sourcepole.ch>
> wrote:
>
>>
> Hi
>
> I'm looking for a method to set quotas per user in a Postgresql Database
> Cluster. After searching around I found the trigger called check_quota.
> But this trigger has to be added to every table stored in a database.
> What I'm looking for is a cluster wide quotation based on user or role.
>
> Any help or hint are very appreciated.
>
> All the best
> Horst Düster
>
>
>
>>
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
- --
Dr. Horst Düster
Managing Director
Sourcepole AG - Linux & Open Source Solutions
Weberstrasse 5, 8004 Zürich, Switzerland
Tel: +41 (0)44 440 77 11
Direkt: +41 (0)44 515 67 70
horst.duester@sourcepole.ch http://www.sourcepole.ch
www.sourcepole.ch/kurse - Das Kursangebot Herbst 2014
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
iQEcBAEBAgAGBQJT+y3aAAoJENxaj+NbOI6S8dAH/iaVfBFqAnTjP3WyF7/8CIXB
0Ju7uTG35tN5hEXTbUJYVO0Ly5dd7nEBDaGSMZkMMHyPGOM92zeKJif9sX/xrPBu
0lNnUjq/crp5pvkh2u2BtvNfHawzbEDapCzoCkI4KlkOoyyYzGUO3rMpWprY/H/j
q1ZJrd6saHIrOfRRwuoq8Sg1fUdcCcTpcBBT4s9mkhfRCrUAwPhcv2n6k458kO0z
glJiWdMq+KaD6GyZawBxc4XLJJEvn0oiyvhZ7OkroboKPO2bQcwb7538S8Hl/8IP
CdDRbAZWdrrXdmmeB5k7Alt+DOtxDNjQdr4+g9NzH1GfF50yWEFagGp8xEsslJQ=
=aNNI
-----END PGP SIGNATURE-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-08-25 12:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-25 11:21 Database User Quota Horst Düster <horst.duester@sourcepole.ch>
2014-08-25 11:34 ` hubert depesz lubaczewski <depesz@gmail.com>
2014-08-25 12:36 ` Horst Düster <horst.duester@sourcepole.ch>
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