Обсуждение: Sorry, real newbie question about connecting to a database
I am developinng an appliction usig Postgresql 11, installed on a UBUTU 18.14 machine. I ahve vreated a new database to do some testing on restricting access of specific users/roles to certain data. I have done the following: REVOKE ALL ON DATABASE pertest FROM employee; GRANT CONNECT ON DATABASE pertest TO employee; and I have verifed tht the user employee does exst, I have also doen a few more GRABTs to allow specific acces. But I cannot conect, or swith to user employee: stan@smokey:/etc/postgresql/11/main$ psql -U employee psql: FATAL: Peer authentication failed for user "employee" stan=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------+----------+----------+---------+---------+----------------------- pertest | stan | UTF8 | C.UTF-8 | C.UTF-8 | =Tc/stan + | | stan=CTc/stan employee=CTc/stan Sorrry cut and paste mangled that. What am I failing to do here? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
stan <stanb@panix.com> writes: > But I cannot conect, or swith to user > employee: > stan@smokey:/etc/postgresql/11/main$ psql -U employee > psql: FATAL: Peer authentication failed for user "employee" This means that you've set pg_hba.conf to specify "peer" authentication, which by default only lets you connect as a PG role named the same as your OS account. (This is about are-you-who-you-say-you-are; whether who-you-say-you-are has privilege to connect is independent.) You could use some other auth method, or you could set up a mapping file that says you (stan) are allowed to connect as "employee". regards, tom lane
On 8/19/19 7:44 AM, stan wrote: > I am developinng an appliction usig Postgresql 11, installed on a UBUTU > 18.14 machine. > > I ahve vreated a new database to do some testing on restricting access of > specific users/roles to certain data. I have done the following: > > REVOKE ALL ON DATABASE pertest FROM employee; > GRANT CONNECT ON DATABASE pertest TO employee; > > and I have verifed tht the user employee does exst, I have also doen a few > more GRABTs to allow specific acces. But I cannot conect, or swith to user > employee: > > stan@smokey:/etc/postgresql/11/main$ psql -U employee > psql: FATAL: Peer authentication failed for user "employee" > > stan=> \l > List of databases > Name | Owner | Encoding | Collate | Ctype | Access privileges > --------+----------+----------+---------+---------+----------------------- > pertest | stan | UTF8 | C.UTF-8 > | C.UTF-8 | =Tc/stan + > | | stan=CTc/stan > > employee=CTc/stan > > Sorrry cut and paste mangled that. > > What am I failing to do here? > > Tom has spelled out the specific issue. The generic issue is that security in Postgres is a multi-layer process that involves many moving parts. You will save yourself a lot of do overs by looking at the relevant documentation. Starting roughly from outside in: Server connection: https://www.postgresql.org/docs/11/runtime-config-connection.html Client authentication(the pg_hba.conf Tom referred to): https://www.postgresql.org/docs/11/client-authentication.html Database roles(users): https://www.postgresql.org/docs/11/user-manag.html Role/user permissions: https://www.postgresql.org/docs/11/sql-grant.html Finer grained permissions(row level security): https://www.postgresql.org/docs/11/ddl-rowsecurity.html The above is intimidating and not something that will be fully understood in a single reading(or in my case multiple readings:)). Still a passing familiarity with the concepts will make your life easier. -- Adrian Klaver adrian.klaver@aklaver.com