pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql question
6+ messages / 5 participants
[nested] [flat]
* postgresql question
@ 2011-01-21 21:19 Dennis Hoffman <dennis.hoffman@seagate.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dennis Hoffman @ 2011-01-21 21:19 UTC (permalink / raw)
To: pgsql-admin
Hello:
I am running Postgres version 7.4.13 on a Linux CentOS box with kernel
version 2.6.9-67.0.7
I have a process running under a user ( "merlin" ) that needs access to the
data base ( to calculate the DB size ). I have added the "merlin" user as a
member of the postgres group, and changed the file group permissions ( I.E.
/var/lib/pgsql/data/base ) to 750 from 700. this allows the "merlin" user's
process to access the DB files. However - and this is the problem - after
changing the file permissions, the Postmaster won't start. The indication
is that it does'nt know where to find the database system data. I provide a
path ( using the -D option ), and then the indication is that it can not
read the permission of the directory /var/lib/pgsql/data/base/171142. If I
do nothing other than remove the file permissions ( change back to 700 from
750 ), the Postmaster starts just as it should. Why does the Postmaster not
start with the group permissions changed? Is this a bug? What can I do?
TIA
Dennis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgresql question
@ 2011-01-21 21:39 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Dennis Hoffman <dennis.hoffman@seagate.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2011-01-21 21:39 UTC (permalink / raw)
To: pgsql-admin; Dennis Hoffman <dennis.hoffman@seagate.com>
Dennis Hoffman <dennis.hoffman@seagate.com> wrote:
> If I do nothing other than remove the file permissions ( change
> back to 700 from 750 ), the Postmaster starts just as it should.
> Why does the Postmaster not start with the group permissions
> changed?
That's intentional, as a security measure.
> Is this a bug?
It is considered a feature.
> What can I do?
I'd be tempted to write a script which was owned by root which
calculated the size as desired, and grant sudo rights to merlin to
execute that particular script as the user with rights.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgresql question
@ 2011-01-21 22:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2011-01-21 22:03 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin@postgresql.org, "Dennis Hoffman" <dennis.hoffman@seagate.com>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Dennis Hoffman <dennis.hoffman@seagate.com> wrote:
>> What can I do?
> I'd be tempted to write a script which was owned by root which
> calculated the size as desired, and grant sudo rights to merlin to
> execute that particular script as the user with rights.
Seems like a postgres-owned script would be safer. But actually
I'd suggest using contrib/dbsize rather than doing any of this.
Those functions got integrated into the core eventually, but in
7.4 they're in contrib.
(Ob-comment-you-do-realize-how-obsolete-7.4-is-dont-you)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* PostgreSQL question
@ 2011-09-26 10:36 Mridul Mathew <mridulmathew@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mridul Mathew @ 2011-09-26 10:36 UTC (permalink / raw)
To: pgsql-admin
Hello,
I wanted to take a moment here and reach out to the community with a
question. How does postgreSQL handle schema switching in the database? I
have a test scenario.
##################
vpn2=> show search_path;
search_path
-------------
mridul
(1 row)
CREATE OR REPLACE PACKAGE mridul.TEST_PKG
IS
PROCEDURE execute_for_all_schema();
end TEST_PKG;
CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG
IS
PROCEDURE execute_for_all_schema() IS
CURSOR c_schema_names IS
SELECT name
FROM mridul.schema_config
WHERE status = 'ACTIVE';
BEGIN
FOR dat_schema_names IN c_schema_names LOOP
----- EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '||
dat_schema_names.name;
EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name;
EXECUTE IMMEDIATE 'insert into A values (1)';
commit;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
end TEST_PKG;
vpn2=> select * from mridul.schema_config;
id | name | description | status | last_device_sync_date
----+-------+-------------+--------+-----------------------
1 | TEST | TESTING | ACTIVE | 22-SEP-11 18:28:11
6 | TEST1 | TESTING5 | ACTIVE | 22-SEP-11 11:46:09
2 | TEST2 | TESTING2 | ACTIVE | 23-SEP-11 11:54:35
(3 rows)
vpn2=> show search_path;
search_path
-------------
mridul
(1 row)
vpn2=> select count(*) from test.a;
count
-------
0
(1 row)
vpn2=> select count(*) from test1.a;
count
-------
0
(1 row)
vpn2=> select count(*) from test2.a;
count
-------
0
(1 row)
vpn2=> exec mridul.TEST_PKG.execute_for_all_schema;
EDB-SPL Procedure successfully completed
vpn2=> select count(*) from test.a;
count
-------
1
(1 row)
vpn2=> select count(*) from test1.a;
count
-------
1
(1 row)
vpn2=> select count(*) from test2.a;
count
-------
1
(1 row)
If I convert the 'insert into A ...' part from a dynamic dml to a normal
insert, and then execute the procedure, there are 3 rows inserted into
test.a and none go into test1.a nor test2.a. However, if I echo the
search_path after executing the proc, it displays the last schema's name
(meaning, it has switched the schemas, but the insert has gone into only the
first schema).
Thanks in advance!
-Mridul.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL question
@ 2011-09-26 18:44 Guillaume Lelarge <guillaume@lelarge.info>
parent: Mridul Mathew <mridulmathew@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2011-09-26 18:44 UTC (permalink / raw)
To: Mridul Mathew <mridulmathew@gmail.com>; +Cc: pgsql-admin
On Mon, 2011-09-26 at 16:06 +0530, Mridul Mathew wrote:
> Hello,
>
> I wanted to take a moment here and reach out to the community with a
> question. How does postgreSQL handle schema switching in the database? I
> have a test scenario.
>
> ##################
>
> vpn2=> show search_path;
> search_path
> -------------
> mridul
> (1 row)
>
> CREATE OR REPLACE PACKAGE mridul.TEST_PKG
> IS
> PROCEDURE execute_for_all_schema();
> end TEST_PKG;
>
> CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG
> IS
>
> PROCEDURE execute_for_all_schema() IS
>
> CURSOR c_schema_names IS
> SELECT name
> FROM mridul.schema_config
> WHERE status = 'ACTIVE';
>
> BEGIN
>
> FOR dat_schema_names IN c_schema_names LOOP
> ----- EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '||
> dat_schema_names.name;
> EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name;
> EXECUTE IMMEDIATE 'insert into A values (1)';
> commit;
> END LOOP;
> EXCEPTION
> WHEN OTHERS THEN
> RAISE;
>
> END;
> end TEST_PKG;
>
> vpn2=> select * from mridul.schema_config;
> id | name | description | status | last_device_sync_date
> ----+-------+-------------+--------+-----------------------
> 1 | TEST | TESTING | ACTIVE | 22-SEP-11 18:28:11
> 6 | TEST1 | TESTING5 | ACTIVE | 22-SEP-11 11:46:09
> 2 | TEST2 | TESTING2 | ACTIVE | 23-SEP-11 11:54:35
> (3 rows)
>
> vpn2=> show search_path;
> search_path
> -------------
> mridul
> (1 row)
>
> vpn2=> select count(*) from test.a;
> count
> -------
> 0
> (1 row)
>
>
> vpn2=> select count(*) from test1.a;
> count
> -------
> 0
> (1 row)
>
>
> vpn2=> select count(*) from test2.a;
> count
> -------
> 0
> (1 row)
>
> vpn2=> exec mridul.TEST_PKG.execute_for_all_schema;
>
> EDB-SPL Procedure successfully completed
> vpn2=> select count(*) from test.a;
> count
> -------
> 1
> (1 row)
>
>
> vpn2=> select count(*) from test1.a;
> count
> -------
> 1
> (1 row)
>
>
> vpn2=> select count(*) from test2.a;
> count
> -------
> 1
> (1 row)
>
> If I convert the 'insert into A ...' part from a dynamic dml to a normal
> insert, and then execute the procedure, there are 3 rows inserted into
> test.a and none go into test1.a nor test2.a. However, if I echo the
> search_path after executing the proc, it displays the last schema's name
> (meaning, it has switched the schemas, but the insert has gone into only the
> first schema).
>
This isn't a PostgreSQL question, this is an EnterpriseDB one. Packages
are not part of the community PostgreSQL distribution. "EXECUTE
IMMEDIATE" is also not part of the community PostgreSQL distribution.
So I guess you need to ask them.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL question
@ 2011-09-27 06:34 Mridul Mathew <mridulmathew@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 6+ messages in thread
From: Mridul Mathew @ 2011-09-27 06:34 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-admin
Okay, thanks!
-Mridul.
On Tue, Sep 27, 2011 at 12:14 AM, Guillaume Lelarge
<guillaume@lelarge.info>wrote:
> On Mon, 2011-09-26 at 16:06 +0530, Mridul Mathew wrote:
> > Hello,
> >
> > I wanted to take a moment here and reach out to the community with a
> > question. How does postgreSQL handle schema switching in the database? I
> > have a test scenario.
> >
> > ##################
> >
> > vpn2=> show search_path;
> > search_path
> > -------------
> > mridul
> > (1 row)
> >
> > CREATE OR REPLACE PACKAGE mridul.TEST_PKG
> > IS
> > PROCEDURE execute_for_all_schema();
> > end TEST_PKG;
> >
> > CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG
> > IS
> >
> > PROCEDURE execute_for_all_schema() IS
> >
> > CURSOR c_schema_names IS
> > SELECT name
> > FROM mridul.schema_config
> > WHERE status = 'ACTIVE';
> >
> > BEGIN
> >
> > FOR dat_schema_names IN c_schema_names LOOP
> > ----- EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '||
> > dat_schema_names.name;
> > EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name;
> > EXECUTE IMMEDIATE 'insert into A values (1)';
> > commit;
> > END LOOP;
> > EXCEPTION
> > WHEN OTHERS THEN
> > RAISE;
> >
> > END;
> > end TEST_PKG;
> >
> > vpn2=> select * from mridul.schema_config;
> > id | name | description | status | last_device_sync_date
> > ----+-------+-------------+--------+-----------------------
> > 1 | TEST | TESTING | ACTIVE | 22-SEP-11 18:28:11
> > 6 | TEST1 | TESTING5 | ACTIVE | 22-SEP-11 11:46:09
> > 2 | TEST2 | TESTING2 | ACTIVE | 23-SEP-11 11:54:35
> > (3 rows)
> >
> > vpn2=> show search_path;
> > search_path
> > -------------
> > mridul
> > (1 row)
> >
> > vpn2=> select count(*) from test.a;
> > count
> > -------
> > 0
> > (1 row)
> >
> >
> > vpn2=> select count(*) from test1.a;
> > count
> > -------
> > 0
> > (1 row)
> >
> >
> > vpn2=> select count(*) from test2.a;
> > count
> > -------
> > 0
> > (1 row)
> >
> > vpn2=> exec mridul.TEST_PKG.execute_for_all_schema;
> >
> > EDB-SPL Procedure successfully completed
> > vpn2=> select count(*) from test.a;
> > count
> > -------
> > 1
> > (1 row)
> >
> >
> > vpn2=> select count(*) from test1.a;
> > count
> > -------
> > 1
> > (1 row)
> >
> >
> > vpn2=> select count(*) from test2.a;
> > count
> > -------
> > 1
> > (1 row)
> >
> > If I convert the 'insert into A ...' part from a dynamic dml to a normal
> > insert, and then execute the procedure, there are 3 rows inserted into
> > test.a and none go into test1.a nor test2.a. However, if I echo the
> > search_path after executing the proc, it displays the last schema's name
> > (meaning, it has switched the schemas, but the insert has gone into only
> the
> > first schema).
> >
>
> This isn't a PostgreSQL question, this is an EnterpriseDB one. Packages
> are not part of the community PostgreSQL distribution. "EXECUTE
> IMMEDIATE" is also not part of the community PostgreSQL distribution.
>
> So I guess you need to ask them.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-09-27 06:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-21 21:19 postgresql question Dennis Hoffman <dennis.hoffman@seagate.com>
2011-01-21 21:39 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-21 22:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-26 10:36 PostgreSQL question Mridul Mathew <mridulmathew@gmail.com>
2011-09-26 18:44 ` Re: PostgreSQL question Guillaume Lelarge <guillaume@lelarge.info>
2011-09-27 06:34 ` Re: PostgreSQL question Mridul Mathew <mridulmathew@gmail.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