Thread: Simple commands don't work
Hello- I am new to Postgres, but I am very experienced with Sybase, Oracle, and MySQL. I am trying to use some simple commands, but they do not seem to work. 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'; ERROR: unrecognized configuration parameter "password" Then I read somewhere else it is: phis=> ALTER USER larry WITH PASSWORD 'XXXX'; ALTER ROLE But then it still let me log in without one. 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. 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" 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. The I found this: phis=> grant ALL PRIVILEGES ON DATABASE phis to larry; WARNING: no privileges were granted for "phis" From pgAdmin I was able to grant myself select access, but why don't the command line commands work? Thanks! -larry
On 2014-06-16, 1:45 PM, Larry Martell wrote: > Hello- > > I am new to Postgres, but I am very experienced with Sybase, Oracle, > and MySQL. I am trying to use some simple commands, but they do not > seem to work. > > 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'; > ERROR: unrecognized configuration parameter "password" > > Then I read somewhere else it is: > > phis=> ALTER USER larry WITH PASSWORD 'XXXX'; > ALTER ROLE > > But then it still let me log in without one. > > 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. > > 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" > > 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. The I found this: > > phis=> grant ALL PRIVILEGES ON DATABASE phis to larry; > WARNING: no privileges were granted for "phis" > > From pgAdmin I was able to grant myself select access, but why don't > the command line commands work? > > > Thanks! > -larry > > Hi there, ALTER USER larry password 'newpass'; That should work Ziggy
Check in your pg_hba.conf file.
If you are working on the server itself, pg_hba.conf may be letting you in without passwords. You'll see a line like 'host all all 127.0.0.1 trust'.
On Mon, Jun 16, 2014 at 10:45 AM, Larry Martell <larry.martell@gmail.com> wrote:
Hello-
I am new to Postgres, but I am very experienced with Sybase, Oracle,
and MySQL. I am trying to use some simple commands, but they do not
seem to work.
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';
ERROR: unrecognized configuration parameter "password"
Then I read somewhere else it is:
phis=> ALTER USER larry WITH PASSWORD 'XXXX';
ALTER ROLE
But then it still let me log in without one.
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.
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"
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. The I found this:
phis=> grant ALL PRIVILEGES ON DATABASE phis to larry;
WARNING: no privileges were granted for "phis"
From pgAdmin I was able to grant myself select access, but why don't
the command line commands work?
Thanks!
-larry
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
On Mon, Jun 16, 2014 at 1:59 PM, Jason Whitener <jwhitene@pcc.edu> wrote: > Check in your pg_hba.conf file. > http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html > > If you are working on the server itself, pg_hba.conf may be letting you in > without passwords. You'll see a line like 'host all all 127.0.0.1 trust'. Nope - all the host entries have password. > > > On Mon, Jun 16, 2014 at 10:45 AM, Larry Martell <larry.martell@gmail.com> > wrote: >> >> Hello- >> >> I am new to Postgres, but I am very experienced with Sybase, Oracle, >> and MySQL. I am trying to use some simple commands, but they do not >> seem to work. >> >> 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'; >> ERROR: unrecognized configuration parameter "password" >> >> Then I read somewhere else it is: >> >> phis=> ALTER USER larry WITH PASSWORD 'XXXX'; >> ALTER ROLE >> >> But then it still let me log in without one. >> >> 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. >> >> 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" >> >> 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. The I found this: >> >> phis=> grant ALL PRIVILEGES ON DATABASE phis to larry; >> WARNING: no privileges were granted for "phis" >> >> From pgAdmin I was able to grant myself select access, but why don't >> the command line commands work? >> >> >> Thanks! >> -larry
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
On Mon, Jun 16, 2014 at 2:42 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > 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 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
Larry Martell wrote: >> Check in your pg_hba.conf file. >> http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html >> >> If you are working on the server itself, pg_hba.conf may be letting you in >> without passwords. You'll see a line like 'host all all 127.0.0.1 trust'. > > Nope - all the host entries have password. Then there is probably a line that reads local all all trust If you connect without specifying a host (locally via socket), this will let you in without a password. Yours, Laurenz Albe
On Tue, Jun 17, 2014 at 3:23 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Larry Martell wrote: >>> Check in your pg_hba.conf file. >>> http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html >>> >>> If you are working on the server itself, pg_hba.conf may be letting you in >>> without passwords. You'll see a line like 'host all all 127.0.0.1 trust'. >> >> Nope - all the host entries have password. > > Then there is probably a line that reads > local all all trust > > If you connect without specifying a host (locally via socket), this will > let you in without a password. There are these local entires: local all postgres peer local all all peer
Larry Martell <larry.martell@gmail.com> wrote: > There are these local entires: > > local all postgres peer > local all all peer When peer authentication is specified, the current OS login is considered authenticated as a database user with the same ID, and no password is needed. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Jun 17, 2014 at 11:34 AM, Kevin Grittner <kgrittn@ymail.com> wrote: > Larry Martell <larry.martell@gmail.com> wrote: > >> There are these local entires: >> >> local all postgres peer >> local all all peer > > When peer authentication is specified, the current OS login is > considered authenticated as a database user with the same ID, and > no password is needed. Thanks.