Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WultW-0000jt-Vd for pgsql-admin@arkaria.postgresql.org; Wed, 11 Jun 2014 17:01:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WultW-0007C4-Gg for pgsql-admin@arkaria.postgresql.org; Wed, 11 Jun 2014 17:01:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1WuVOF-0005WJ-Jd for pgsql-admin@postgresql.org; Tue, 10 Jun 2014 23:23:39 +0000 Received: from smtp3.ucdavis.edu ([128.120.32.129]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1WuVO5-0005n0-4O for pgsql-admin@postgresql.org; Tue, 10 Jun 2014 23:23:37 +0000 Received: from exhub.ex.ad3.ucdavis.edu (exhub.ex.ad3.ucdavis.edu [169.237.229.95]) by smtp3.ucdavis.edu (8.14.4/8.14.5/it-oel6-mimedefang-smtp-1.8) with ESMTP id s5ANNKbh001119 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=OK) for ; Tue, 10 Jun 2014 16:23:21 -0700 Received: from na01-bl2-obe.outbound.protection.outlook.com (207.46.163.206) by exhub.ex.ad3.ucdavis.edu (169.237.229.95) with Microsoft SMTP Server (TLS) id 14.3.174.1; Tue, 10 Jun 2014 16:23:02 -0700 Received: from BL2PR08MB113.namprd08.prod.outlook.com (10.242.196.11) by BL2PR08MB115.namprd08.prod.outlook.com (10.242.196.25) with Microsoft SMTP Server (TLS) id 15.0.954.9; Tue, 10 Jun 2014 23:23:00 +0000 Received: from BL2PR08MB113.namprd08.prod.outlook.com ([169.254.7.159]) by BL2PR08MB113.namprd08.prod.outlook.com ([169.254.7.159]) with mapi id 15.00.0954.000; Tue, 10 Jun 2014 23:23:00 +0000 From: William Wagman To: "pgsql-admin@postgresql.org" Subject: Question re privileges on extensions Thread-Topic: Question re privileges on extensions Thread-Index: Ac+FAuZM2HeVxeBlRG+2ecY9IsjsFA== Date: Tue, 10 Jun 2014 23:22:59 +0000 Message-ID: <32d712b7ef4d4606bd01d1ff17c0d090@BL2PR08MB113.namprd08.prod.outlook.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [169.237.11.138] x-microsoft-antispam: BL:0; ACTION:Default; RISK:Low; SCL:0; SPMLVL:NotSpam; PCL:0; RULEID: x-forefront-prvs: 0238AEEDB0 x-forefront-antispam-report: SFV:NSPM; SFS:(6009001)(428001)(189002)(199002)(19625215002)(15975445006)(19609705001)(80022001)(2656002)(15202345003)(99286001)(76576001)(99396002)(50986999)(64706001)(74502001)(79102001)(19300405004)(74662001)(83322001)(66066001)(31966008)(20776003)(19580405001)(19580395003)(101416001)(86362001)(74316001)(46102001)(92566001)(85852003)(76482001)(4396001)(33646001)(83072002)(551544002)(81342001)(21056001)(88552001)(75432001)(77982001)(54356999)(81542001)(16236675004)(87936001)(340984003)(24736002); DIR:OUT; SFP:; SCL:1; SRVR:BL2PR08MB115; H:BL2PR08MB113.namprd08.prod.outlook.com; FPR:; MLV:sfv; PTR:InfoNoRecords; MX:1; A:1; LANG:en; received-spf: None (: ad3.ucdavis.edu does not designate permitted sender hosts) authentication-results: spf=none (sender IP is ) smtp.mailfrom=szwagman@ad3.ucdavis.edu; Content-Type: multipart/alternative; boundary="_000_32d712b7ef4d4606bd01d1ff17c0d090BL2PR08MB113namprd08pro_" MIME-Version: 1.0 X-OriginatorOrg: ad3.ucdavis.edu X-Virus-Scanned: clamav-milter 0.98.1 at smtp3 X-Virus-Status: Clean X-Scanned-By: MIMEDefang 2.74 X-Pg-Spam-Score: -4.8 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_32d712b7ef4d4606bd01d1ff17c0d090BL2PR08MB113namprd08pro_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Greetings, I am very new to PostgreSQL and hopefully can pose my question in a way tha= t makes sense. We are an Oracle shop with a very small number of recently i= nstalled PostgreSQL databases. To monitor our Oracle databases we use the O= racle Enterprise Manager for which we recently installed a third party plug= -in to monitor PostgreSQL databases. My understanding is that in order to m= onitor queries pg_stat_statements must be configured in the database. We ar= e running PostgreSQL 9.2.8 on 64 bit Linux. I have taken the following step= s - 1) Installed the postgresql92-contrib-9.2.8-1PGDG.rhel6.x86_64 package= on our server. 2) In the postgresql.conf file we have the following entries - shared_preload_libraries =3D 'pg_stat_statements' pg_stat_statements.max =3D 10000 pg_stat_statements.track =3D all pg_stat_statements.track_utility =3D on 3) In the database executed the statement - CREATE EXTENSION IF NOT EXISTS pg_stat_statements; 4) Restarted the database. The above steps were done as the postgres use= r. 5) CREATE ROLE "oem_monitor" LOGIN ENCRYPTED PASSWORD '' NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT pdev_nolog_conn TO "oem_monitor"; GRANT pprd_nolog_conn TO "oem_monitor"; GRANT ptst_nolog_conn TO "oem_monitor"; GRANT connect ON DATABASE profiles TO "oem_monitor"; COMMENT ON ROLE "oem_monitor" IS 'OEM connect acct'; The OEM plug-in sees everything but the sql information and the console dis= plays a message indicating "pg_stat_statements status unknown, ensure data = collection has occurred." I suspect that privileges on the pg_stat_statemen= ts extension is the issue. The extension does exist - edus-test=3D# create extension "pg_stat_statements"; ERROR: extension "pg_stat_statements" already exists however the plug-in does not seem to see the shared_preload_libraries. Hopefully I have stated the question in a meaningful way such that someone = can assist me in resolving the issue. Thank you. Bill Wagman University of California Davis, IET 3820 Chiles Road Davis Ca. 95616 (530) 752-9706 wjwagman@ucdavis.edu --_000_32d712b7ef4d4606bd01d1ff17c0d090BL2PR08MB113namprd08pro_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Greetings,

 

