Thread: How to enable remote anoymous logins? (+ admin books?)
Hi. What must I do to enable remote anonymous logins to our PostgreSQL server? What determines whether a password is required? Is it possible to require that the password be the user's e-mail address? Does the server keep record of all connections? (BTW, our server is version 8.0, and runs on Linux.) As you can see, I have many questions, too many in fact! I have not had much luck finding good resources on *administering* a PostgreSQL server. Are there any good books for this? Or other resources for the novice dbadmin? I'm particularly interested in stuff on administering PostgreSQL on Linux/Unix. Many thanks in advance! kj
On 2/27/06 10:21 AM, "kynn@panix.com" <kynn@panix.com> wrote: > > > > Hi. What must I do to enable remote anonymous logins to our > PostgreSQL server? What determines whether a password is required? > Is it possible to require that the password be the user's e-mail > address? Does the server keep record of all connections? > > (BTW, our server is version 8.0, and runs on Linux.) > > As you can see, I have many questions, too many in fact! I have not > had much luck finding good resources on *administering* a PostgreSQL > server. Are there any good books for this? Or other resources for > the novice dbadmin? I'm particularly interested in stuff on > administering PostgreSQL on Linux/Unix. If you haven't found the postgresql docs yet, they are here and are excellent. http://www.postgresql.org/docs/8.0 See here for details on what you want to do: http://www.postgresql.org/docs/8.0/interactive/client-authentication.html However, in short, you probably DO NOT want to make anonymous connections to your database in most cases. What is it that you are trying to do? Sean
On 2/28/06, kynn@panix.com <kynn@panix.com> wrote: > > > > Hi. What must I do to enable remote anonymous logins to our > PostgreSQL server? What determines whether a password is required? I'd suggest you start by reading the FAQ, and the excellent documentation. As for the authentication, look at $PGDATA/pg_hba.conf .... > Is it possible to require that the password be the user's e-mail > address? Does the server keep record of all connections? How is it anonymous if you require his password? Or do you just want to keep track of who it is that logged in without actual authentication? As for the recording: it will if you tell it to, again, read the excellent documentation. > As you can see, I have many questions, too many in fact! I have not > had much luck finding good resources on *administering* a PostgreSQL > server. Look at again. http://www.postgresql.org/docs/ > Are there any good books for this? Or other resources for > the novice dbadmin? I'm particularly interested in stuff on > administering PostgreSQL on Linux/Unix. There's a couple ... have a search on Amazon for PpostgreSQL Cheers, Andrej
X-Original-To: kynn@panix.com
Date: Mon, 27 Feb 2006 10:34:22 -0500
From: Sean Davis <sdavis2@mail.nih.gov>
Thread-Topic: [NOVICE] How to enable remote anoymous logins? (+ admin
books?)
Thread-Index: AcY7s0gDhq1azaemEdqKXgANkzVl6A==
On 2/27/06 10:21 AM, "kynn@panix.com" <kynn@panix.com> wrote:
>
>
>
> Hi. What must I do to enable remote anonymous logins to our
> PostgreSQL server? What determines whether a password is required?
> Is it possible to require that the password be the user's e-mail
> address? Does the server keep record of all connections?
>
> (BTW, our server is version 8.0, and runs on Linux.)
>
> As you can see, I have many questions, too many in fact! I have not
> had much luck finding good resources on *administering* a PostgreSQL
> server. Are there any good books for this? Or other resources for
> the novice dbadmin? I'm particularly interested in stuff on
> administering PostgreSQL on Linux/Unix.
If you haven't found the postgresql docs yet, they are here and are
excellent.
http://www.postgresql.org/docs/8.0
See here for details on what you want to do:
http://www.postgresql.org/docs/8.0/interactive/client-authentication.html
Thanks!
However, in short, you probably DO NOT want to make anonymous connections to
your database in most cases. What is it that you are trying to do?
I am trying go mimic the behavior of other DB servers I have used that
allow me to connect, without requiring a password, with something
like:
% mysql -h ensembldb.ensembl.org -U anonymous
Granted, all the examples I know of are, like the one shown above,
with MySQL servers, but I thought I'd be able to do the same thing
with Postgres.
Needless to say, my intention was to give the anonymous user minimal
privileges (namely, read-only queries on a specific database).
I suppose that even a user with minimal privileges could cause harm
(e.g. by submitting a very computationally demanding request). Is
this the reason for the advice against such connections, or is there
something else?
kj
On 2/27/06 12:58 PM, "kynn@panix.com" <kynn@panix.com> wrote: > > > X-Original-To: kynn@panix.com > Date: Mon, 27 Feb 2006 10:34:22 -0500 > From: Sean Davis <sdavis2@mail.nih.gov> > Thread-Topic: [NOVICE] How to enable remote anoymous logins? (+ admin > books?) > Thread-Index: AcY7s0gDhq1azaemEdqKXgANkzVl6A== > > > > > On 2/27/06 10:21 AM, "kynn@panix.com" <kynn@panix.com> wrote: > >> >> >> >> Hi. What must I do to enable remote anonymous logins to our >> PostgreSQL server? What determines whether a password is required? >> Is it possible to require that the password be the user's e-mail >> address? Does the server keep record of all connections? >> >> (BTW, our server is version 8.0, and runs on Linux.) >> >> As you can see, I have many questions, too many in fact! I have not >> had much luck finding good resources on *administering* a PostgreSQL >> server. Are there any good books for this? Or other resources for >> the novice dbadmin? I'm particularly interested in stuff on >> administering PostgreSQL on Linux/Unix. > > If you haven't found the postgresql docs yet, they are here and are > excellent. > > http://www.postgresql.org/docs/8.0 > > See here for details on what you want to do: > > http://www.postgresql.org/docs/8.0/interactive/client-authentication.html > > Thanks! > > However, in short, you probably DO NOT want to make anonymous connections > to > your database in most cases. What is it that you are trying to do? > > > > I am trying go mimic the behavior of other DB servers I have used that > allow me to connect, without requiring a password, with something > like: > > % mysql -h ensembldb.ensembl.org -U anonymous Ah, yes, good-ol ensembl. > Granted, all the examples I know of are, like the one shown above, > with MySQL servers, but I thought I'd be able to do the same thing > with Postgres. Yes. You can. Just create an anonymous user and set the auth type to "trust" for all connections. If you do this, you need to make ABSOLUTELY SURE that you have sufficiently limited the privileges of that user. > Needless to say, my intention was to give the anonymous user minimal > privileges (namely, read-only queries on a specific database). > > I suppose that even a user with minimal privileges could cause harm > (e.g. by submitting a very computationally demanding request). Is > this the reason for the advice against such connections, or is there > something else? That is the main reason in my mind. You might want to look at a web-based abstraction layer (website) that allows common queries in a quick-and-easy way and then create user accounts for your "power users" on an as-needed basis. Look at ensmart, biomart, and the UCSC table browser for excellent examples of these in genomics. Sean
Date: Mon, 27 Feb 2006 13:10:08 -0500
From: Sean Davis <sdavis2@mail.nih.gov>
Thread-Topic: [NOVICE] How to enable remote anoymous logins? (+ admin
books?)
On 2/27/06 12:58 PM, "kynn@panix.com" <kynn@panix.com> wrote:
If you do this, you need to make ABSOLUTELY
SURE that you have sufficiently limited the privileges of that user.
How can I list the privileges a user has?
Date: Tue, 28 Feb 2006 06:50:51 +1300 From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com> On 2/28/06, kynn@panix.com <kynn@panix.com> wrote: > Is it possible to require that the password be the user's e-mail > address? Does the server keep record of all connections? How is it anonymous if you require his password? I meant "anonymous" in the same spirit as "anonymous FTP", which often requests an e-mail address as password. I realize that often it is possible for users to enter anything in response to this request, but it is still helpful with users who choose to satisfy the request. > Are there any good books for this? Or other resources for > the novice dbadmin? I'm particularly interested in stuff on > administering PostgreSQL on Linux/Unix. There's a couple ... have a search on Amazon for PpostgreSQL That was my initial approach, and I ended up with a book that did not answer the questions I just posted. Hence I thought I would ask those with more knowledge for some specific recommendations. kj
On 2/27/06 2:05 PM, "kynn@panix.com" <kynn@panix.com> wrote: > > > Date: Mon, 27 Feb 2006 13:10:08 -0500 > From: Sean Davis <sdavis2@mail.nih.gov> > Thread-Topic: [NOVICE] How to enable remote anoymous logins? (+ admin > books?) > > On 2/27/06 12:58 PM, "kynn@panix.com" <kynn@panix.com> wrote: > > If you do this, you need to make ABSOLUTELY > SURE that you have sufficiently limited the privileges of that user. > > How can I list the privileges a user has? When I say ABSOLUTELY SURE above, I mean that you really have to read and understand the access control offered by postgresql, that's all. See here for managing users and privileges: http://www.postgresql.org/docs/8.0/interactive/user-manag.html Sean
In PLPGSQL, does anyone know how to subtract MILLISECONDS from a field defined as timestamp(3), yielding another field defined as timestamp(3)? Have been trying interval, and conversion functions but no luck. Example: '2006-10-17 14:23:28.951'; subtract 5 milliseconds from a field called mytable.end_time, and return the results to a field named work_timestamp. work_timestamp := ????
SELECT (end_time - interval '00:00:05') AS work_timestamp FROM mytable;
--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Lane Van Ingen
Sent: Tuesday, October 17, 2006 2:33 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] How to Substract Milliseconds from A timestamp(3)
Field
In PLPGSQL, does anyone know how to subtract MILLISECONDS from a field
defined as timestamp(3), yielding another field defined as timestamp(3)?
Have been trying interval, and conversion functions but no luck.
Example: '2006-10-17 14:23:28.951'; subtract 5 milliseconds from a field
called mytable.end_time, and return the results to a field named
work_timestamp.
work_timestamp := ????
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Brandon Aiken wrote:
> SELECT (end_time - interval '00:00:05') AS work_timestamp FROM mytable;
No, that will subtract 5 seconds, not 5 milliseconds. Just use subtract
'0.005 seconds'::interval:
select now(), now()-'0.005 seconds'::interval;
now | ?column?
-------------------------------+-------------------------------
2006-10-17 14:11:59.805446-07 | 2006-10-17 14:11:59.800446-07
Cheers,
Steve
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Lane Van Ingen
> Sent: Tuesday, October 17, 2006 2:33 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] How to Substract Milliseconds from A timestamp(3)
> Field
>
> In PLPGSQL, does anyone know how to subtract MILLISECONDS from a field
> defined as timestamp(3), yielding another field defined as timestamp(3)?
>
> Have been trying interval, and conversion functions but no luck.
>
> Example: '2006-10-17 14:23:28.951'; subtract 5 milliseconds from a field
>
> called mytable.end_time, and return the results to a field named
> work_timestamp.
>
> work_timestamp := ????
Ah, right. Mea culpa.
SELECT (end_time - interval '00:00:00.005') AS work_timestamp FROM
mytable;
That should work, too.
--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Tuesday, October 17, 2006 5:14 PM
To: pgsql-novice@postgresql.org
Cc: Brandon Aiken; Lane Van Ingen
Subject: Re: [NOVICE] How to Substract Milliseconds from A timestamp(3)
Field
Brandon Aiken wrote:
> SELECT (end_time - interval '00:00:05') AS work_timestamp FROM
mytable;
No, that will subtract 5 seconds, not 5 milliseconds. Just use subtract
'0.005 seconds'::interval:
select now(), now()-'0.005 seconds'::interval;
now | ?column?
-------------------------------+-------------------------------
2006-10-17 14:11:59.805446-07 | 2006-10-17 14:11:59.800446-07
Cheers,
Steve
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Lane Van Ingen
> Sent: Tuesday, October 17, 2006 2:33 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] How to Substract Milliseconds from A timestamp(3)
> Field
>
> In PLPGSQL, does anyone know how to subtract MILLISECONDS from a field
> defined as timestamp(3), yielding another field defined as
timestamp(3)?
>
> Have been trying interval, and conversion functions but no luck.
>
> Example: '2006-10-17 14:23:28.951'; subtract 5 milliseconds from a
field
>
> called mytable.end_time, and return the results to a field named
> work_timestamp.
>
> work_timestamp := ????