Re: Simple commands don't work

On Mon, Jun 16, 2014 at 2:42 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Larry Martell <larry(dot)martell(at)gmail(dot)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

What confused me was alteruser page, where is says:

ALTER USER name SET parameter { TO | = } { value | DEFAULT }

and then in the Parameters section:

password

The new password to be used for this account.

>> 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.

No trust is not set.

>> 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