grant all privileges on database

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/

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

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.

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/

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.