postgres database user account

Hoping someone out there can answer this general question(s).... I am
having to justify having access to the "postgres" database user account
to do DBA type work.....
Is there any specific items that require the postgres database user
account to run?

Can any general user (with superuser permission) basically do what this
postgres account does?

thanks, Maria Wilson
Nasa/Langley Research Center
Hampton, Virginia 23681

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

Yes, you can create a role that is a superuser that should be able to do in=
ternal work:

CREATE ROLE myuser;
ALTER ROLE myuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN PASSWORD=
'mypassword' VALID UNTIL 'infinity';

If you are talking about the operating system account named postgres, then =
this is a different question.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Maria L. Wilson
Sent: Wednesday, June 30, 2010 2:15 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] postgres database user account

Hoping someone out there can answer this general question(s).... I am=20
having to justify having access to the "postgres" database user account=20
to do DBA type work.....
Is there any specific items that require the postgres database user=20
account to run?

Can any general user (with superuser permission) basically do what this=20
postgres account does?

thanks, Maria Wilson
Nasa/Langley Research Center
Hampton, Virginia 23681

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

ok - thanks that makes sense....

so what about the operating system account that is different? What we
are planning on doing with the OS acct (postgres) is only allowing users
sudo ability to this account. Nobody should be able to directly log
into it. Do you think that will cause problems?

thanks again - Maria

Plugge, Joe R. wrote:
> Yes, you can create a role that is a superuser that should be able to do internal work:
>
> CREATE ROLE myuser;
> ALTER ROLE myuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN PASSWORD 'mypassword' VALID UNTIL 'infinity';
>
> If you are talking about the operating system account named postgres, then this is a different question.
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Maria L. Wilson
> Sent: Wednesday, June 30, 2010 2:15 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] postgres database user account
>
> Hoping someone out there can answer this general question(s).... I am
> having to justify having access to the "postgres" database user account
> to do DBA type work.....
> Is there any specific items that require the postgres database user
> account to run?
>
> Can any general user (with superuser permission) basically do what this
> postgres account does?
>
> thanks, Maria Wilson
> Nasa/Langley Research Center
> Hampton, Virginia 23681
>
>

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

If the user is allowed to become (sudo - postgres) they can stop, start, th=
e database and then change the pg_hba.conf, created unrestricted access int=
o the database, even from remote machines. Typically on our systems, we do=
not allow users to log into the actual machine, rather create roles in the=
database according to their needs and then have them come in from another =
machine. This keeps all sorts of undesirable behavior (zcat, grep, cut, aw=
k, programs running etc) off of your database machine.

-----Original Message-----
From: Maria L. Wilson [mailto:Maria.L.Wilson-1@nasa.gov]=20
Sent: Wednesday, June 30, 2010 3:03 PM
To: Plugge, Joe R.
Cc: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS APPLICATIONS]; pgsql-a=
dmin@postgresql.org
Subject: Re: [ADMIN] postgres database user account

ok - thanks that makes sense....

so what about the operating system account that is different? What we=20
are planning on doing with the OS acct (postgres) is only allowing users=20
sudo ability to this account. Nobody should be able to directly log=20
into it. Do you think that will cause problems?

thanks again - Maria

Plugge, Joe R. wrote:
> Yes, you can create a role that is a superuser that should be able to do =
internal work:
>
> CREATE ROLE myuser;
> ALTER ROLE myuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN PASSWO=
RD 'mypassword' VALID UNTIL 'infinity';
>
> If you are talking about the operating system account named postgres, the=
n this is a different question.
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgres=
ql.org] On Behalf Of Maria L. Wilson
> Sent: Wednesday, June 30, 2010 2:15 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] postgres database user account
>
> Hoping someone out there can answer this general question(s).... I am=20
> having to justify having access to the "postgres" database user account=
=20
> to do DBA type work.....
> Is there any specific items that require the postgres database user=20
> account to run?
>
> Can any general user (with superuser permission) basically do what this=
=20
> postgres account does?
>
> thanks, Maria Wilson
> Nasa/Langley Research Center
> Hampton, Virginia 23681
>
> =20

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

On Wed, 2010-06-30 at 16:02 -0400, Maria L. Wilson wrote:
> ok - thanks that makes sense....
>
> so what about the operating system account that is different? What we
> are planning on doing with the OS acct (postgres) is only allowing users
> sudo ability to this account. Nobody should be able to directly log
> into it. Do you think that will cause problems?

You should treat the postgres account like root. In reality nobody
should be logging in as postgres on the OS except for extremely specific
purposes.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

--------------020703090506080208050700
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit

and what would be those "extremely specific purposes"?

Joshua D. Drake wrote:
> On Wed, 2010-06-30 at 16:02 -0400, Maria L. Wilson wrote:
>
>> ok - thanks that makes sense....
>>
>> so what about the operating system account that is different? What we
>> are planning on doing with the OS acct (postgres) is only allowing users
>> sudo ability to this account. Nobody should be able to directly log
>> into it. Do you think that will cause problems?
>>
>
> You should treat the postgres account like root. In reality nobody
> should be logging in as postgres on the OS except for extremely specific
> purposes.
>
> Joshua D. Drake
>
>

--------------020703090506080208050700
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: 7bit







and what would be those "extremely specific purposes"?



Joshua D. Drake wrote:
cite="mid:1277928730.20538.2776.camel@jd-desktop.unknown.cha rter.com"
type="cite">

On Wed, 2010-06-30 at 16:02 -0400, Maria L. Wilson wrote:

  

ok - thanks that makes sense....



so what about the operating system account that is different?  What we 

