[GENERAL] Database level read only user
Hi,
Today a client ask me for help to create a read only user for a postgresql 8.2
database, i ended up reading, copying and pasting and finally creating a
function i'm sharing with the list:
CREATE OR REPLACE FUNCTION db_grant(usuario text, privilegio text)
RETURNS INTEGER AS $$
DECLARE
db RECORD;
BEGIN
FOR db IN
SELECT nspname
FROM pg_namespace
WHERE has_schema_privilege(nspname, 'USAGE') and nspname !~ '^pg_'
LOOP
EXECUTE 'GRANT USAGE ON schema ' || db.nspname || ' to ' || usuario;
END LOOP;
FOR db IN
SELECT *
FROM pg_tables
WHERE tableowner = current_user
LOOP
EXECUTE 'GRANT ' || privilegio || ' ON ' || db.schemaname || '.' ||
db.tablename || ' TO ' || usuario;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION db_grant (text, text)
IS 'Give permissions at database level, Hans Poo, Santiago Julio de 2011';
-- This CREATE must be run by the owner of the database and will be created in
the default schema usually public.
-- This is the call:
select db_grant('usuario1','select');
Bye
Hans
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected protocol character='j' during authentication..
Eric, The posts in this forum must be done in english. Entiendo que los posts a este foro deben ser en inglés. May be you already did some of the next: - Test if it works connecting with same credentials from the server itself and other machine. - Raise log/debug level on server and examine the server log file. - Run an sniffer like wireshark somewhere in between the two machines to sniff network traffic, looking for something suspect, don't use ssl connections for this. - Finally may be something of character encoding in the windows client machine, try a fresh linux machine and use psql from command line. A lo mejor ya hiciste algo como lo siguiente: Prueba si funciona conectando con las mismas credenciales desde el mismo servidor u otra máquina conectada directamente al servidor. Incrementa el nivel de debug/log del servidor y examina el log file (archivo postgresql.conf). Ejecuta un sniffer como wireshark en algún lugar entre las dos máquinas para analizar el tráfico de red, para esto no debes usar ssl en la conexión postgres. Finalmente, puede ser algo con la codificación de caracteres en el cliente windows, intenta con un live CD de ubuntu y usa psql desde la línea de comandos. Bye Hans Hans Poo, Welinux S.A. Bombero Ossa #1010, oficina 800, +56-2-3729770, Movil: +56-9-3199305 Santiago, Chile - Mensaje original - De: ego...@adgsystems.com.do Para: pgsql-general@postgresql.org Enviados: Jueves, 19 de Mayo 2011 16:45:01 Asunto: [GENERAL] Unexpected protocol character='j' during authentication.. Saludos al foro. Este error que describe el asunto del post, lo he buscado y no he encontrado una solucion. Describo el ambiente de mi servidor BD. PostgreSql 8.3 Windows 2003 Server Tengo una base de datos a la que se conectan varios vendedores y sincronizan los datos de ventas. Desde hace varios días hemos estado buscando alguna solucion. Re-instalamos postgres, cambiamos la ubicacion de red del servidor, redireccionamos el puerto a otra maquina, entre otras pruebas mas. Cuando cambiamos de router o reseteamos el router, permite la conexion por un periodo de tiempo breve. Luego pasa a dar este molestoso error de conexion: Unexpected protocol character='Ã' during authentication... Por favor, si son tan amables de ayudarme, se los agradeceria mucho. Gracias! Eris J. Gómez ADGSystems, EIRL Gestor de Proyectos Tel. 809.241.7309 ego...@adgsystems.com.do http://www.adgsystems.com.do -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Script perl para eliminar constraints duplicados
Hi,
Sometimes i end up with some duplicated constraints definitions in my database,
i've noticed this when i reverse engineer databases, and see many links between
two tables.
I prepared a perl script that read an schema on standard input, and prints on
standard output some drop constraints for duplicated definitions, if you like
you can then execute them against your database:
For example if you save it as drop-dup-constraints.pl, then you can check your
database with:
pg_dump -Ox -s mydatabase | drop-dup-constraints.pl
#!/usr/bin/perl
use strict;
use warnings;
## Elimina los constraints sobre el mismo campo y la misma tabla
my ($table, $constName, $field);
my %tuplas;
my @lines = ();
while () {
next if /--/;
chomp;
push @lines, $_;
# Ensamblar el sql acumulado e imprimir
if (/;/) {
processLine(join , @lines);
@lines = ();
}
}
# Results
TUPLA: while (my ($k,$v) = each %tuplas) {
next TUPLA if @$v == 1;
# print $k\n;
my @arr = @$v;
shift @arr; # let the first constraint
my ($table) = split /,/, $k;
foreach my $constraint (@arr) {
# print \t$_\n;
printf alter table %s drop constraint %s;\n, $table, $constraint;
}
}
exit 0;
my $lastSchema = public;
sub processLine {
local $_ = shift;
chomp;
$lastSchema = $1 if /SET search_path = (\w+)/;
if (my ($table, $constName, $constraintType, $constraintName) = /ALTER TABLE
ONLY (\w+)\s+ADD CONSTRAINT (\w+) (FOREIGN KEY|UNIQUE)\s*\((\w+)\)/) {
my $key = $lastSchema.$table,$constraintType.$constraintName;
my $aref = $tuplas{$key};
unless ($aref) {
$aref = [];
$tuplas{$key} = $aref;
}
push @$aref, $constName;
}
}
Bye
Hans
Hans Poo, Welinux S.A.
Bombero Ossa #1010, oficina 526,
+56-2-3729770, Movil: +56-9-3199305
Santiago, Chile
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Perl script to drop duplicated constraints definitions
Hi,
Sorry for the recent post, mistakenly i wrote the subject in spanish... glup.
Hi,
Sometimes i end up with some duplicated constraints definitions in my database,
i've noticed this when i reverse engineer databases, and see many links between
two tables.
I prepared a perl script that read an schema on standard input, and prints on
standard output some drop constraints for duplicated definitions, if you like
you can then execute them against your database:
For example if you save it as drop-dup-constraints.pl, then you can check your
database with:
pg_dump -Ox -s mydatabase | drop-dup-constraints.pl
#!/usr/bin/perl
use strict;
use warnings;
## Elimina los constraints sobre el mismo campo y la misma tabla
my ($table, $constName, $field);
my %tuplas;
my @lines = ();
while () {
next if /--/;
chomp;
push @lines, $_;
# Ensamblar el sql acumulado e imprimir
if (/;/) {
processLine(join , @lines);
@lines = ();
}
}
# Results
TUPLA: while (my ($k,$v) = each %tuplas) {
next TUPLA if @$v == 1;
#print $k\n;
my @arr = @$v;
shift @arr; # let the first constraint
my ($table) = split /,/, $k;
foreach my $constraint (@arr) {
# print \t$_\n;
printf alter table %s drop constraint %s;\n, $table, $constraint;
}
}
exit 0;
my $lastSchema = public;
sub processLine {
local $_ = shift;
chomp;
$lastSchema = $1 if /SET search_path = (\w+)/;
if (my ($table, $constName, $constraintType, $constraintName) = /ALTER TABLE
ONLY (\w+)\s+ADD CONSTRAINT (\w+) (FOREIGN KEY|UNIQUE)\s*\((\w+)\)/) {
my $key = $lastSchema.$table,$constraintType.$constraintName;
my $aref = $tuplas{$key};
unless ($aref) {
$aref = [];
$tuplas{$key} = $aref;
}
push @$aref, $constName;
}
}
Bye
Hans
Hans Poo, Welinux S.A.
Bombero Ossa #1010, oficina 526,
+56-2-3729770, Movil: +56-9-3199305
Santiago, Chile
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
