[SQL] Copy user privileges
Hi All, I have dumped a database with no data and restored it with a new db name. I want to keep all the privileges but assign them to a new user. What is the best way of doing this? Is it to alter the system tables directly in which case which ones. TIA, Graham ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PostgreSQL - ERROR: Cannot cast type character varying to integer
thanks for the answer. i get it too as sum(int4(unidades::text)) Maybe somebody will need this answer too, so i want to add some info I get from other list: We can know wich CAST we have in our DB with this view: create view showcasts as select t.typname as source, t1.typname as target, p.proname as function, (select case when c.castcontext = 'e' then 'Must use Explicit Cast' else ( select case when c.castcontext = 'i' then 'Implicit cast for expressions and assignments' else 'Implicit cast only for assignments' end) end ) as casttype from pg_cast c, pg_type t, pg_type t1, pg_proc p where c.castsource = t.oid and c.casttarget = t1.oid and c.castfunc = p.oid; I hope this can be helpfull. again thanks to all. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: martes, 12 de julio de 2005 13:35 Para: Mauricio Fernandez CC: pgsql-sql@postgresql.org Asunto: Re: [SQL] PostgreSQL - ERROR: Cannot cast type character varying to integer Under PostgreSQL 7.3 it was necessary to use an intermediate cast to text when converting from varchar to int4. sum( unidades::text::int4 ) should do. Regards, Christoph ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL - ERROR: Cannot cast type character varying to
Under PostgreSQL 7.3 it was necessary to use an intermediate cast to text when converting from varchar to int4. sum( unidades::text::int4 ) should do. Regards, Christoph Mauricio Fernandez wrote: > > Hello I´m new in the list, and I want to learn a lot about postgresql, now > I´m working in a database and I have this problem: > > I have a table called historicoventas as follows (just the structure) > > > historicoventas > > ( > > codigopedidovarchar(20) > > codigohospital varchar(20) > > fecha varchar(15) > > productovarchar(20) > > unidadesvarchar(10) > > costo varchar(20) > > ) > > > And I want to create a view based on that table whit this instruction: > > > CREATE OR REPLACE VIEW vw_temphistoricoventas > > (Pedido, Fecha, Hospital, Producto, Unidades, Costo) > > AS > > SELECT codigoPedido , (substring( fecha from 1 for 4 ) || '-' > > || substring( fecha from 5 for 2 ) || '-' > > || substring( fecha from 7 for 2 ) ) AS fecha, > > codigoHospital , producto , sum( unidades::int4 ) AS unidades, sum( > > costo::float4 ) AS costo > > FROM historicoventas > > WHERE ((producto = '759928') OR (producto = '759936') OR > > (producto = '4002382') OR (producto = '4002580')) > > GROUP BY codigoPedido, fecha, codigoHospital, producto > > ORDER BY fecha; > But I always get this message: > > ERROR: Cannot cast type character varying to integer > My developer workstation is under win xp and postgresql 8.0 but in > production server (external web hosting partner) I have a Linux Red Hat with > kernel 2.4.21-27.0.2.ELsmp y PostgreSQL 7.3. > In local machine all castings between data types works fine but in server it > doesn´t. > convertions like sum(unidades::int4) works fine and reading the postgresql > 7.3 docs I can see this type of convertions are enabled by the system > Can somebody help me with this topic? > > Thanks a lot > > > > > Mauricio Fernández A. > > Ingeniero de Sistemas > > Universidad Autónoma de Manizales > > [EMAIL PROTECTED] > > movil: 607365968 > > > > P.D. Some historicoventas table rows can be: > > codPedido codHospital fecha productounidades > > costo > > 0001001299100058 20050103759936 1 > > 2375 > > 0001001299100058 20050103759928 1 > > 2375 > > 0001001300100078 20050103759936 1 > > 2375 > > 0001003044100086 20050614759936 1 > > 2275.25 > > 0001003128100062 20050623759936 2 > > 4550.5 > > 0001003129100069 20050623759936 2 > > 4550.5 > > 0001003131100019 20050623759928 5 > > 11376.25 > > 0001003131100019 20050623759936 3 > > 6825.75 > > 0001003132100075 20050623759936 4 > > 9101 > > 0001003133100106 20050623759936 2 > > 4550.5 > > 0001003134100031 20050623759936 2 > > 4550.5 > > 0001003135100037 20050623759936 3 > > 6825.75 > > 000100313619 20050623759936 5 > > 11376.25 > > 000100313719 20050623759936 1 > > 2275.25 > > 0001003138100024 20050623759936 4 > > 9101 > > > Name: winmail.dat >winmail.datType: application/ms-tnef > Encoding: base64 > > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Copy user privileges
"Graham Vickrage" <[EMAIL PROTECTED]> writes: > I want to keep all the privileges but assign them to a new user. > What is the best way of doing this? How about just renaming the old user to a new name? I don't think we have an ALTER command for that, but an UPDATE on pg_shadow would get the job done just as well. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Copy user privileges
On Tue, Jul 12, 2005 at 03:12:50PM -0400, Tom Lane wrote:
> "Graham Vickrage" <[EMAIL PROTECTED]> writes:
> > I want to keep all the privileges but assign them to a new user.
>
> > What is the best way of doing this?
>
> How about just renaming the old user to a new name?
>
> I don't think we have an ALTER command for that, but an UPDATE on
> pg_shadow would get the job done just as well.
What about ALTER USER RENAME TO?
test=# CREATE USER user1;
CREATE USER
test=# CREATE TABLE foo (x integer);
CREATE TABLE
test=# GRANT SELECT ON foo TO user1;
GRANT
test=# \z foo
Access privileges for database "test"
Schema | Name | Type | Access privileges
+--+---+--
public | foo | table | {postgres=arwdRxt/postgres,user1=r/postgres}
(1 row)
test=# ALTER USER user1 RENAME TO user2;
ALTER USER
test=# \z foo
Access privileges for database "test"
Schema | Name | Type | Access privileges
+--+---+--
public | foo | table | {postgres=arwdRxt/postgres,user2=r/postgres}
(1 row)
Renaming the user is only useful if you no longer need the old user.
If you need to keep the old user and copy its privileges, then consider
granting privileges to groups instead of to users -- then you could
just add the new user to a group.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] How to store and retrieve data with special characters (\n, \r, \f, \t) in the same format
How to store and retrieve data with special characters (\n, \r, \f, \t) in the same format, because while storing \f treats data in strange way. Example: ‘This is sample data \n \root\files.txt, \root\text.txt’ Sol: One way replaces \ with \\ Thanks Dinesh
[SQL] dynamically loaded functions
I've written some c-functions which depend on my code (gnova.so) as well as a third-party library of functions (oe_chem.so). Up until now, I've been preloading (in postgresql.conf) both .so's and it all works fine. To make it easier for my users to install my stuff, I'd like to avoid the preloading, but this would require giving 2 .so names in the CREATE FUNCTION statement (I imagine). So, what I would like to do is something like this: CREATE or REPLACE FUNCTION cansmiles(varchar) RETURNS varchar AS 'gnova,oe_chem', 'oe_cansmiles' LANGUAGE 'c' IMMUTABLE STRICT; but I get this: ERROR: could not access file "gnova,oe_chem": No such file or directory What I'm doing now: CREATE or REPLACE FUNCTION cansmiles(varchar) RETURNS varchar AS 'gnova', 'oe_cansmiles' LANGUAGE 'c' IMMUTABLE STRICT; requires preloading of oe_chem.so to work. Is there any way I can associate oe_cansmiles with 2 .so's without preloading? More info: oe_cansmiles is in gnova.so, but there are functions in gnova.so that are in oe_chem.so. TJ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] dynamically loaded functions
On Mon, Jul 11, 2005 at 08:16:17PM -0700, TJ O'Donnell wrote: > > CREATE or REPLACE FUNCTION cansmiles(varchar) RETURNS varchar >AS 'gnova', 'oe_cansmiles' LANGUAGE 'c' IMMUTABLE STRICT; > requires preloading of oe_chem.so to work. > > Is there any way I can associate oe_cansmiles with 2 .so's without > preloading? It sounds like you need to link gnova.so against the other shared objects so the runtime linker can find them. For examples, see the Makefiles used by contributed modules like dblink, xml2, and a few others that link against external libraries. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
