Обсуждение: grant all privileges on database
grant all privileges on database test to auser As far as I can tell this does nothing. Intuitively this command suggests that the auser would be able to access and modify the database test in anyway. It would also suggest that as new tables for the database auser would automatically have access to them. Instead it appears that we have to still individually grant access to tables on an individual basis. It seems to me that if it did pass these blanket privileges on it would be very useful and make administration a lot easier. While it is not hard to initially grant the individual access (i am looking for a script) it is a pain in the butt to maintain. Is this in fact how it works? While I am sure someone will reply that by forcing us to individually grant access on a table by table basis you are inherently forcing the administrators to maintain high security - which is a good thing. However i would then reply that in fact because most db admins are lazy this means that they won't even bother and just use the super user privilege instead.
On Wed, Dec 13, 2006 at 12:20:03PM -0800, developer@wexwarez.com wrote: > grant all privileges on database test to auser > > As far as I can tell this does nothing. Intuitively this command suggests > that the auser would be able to access and modify the database test in > anyway. It would also suggest that as new tables for the database auser > would automatically have access to them. It's not intuitive to me. Just like granting full access to the root of a filesystem does not grant you access to every file on it. Each directory and file needs to be done also. > Instead it appears that we have to still individually grant access to > tables on an individual basis. It seems to me that if it did pass these > blanket privileges on it would be very useful and make administration a > lot easier. While it is not hard to initially grant the individual access > (i am looking for a script) it is a pain in the butt to maintain. Is this > in fact how it works? I beleive the usual approach is you create a role and give permissions to tables to that role and then for each user that comes along, you assign that role to the user. That makes administration easier I think. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
developer@wexwarez.com wrote: > grant all privileges on database test to auser > > As far as I can tell this does nothing. Intuitively this command suggests > that the auser would be able to access and modify the database test in > anyway. It would also suggest that as new tables for the database auser > would automatically have access to them. > > It just grants all privileges for a database object to auser. You should check out the grant manager in Lightning Admin for Postgresql, it visually shows everything and makes it a bitter easier to see how it works. http://www.amsoftwaredesign.com/onlinehelp/pgla/grant_manager.htm In the screenshot the cells that are green are the ones that can be set for a particular object, and for a database it's just two. So the grant statement you used above did indeed do something, but only for a database object. Hope this helps. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration Only $9.99 through January 1st 2007
I am trying to loop through some data and then run insert some of the
resulting data into a new table. I can create the function but when I run
it i get the error:
ERROR: query "SELECT 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id , patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
, $7 , 'Other', 'ACCEPTED'" returned 11 columns
SQL state: 42601
Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement
I don't understand what the "returned 11 columns" means. I am inserting
10 and i counted and it all matches.
Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
is where the errors starts
CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
DECLARE
data RECORD;
paymentId int;
BEGIN
RAISE NOTICE 'Start loop...';
FOR data IN select slra.company_id, slra.create_date,
slra.service_line_responsibility_id,
slr.insurance_policy_responsible_party_id,
slr.patient_responsible_party_id,
slr.patient_contact_responsible_party_id,
insurer_service_center.insurer_network_id
from
.
.
.
.
.
.
.
.
.
LOOP
-- Now "data" has one record
EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
EXECUTE 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
data.company_id, data.create_date , data.patient_responsible_party_id ,
data.patient_contact_responsible_party_id , data.insurer_network_id,
'Other', 'ACCEPTED';
END LOOP;
RAISE NOTICE 'Done loop .';
RETURN 1;
END;
$$ LANGUAGE plpgsql;
select add_missing_slrps() ;
I assumed using the '%' symbol will automatically use the real value.
Like if it is a date it will handle it like a java prepared statement. Am
I wrong?
I have tried all kinds of things but I truly have no idea what the problem
is.
thanks
On Wednesday 20 December 2006 7:36 pm, developer@wexwarez.com wrote:
> I am trying to loop through some data and then run insert some of the
> resulting data into a new table. I can create the function but when I run
> it i get the error:
>
> ERROR: query "SELECT 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id , patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
> SQL state: 42601
> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement
>
> I don't understand what the "returned 11 columns" means. I am inserting
> 10 and i counted and it all matches.
>
>
> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
> is where the errors starts
>
> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
> DECLARE
> data RECORD;
> paymentId int;
> BEGIN
> RAISE NOTICE 'Start loop...';
>
> FOR data IN select slra.company_id, slra.create_date,
> slra.service_line_responsibility_id,
> slr.insurance_policy_responsible_party_id,
> slr.patient_responsible_party_id,
> slr.patient_contact_responsible_party_id,
> insurer_service_center.insurer_network_id
> from
> .
> .
> .
> .
> .
> .
> .
> .
> .
> LOOP
> -- Now "data" has one record
> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id patient_contact_responsible_party_id ,
^^^^ no comma
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
> data.company_id, data.create_date , data.patient_responsible_party_id ,
> data.patient_contact_responsible_party_id , data.insurer_network_id,
> 'Other', 'ACCEPTED';
>
>
> END LOOP;
>
> RAISE NOTICE 'Done loop .';
> RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
> select add_missing_slrps() ;
>
>
> I assumed using the '%' symbol will automatically use the real value.
> Like if it is a date it will handle it like a java prepared statement. Am
> I wrong?
>
> I have tried all kinds of things but I truly have no idea what the problem
> is.
> thanks
>
See inline comment, but I think you are missing a comma in your columns list.
--
Adrian Klaver
aklaver@comcast.net
developer@wexwarez.com wrote:
> I am trying to loop through some data and then run insert some of the
> resulting data into a new table. I can create the function but when I run
> it i get the error:
>
> ERROR: query "SELECT 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id , patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
> SQL state: 42601
> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute statement
>
> I don't understand what the "returned 11 columns" means. I am inserting
> 10 and i counted and it all matches.
>
>
> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
> is where the errors starts
>
> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
> DECLARE
> data RECORD;
> paymentId int;
> BEGIN
> RAISE NOTICE 'Start loop...';
>
> FOR data IN select slra.company_id, slra.create_date,
> slra.service_line_responsibility_id,
> slr.insurance_policy_responsible_party_id,
> slr.patient_responsible_party_id,
> slr.patient_contact_responsible_party_id,
> insurer_service_center.insurer_network_id
> from
> .
> .
> .
> .
> .
> .
> .
> .
> .
> LOOP
> -- Now "data" has one record
> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
> data.company_id, data.create_date , data.patient_responsible_party_id ,
> data.patient_contact_responsible_party_id , data.insurer_network_id,
> 'Other', 'ACCEPTED';
>
>
> END LOOP;
>
> RAISE NOTICE 'Done loop .';
> RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
> select add_missing_slrps() ;
>
>
> I assumed using the '%' symbol will automatically use the real value.
> Like if it is a date it will handle it like a java prepared statement. Am
> I wrong?
>
I believe you are wrong. the EXECUTE is being given 11 columns, it
expects 1. I think you need to form your execute query like;
EXECUTE 'INSERT INTO payment (
id,amount,accepted_date, company_id , date ,
patient_responsible_party_id patient_contact_responsible_party_id ,
insurer_network_responsible_party_id, type, status)
values (' || quote_ident(paymentId) || ',' || ...
Something of that fashion.
> I have tried all kinds of things but I truly have no idea what the problem
> is.
> thanks
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>
>
> developer@wexwarez.com wrote:
>> I am trying to loop through some data and then run insert some of the
>> resulting data into a new table. I can create the function but when I
>> run
>> it i get the error:
>>
>> ERROR: query "SELECT 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id , patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
>> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
>> SQL state: 42601
>> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute
>> statement
>>
>> I don't understand what the "returned 11 columns" means. I am inserting
>> 10 and i counted and it all matches.
>>
>>
>> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
>> is where the errors starts
>>
>> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
>> DECLARE
>> data RECORD;
>> paymentId int;
>> BEGIN
>> RAISE NOTICE 'Start loop...';
>>
>> FOR data IN select slra.company_id, slra.create_date,
>> slra.service_line_responsibility_id,
>> slr.insurance_policy_responsible_party_id,
>> slr.patient_responsible_party_id,
>> slr.patient_contact_responsible_party_id,
>> insurer_service_center.insurer_network_id
>> from
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> LOOP
>> -- Now "data" has one record
>> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>>
>> EXECUTE 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
>> data.company_id, data.create_date , data.patient_responsible_party_id ,
>> data.patient_contact_responsible_party_id , data.insurer_network_id,
>> 'Other', 'ACCEPTED';
>>
>>
>> END LOOP;
>>
>> RAISE NOTICE 'Done loop .';
>> RETURN 1;
>> END;
>> $$ LANGUAGE plpgsql;
>> select add_missing_slrps() ;
>>
>>
>> I assumed using the '%' symbol will automatically use the real value.
>> Like if it is a date it will handle it like a java prepared statement.
>> Am
>> I wrong?
>>
> I believe you are wrong. the EXECUTE is being given 11 columns, it
> expects 1. I think you need to form your execute query like;
>
> EXECUTE 'INSERT INTO payment (
> id,amount,accepted_date, company_id , date ,
> patient_responsible_party_id patient_contact_responsible_party_id ,
> insurer_network_responsible_party_id, type, status)
> values (' || quote_ident(paymentId) || ',' || ...
>
> Something of that fashion.
>
>
>> I have tried all kinds of things but I truly have no idea what the
>> problem
>> is.
>> thanks
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org/
>>
>>
>>
>
>
Thanks for replying. So the '%' symbol can not be used with an EXECUTE
'INSERT statement. I thought this was exactly what it was for?
> On Wednesday 20 December 2006 7:36 pm, developer@wexwarez.com wrote:
>> I am trying to loop through some data and then run insert some of the
>> resulting data into a new table. I can create the function but when I
>> run
>> it i get the error:
>>
>> ERROR: query "SELECT 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id , patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', $1 , 0.0, $2 , $3 , $4 , $5 , $6
>> , $7 , 'Other', 'ACCEPTED'" returned 11 columns
>> SQL state: 42601
>> Context: PL/pgSQL function "add_missing_slrps" line 20 at execute
>> statement
>>
>> I don't understand what the "returned 11 columns" means. I am inserting
>> 10 and i counted and it all matches.
>>
>>
>> Here is my function i deleted some of the sql. The EXECUTE 'INSERT INTO
>> is where the errors starts
>>
>> CREATE OR REPLACE FUNCTION add_missing_slrps() RETURNS integer AS $$
>> DECLARE
>> data RECORD;
>> paymentId int;
>> BEGIN
>> RAISE NOTICE 'Start loop...';
>>
>> FOR data IN select slra.company_id, slra.create_date,
>> slra.service_line_responsibility_id,
>> slr.insurance_policy_responsible_party_id,
>> slr.patient_responsible_party_id,
>> slr.patient_contact_responsible_party_id,
>> insurer_service_center.insurer_network_id
>> from
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> .
>> LOOP
>> -- Now "data" has one record
>> EXECUTE 'select nextval(''seq_payment'') ' into paymentId;
>>
>> EXECUTE 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id patient_contact_responsible_party_id ,
> ^^^^ no comma
>
>> insurer_network_responsible_party_id, type, status)
>> values (%,%,%,%,%,%,%,%,%,%)', paymentId, 0.0, data.create_date ,
>> data.company_id, data.create_date , data.patient_responsible_party_id ,
>> data.patient_contact_responsible_party_id , data.insurer_network_id,
>> 'Other', 'ACCEPTED';
>>
>>
>> END LOOP;
>>
>> RAISE NOTICE 'Done loop .';
>> RETURN 1;
>> END;
>> $$ LANGUAGE plpgsql;
>> select add_missing_slrps() ;
>>
>>
>> I assumed using the '%' symbol will automatically use the real value.
>> Like if it is a date it will handle it like a java prepared statement.
>> Am
>> I wrong?
>>
>> I have tried all kinds of things but I truly have no idea what the
>> problem
>> is.
>> thanks
>>
>
> See inline comment, but I think you are missing a comma in your columns
> list.
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
Adrian- It must have been copied over wrong or something because I just
checked it over and over and that comma is there and the result is the
same.
developer@wexwarez.com wrote:
>> I believe you are wrong. the EXECUTE is being given 11 columns, it
>> expects 1. I think you need to form your execute query like;
>>
>> EXECUTE 'INSERT INTO payment (
>> id,amount,accepted_date, company_id , date ,
>> patient_responsible_party_id patient_contact_responsible_party_id ,
>> insurer_network_responsible_party_id, type, status)
>> values (' || quote_ident(paymentId) || ',' || ...
>>
>> Something of that fashion.
> Thanks for replying. So the '%' symbol can not be used with an EXECUTE
> 'INSERT statement. I thought this was exactly what it was for?
No - the RAISE statement takes that format, nothing else.
However, you only need the EXECUTE for statements that you need planned
every time they are called, e.g. can have their table-name changed. You
should just be able to write:
INSERT INTO ... VALUES (paymentId, 0.0, data.create_date ...)
P.S. - you can remove much of a message when you reply, that way people
can quickly follow the new parts of a message-thread.
--
Richard Huxton
Archonet Ltd
> developer@wexwarez.com wrote:
>>> I believe you are wrong. the EXECUTE is being given 11 columns, it
>>> expects 1. I think you need to form your execute query like;
>>>
>>> EXECUTE 'INSERT INTO payment (
>>> id,amount,accepted_date, company_id , date ,
>>> patient_responsible_party_id patient_contact_responsible_party_id ,
>>> insurer_network_responsible_party_id, type, status)
>>> values (' || quote_ident(paymentId) || ',' || ...
>>>
>>> Something of that fashion.
>
>> Thanks for replying. So the '%' symbol can not be used with an EXECUTE
>> 'INSERT statement. I thought this was exactly what it was for?
>
> No - the RAISE statement takes that format, nothing else.
>
> However, you only need the EXECUTE for statements that you need planned
> every time they are called, e.g. can have their table-name changed. You
> should just be able to write:
> INSERT INTO ... VALUES (paymentId, 0.0, data.create_date ...)
>
> P.S. - you can remove much of a message when you reply, that way people
> can quickly follow the new parts of a message-thread.
>
> --
> Richard Huxton
> Archonet Ltd
>
Thanks Richard that worked for me.