are planning on doing with the OS acct (postgres) is only allowing users 

sudo ability to this account.  Nobody should be able to directly log 

into it.  Do you think that will cause problems?

    

You should treat the postgres account like root. In reality nobody

should be logging in as postgres on the OS except for extremely specific

purposes.



Joshua D. Drake



  

Re: postgres database user account

that sounds similar to what we are trying to accomplish. Looks like
what we need to do is use the sudo at the OS level - and remove the
postgres db user account altogether.... giving specific users the privs
(or create roles) that accomplish what they need.

Plugge, Joe R. wrote:
> If the user is allowed to become (sudo - postgres) they can stop, start, the database and then change the pg_hba.conf, created unrestricted access into the database, even from remote machines. Typically on our systems, we do not allow users to log into the actual machine, rather create roles in the database according to their needs and then have them come in from another machine. This keeps all sorts of undesirable behavior (zcat, grep, cut, awk, programs running etc) off of your database machine.
>
> -----Original Message-----
> From: Maria L. Wilson [mailto:Maria.L.Wilson-1@nasa.gov]
> Sent: Wednesday, June 30, 2010 3:03 PM
> To: Plugge, Joe R.
> Cc: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS APPLICATIONS]; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] postgres database user account
>
> ok - thanks that makes sense....
>
> so what about the operating system account that is different? What we
> are planning on doing with the OS acct (postgres) is only allowing users
> sudo ability to this account. Nobody should be able to directly log
> into it. Do you think that will cause problems?
>
> thanks again - Maria
>
> Plugge, Joe R. wrote:
>
>> Yes, you can create a role that is a superuser that should be able to do internal work:
>>
>> CREATE ROLE myuser;
>> ALTER ROLE myuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN PASSWORD 'mypassword' VALID UNTIL 'infinity';
>>
>> If you are talking about the operating system account named postgres, then this is a different question.
>>
>> -----Original Message-----
>> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Maria L. Wilson
>> Sent: Wednesday, June 30, 2010 2:15 PM
>> To: pgsql-admin@postgresql.org
>> Subject: [ADMIN] postgres database user account
>>
>> Hoping someone out there can answer this general question(s).... I am
>> having to justify having access to the "postgres" database user account
>> to do DBA type work.....
>> Is there any specific items that require the postgres database user
>> account to run?
>>
>> Can any general user (with superuser permission) basically do what this
>> postgres account does?
>>
>> thanks, Maria Wilson
>> Nasa/Langley Research Center
>> Hampton, Virginia 23681
>>
>>
>>

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

"Maria L. Wilson" writes:
> so what about the operating system account that is different? What we
> are planning on doing with the OS acct (postgres) is only allowing users
> sudo ability to this account. Nobody should be able to directly log
> into it. Do you think that will cause problems?

No, that's pretty standard. RPM installations for example have never
assigned a password to the postgres OS account, so it will behave in
exactly that way.

Usually the only things you need the OS account for are to start/stop
the server and update its outside-the-database configuration files,
such as postgresql.conf.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

"Maria L. Wilson" writes:
> that sounds similar to what we are trying to accomplish. Looks like
> what we need to do is use the sudo at the OS level - and remove the
> postgres db user account altogether.... giving specific users the privs
> (or create roles) that accomplish what they need.

You can't remove the postgres DB account; it owns the core system
catalogs, functions, etc. In any case, understand that any superuser
database account is as powerful as any other. Giving DBAs superuser
accounts other than postgres is probably good just from an
administrative standpoint, but it won't reduce their capability to
screw things up.

If you're using a PG version recent enough to have a "createrole"
account attribute as distinct from "superuser", look into how much of
your admin work can be done with "createrole" accounts. Those are
a lot weaker than full superuser, but still are enough for many ordinary
admin tasks (such as managing everyday-user accounts).

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

This is a multi-part message in MIME format.

--=oGmtomMw73591=imx3.etecsa.cu=
Content-Disposition: inline
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable



El mié, 30-06-2010 a las 16:15 -0400, Maria L. Wilson escribió:
> that sounds similar to what we are trying to accomplish. Looks like=20
> what we need to do is use the sudo at the OS level - and remove the=20
> postgres db user account altogether.... giving specific users the priv=
s=20
> (or create roles) that accomplish what they need.
>=20


or only allow IP connections and remove the sock LOCAL and remove ALL of
pg_hba Usuaris

--=20
Redards,
Gilberto Castillo
Edificio Beijing. Miramar Trade Center. Etecsa.
Miramar, La Haban.Cuba.

--=oGmtomMw73591=imx3.etecsa.cu=
Content-Type: text/plain
Content-Disposition: inline

---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: ,

--=oGmtomMw73591=imx3.etecsa.cu=
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=oGmtomMw73591=imx3.etecsa.cu=--

Re: postgres database user account

"Maria L. Wilson" wrote:

> What we are planning on doing with the OS acct (postgres) is only
> allowing users sudo ability to this account. Nobody should be
> able to directly log into it. Do you think that will cause
> problems?

It's what we do. We use ident logins from this account, and use it
for maintenance jobs scheduled through crontab, such as database
vacuum and some backup related processes. It works well for us, and
we figure that if someone can run as root, they can trash the
database anyway, so we don't think we're opening up any security
holes with it.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres database user account

On Wed, 2010-06-30 at 16:13 -0400, Maria L. Wilson wrote:
> and what would be those "extremely specific purposes"?

Needed to modify files that are within the cluster directory
(postgresql.conf) or needing to stop/start PostgreSQL which could be
done with sudo without allowing login.

Sincerely,

Joshua D. Drake
> >

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin