Postgres Pro
Downloads
Home   >   mailing lists

Thread: Database access permissions

Database access permissions

From
"Jonathan A. Zdziarski"
Date:
Hi,

Got another question for you today.  Is there any documentation that talks
about how to set up individual database permissions?  I need to be able to
set it up so that some users can only access certain databases as
read-only.  Thanks.

Thank you,

Jonathan A. Zdziarski
Sr. Systems Administrator
Netrail, inc.
888.NET.RAIL x240

Re: [GENERAL] Database access permissions

From
Howie
Date:
On Thu, 18 Mar 1999, Jonathan A. Zdziarski wrote:

> Hi,
>
> Got another question for you today.  Is there any documentation that talks
> about how to set up individual database permissions?  I need to be able to
> set it up so that some users can only access certain databases as
> read-only.  Thanks.

read up on the GRANTS section in the docs or do a '\h grant' and
a '\h revoke' from psql.

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
  "You can change the world with a bullet in the right place"

Accessing records w/&

From
"Jonathan A. Zdziarski"
Date:
Oddly when I attempt to do a

SELECT * FROM database WHERE "field" = 'anything with an & sign in it'

it returns nothing..  How do you refer to &'s so that it will return the
fields?

Thank you,

Jonathan A. Zdziarski
Sr. Systems Administrator
Netrail, inc.
888.NET.RAIL x240

Re: [GENERAL] Accessing records w/&

From
tc lewis
Date:
works fine for more.

select whatever from whatever where whatever='blah&blah';
and
select whatever from whatever where whatever like '%&%';

both return the expected results in psql.

-tcl.


On Fri, 19 Mar 1999, Jonathan A. Zdziarski wrote:

> Oddly when I attempt to do a
>
> SELECT * FROM database WHERE "field" = 'anything with an & sign in it'
>
> it returns nothing..  How do you refer to &'s so that it will return the
> fields?
>
> Thank you,
>
> Jonathan A. Zdziarski
> Sr. Systems Administrator
> Netrail, inc.
> 888.NET.RAIL x240
>
>
>

ERROR: ExecInitIndexScan

From
Mauro Bartolomeoli
Date:
I'm using Postgresql 6.3.2 on Linux RedHat 5.2.

I receive the following error when I send a specific query to Postgres:

ERROR: ExecInitIndexScan: both left and right op's are rel-vars

The query is:

select codice,descrizione,sub_titolo,
       immobili_per_zona.numero_annunci_immobili_vendita,coordinate
from zona,contiene_zona,immobili_per_zona
where codice_sotto_zona=codice and immobili_per_zona.codice_zona=
      codice_sotto_zona and codice_tipo_immobili=1 and
      immobili_per_zona.numero_annunci_immobili_vendita>0 and
      contiene_zona.codice_zona=31011 and speciale='f' and approvata='t';

The relevant tables are:

ZONA:

codice                           | int4 not null       PRIMARY KEY  |
descrizione                      | varchar() not null               |
immagine                         | varchar()                        |
sub_titolo                       | varchar() not null               |
cap                              | varchar()                        |
prefisso                         | varchar()                        |
numero_annunci_immobili_vendita  | int4 not null                    |
numero_annunci_immobili_affitto  | int4 not null                    |
numero_annunci_aziende_vendita   | int4 not null                    |
numero_annunci_aziende_affitto   | int4 not null                    |
numero_annunci_vacanze_affitto   | int4 not null                    |
principale                       | bool not null                    |
speciale                         | bool not null                    |
attiva                           | bool not null                    |
approvata                        | bool not null                    |

CONTIENE_ZONA:

codice_zona                      | int4 not null                    |
codice_sotto_zona                | int4 not null                    |
coordinate                       | text                             |
PRIMARY_KEY(codice_zona,codice_sotto_zona)

IMMOBILI_PER_ZONA:

codice_tipo_immobili             | int2 not null                    |
codice_zona                      | int4 not null                    |
numero_annunci_immobili_vendita  | int4 not null                    |
numero_annunci_immobili_affitto  | int4 not null                    |
PRIMARY_KEY(codice_tipo_immobili,codice_zona)

I also have the following indeces:

