Re: Simple commands don't work - Mailing list pgsql-novice

Larry Martell <larry.martell@gmail.com> wrote:

> First I wanted to give myself a password. I read in the online
> docs the command was this:
>
> phis=> alter user larry set password = 'XXXX';

That is a mis-reading of the documentation.  There are various user
options or attributes which can be configured, which includes the
password.  There are also various configuration parameters for
which the default value (normally taken from the postgresql.conf
configuration file) can be overridden for particular users.  An
example of this would be to set work_mem higher for a reporting
user.  Above you are trying to use the syntax for setting a
configuration parameter for the password, which is a user
attribute.

> ERROR:  unrecognized configuration parameter "password"

And this is the result of using the wrong syntax.  Was there
anything in the documentation which you found confusing or
misleading?

http://www.postgresql.org/docs/current/interactive/sql-alteruser.html
http://www.postgresql.org/docs/current/interactive/sql-alterrole.html

> Then I read somewhere else it is:
>
> phis=> ALTER USER larry WITH PASSWORD 'XXXX';
> ALTER ROLE

There you go.

> But then it still let me log in without one.

You might want to look at the documentation for the host based
authentication configuration and the pgpass configuration.

http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html
http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html

In pg_hba.conf you may have "trust" configured for local
connections and/or connections through localhost.

> Then I checked with pgAdmin and it showed I did not have any
> password.  I set one there, but it still lets me login without
> one.

pgAdmin, based on how you have *that* configured, may write
passwords to the pgpass file.  I never use pgAdmin, so I'm not sure
what it was showing you; I wonder whether it might have been
showing you what user IDs you had passwords for in the pgpass file,
rather than what logins had passwords within the database.

> Then I went to do a select from a table, and I got permission
> denied.  But the table is in a schema (the name is "public") that
> is set for "GRANT ALL TO ALL"

That grants two permissions for that user in the schema: the right
to use the schema at all and the right to create objects within
that schema.  Those are all of the permissions which exist at the
schema level.

> According to the docs this would be the command to grant access
> to all tables:
>
> phis=> grant select on all tables in public to larry;
> ERROR:  syntax error at or near "public"
>
> But it doesn't like that.

You forgot the word SCHEMA before public.

> The I found this:
>
> phis=> grant ALL PRIVILEGES ON DATABASE phis to larry;
> WARNING:  no privileges were granted for "phis"

You were connected as a user which didn't have rights to grant
database privileges.  You need to be connected the database owner
or as a database superuser.  Also, be aware that this only grants
database-level permissions: the ability to connect to the database,
the ability to create schemas in the database, and the ability to
create temporary tables in the database.  That command would not
grant you any permissions (such as SELECT) on any objects within
the database (like a table).

> From pgAdmin I was able to grant myself select access, but why
> don't the command line commands work?

They do, but it appears you are not using the options which would
provide the behavior you want.

Once you have mastered the commands, perhaps you could suggest what
could be improved in the documentation to help others get to that
point.  Suggestions are welcome.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company