Thread: Database User Quota
-----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-----
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.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
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
-----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-----