Обсуждение: 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