I am very new to PostgreSQL and hopefully can pose m= y question in a way that makes sense. We are an Oracle shop with a very sma= ll number of recently installed PostgreSQL databases. To monitor our Oracle= databases we use the Oracle Enterprise Manager for which we recently installed a third party plug-in to monitor P= ostgreSQL databases. My understanding is that in order to monitor queries p= g_stat_statements must be configured in the database. We are running Postgr= eSQL 9.2.8 on 64 bit Linux. I have taken the following steps –

 

1)      Installed the postgresql92-contrib-9.2.8-1PGDG.rhel6.x86_64 package on our server.=

2)      In the po= stgresql.conf file we have the following entries –<= /p>

sha= red_preload_libraries =3D 'pg_stat_statements'
pg_stat_statements.max =3D 10000
pg_stat_statements.track =3D all

pg_= stat_statements.track_utility =3D on

3)    In= the database executed the statement –

CRE= ATE EXTENSION IF NOT EXISTS pg_stat_statements;

4)    Re= started the database. The above steps were done as the postgres user.<= /o:p>

5)    &nbs= p;CREATE ROLE "oem_monitor" LO= GIN ENCRYPTED PASSWORD '<password>'

NOSUPERUSER NOINH= ERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT pdev_nolog_conn TO "oem_monitor";
GRANT pprd_nolog_conn TO "oem_monitor";
GRANT ptst_nolog_conn TO "oem_monitor";
GRANT  connect ON DATABASE profiles TO "oem_monitor";
COMMENT ON ROLE "oem_monitor" IS 'OEM connect acct';

The OEM pl= ug-in sees everything but the sql information and the console displays a me= ssage indicating “pg_stat_statements status unknown, ensure data collection has occurred.” I suspect that privileges on the pg_s= tat_statements extension is the issue. The extension does exist –

 = ;

edus-test=3D# create extension "= pg_stat_statements";
ERROR:  extension "pg_stat_statements" already exists

 = ;

however the plug-in does not seem to see the shared_= preload_libraries.

 

Hopefully I have stated the question in a meaningful= way such that someone can assist me in resolving the issue. Thank you.

 

Bill Wagman

University of California Davis, IET

3820 Chiles Road

Davis Ca. 95616

(530) 752-9706

wjwagman@ucdavis.edu

 

--_000_32d712b7ef4d4606bd01d1ff17c0d090BL2PR08MB113namprd08pro_--