CREATE INDEX contiene_zona_skey1 on Contiene_Zona (Codice_sotto_zona);
CREATE INDEX contiene_zona_skey2 on Contiene_Zona (Codice_zona);
CREATE INDEX immobili_per_zona_skey1 on immobili_per_zona using hash
             (Codice_zona);

This is the EXPLAIN for the query:

Nested Loop  (cost=2.05 size=1 width=52)
  ->  Nested Loop  (cost=0.00 size=1 width=24)
        ->  Index Scan on immobili_per_zona  (cost=0.00 size=0 width=8)
        ->  Index Scan on contiene_zona  (cost=2.00 size=66 width=16)
  ->  Index Scan on zona  (cost=2.05 size=30555 width=28)

The error magically disappears if i drop the primary key index on
contiene_zona. Is there any way to mantain this index without obtaining
the error?
Is this corrected in 6.4.x? Any help appreciated.

---

------------------------------------
 Mauro Bartolomeoli
 e-mail: mbarto@novacomp.it
 ICQ#: 9602542
------------------------------------

ERROR: ExecInitIndexScan

From
Mauro Bartolomeoli
Date:
I repeat the question because nobody answered. Please, if you know what
the error on the subject means and how to avoid it, reply.

I'm using Postgresql 6.3.2 on Linux RedHat 5.2.

I receive the following error when I send a specific query to Postgres:

ERROR: ExecInitIndexScan: both left and right op's are rel-vars

The query is:

select codice,descrizione,sub_titolo,
       immobili_per_zona.numero_annunci_immobili_vendita,coordinate
from zona,contiene_zona,immobili_per_zona
where codice_sotto_zona=codice and immobili_per_zona.codice_zona=
      codice_sotto_zona and codice_tipo_immobili=1 and
      immobili_per_zona.numero_annunci_immobili_vendita>0 and
      contiene_zona.codice_zona=31011 and speciale='f' and approvata='t';

The relevant tables are:

ZONA:

codice                           | int4 not null       PRIMARY KEY  |
descrizione                      | varchar() not null               |
immagine                         | varchar()                        |
sub_titolo                       | varchar() not null               |
cap                              | varchar()                        |
prefisso                         | varchar()                        |
numero_annunci_immobili_vendita  | int4 not null                    |
numero_annunci_immobili_affitto  | int4 not null                    |
numero_annunci_aziende_vendita   | int4 not null                    |
numero_annunci_aziende_affitto   | int4 not null                    |
numero_annunci_vacanze_affitto   | int4 not null                    |
principale                       | bool not null                    |
speciale                         | bool not null                    |
attiva                           | bool not null                    |
approvata                        | bool not null                    |

CONTIENE_ZONA:

codice_zona                      | int4 not null                    |
codice_sotto_zona                | int4 not null                    |
coordinate                       | text                             |
PRIMARY_KEY(codice_zona,codice_sotto_zona)

IMMOBILI_PER_ZONA:

codice_tipo_immobili             | int2 not null                    |
codice_zona                      | int4 not null                    |
numero_annunci_immobili_vendita  | int4 not null                    |
numero_annunci_immobili_affitto  | int4 not null                    |
PRIMARY_KEY(codice_tipo_immobili,codice_zona)

I also have the following indeces:

CREATE INDEX contiene_zona_skey1 on Contiene_Zona (Codice_sotto_zona);
CREATE INDEX contiene_zona_skey2 on Contiene_Zona (Codice_zona);
CREATE INDEX immobili_per_zona_skey1 on immobili_per_zona using hash
             (Codice_zona);

This is the EXPLAIN for the query:

Nested Loop  (cost=2.05 size=1 width=52)
  ->  Nested Loop  (cost=0.00 size=1 width=24)
        ->  Index Scan on immobili_per_zona  (cost=0.00 size=0 width=8)
        ->  Index Scan on contiene_zona  (cost=2.00 size=66 width=16)
  ->  Index Scan on zona  (cost=2.05 size=30555 width=28)

The error magically disappears if i drop the primary key index on
contiene_zona. Is there any way to mantain this index without obtaining
the error?
Is this corrected in 6.4.x? Any help appreciated.

---

------------------------------------
 Mauro Bartolomeoli
 e-mail: mbarto@novacomp.it
 ICQ#: 9602542
------------------------------------